Unable to filter some transactions where numerical fields are the value 0

[nullun]

Subject of the issue

If you search for transactions without certain filters, the result will include transactions where certain fields are 0 (amount, asset-id, application-id). But if you attempt to filter for those transactions, they seem to disappear for some fields.

Steps to reproduce

Finding asset transfers where amount is 0.

# Count number of axfer transactions in block 47171361, filtering for amount==0 in jq.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=47171361&tx-type=axfer" | jq '.transactions | map(select(."asset-transfer-transaction".amount == 0)) | length'
16

# Count number of axfer transactions in block 47171361, filtering for currency-less-than=1 in URL.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=47171361&tx-type=axfer&currency-less-than=1" | jq '.transactions | map(select(."asset-transfer-transaction".amount == 0)) | length'
0

Looking for application creations, where application-id is 0.

# Count number of appl transactions in block 49725523, filtering for application-id==0 in jq.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=49725523&tx-type=appl" | jq '.transactions | map(select(."application-transaction"."application-id" == 0)) | length'
1

# Count number of appl transactions in block 49725523, filtering for application-id==0 in URL.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=49725523&tx-type=appl&application-id=0" | jq '.transactions | map(select(."application-transaction"."application-id" == 0)) | length'
0

But interestingly, if you look for asset transfers, where asset-id is 0. This does work correctly.

# Count number of axfer transactions in block 47171361, filtering for asset-id==0 in jq.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=47171361&tx-type=axfer" | jq '.transactions | map(select(."asset-transfer-transaction"."asset-id" == 0)) | length'  
1

# Count number of axfer transactions in block 47171361, filtering for asset-id==0 in URL.
nullun ~ % curl -s "https://mainnet-idx.algonode.cloud/v2/transactions?round=47171361&tx-type=axfer&asset-id=0" | jq '.transactions | map(select(."asset-transfer-transaction"."asset-id" == 0)) | length'
1

Your environment

This is consistent on all networks at the moment, including a local sandbox environment building from source.

Expected behaviour

I would probably expect transactions where a field is 0 to be returned if I'm searching for transactions where the field is 0. E.g. Looking for asset optin transactions where amount is zero. Or looking for applications being created since their application-id didn't exist when sending.

Actual behaviour

Some transactions that were committed to the chain with certain fields containing the value 0 are not returned when filtering for them.

[gmalouf]

application id

The application-id behavior above happens because we are storing the created application's actual id on the underlying table rather than 0. We can compensate/mask this, but it leads to the question I pose below:

If someone specifies to filter on the application-id that was created, would you expect the creation transaction to be included in that query as well?
I think a weird behavior would be to filter for application_id=0, get that create transaction, filter for application_id=actualAppId and also get that create transaction (along with potentially other ones).

asset id

Asset id filtering's existing behavior returns transactions representing algo transfers, but it does not include asset creations (because like for application id, the created asset's id is what is stored underneath rather than a zero value).

The answer to the application id question above will likely apply here.

amount
This one is complex and related to indexer database storage choices, but think we see it. Basically, have to make sure looking at the correct currency amount field when filtering, and deal with Postgres's json handling / converting of null/empty values.

[nullun]

My apologies @gmalouf, I completely lost track of this issue.

application id

If someone specifies to filter on the application-id that was created, would you expect the creation transaction to be included in that query as well?

I would. I think the current response to a specific application-id is being helpful when including the creation transaction.

I think a weird behavior would be to filter for application_id=0, get that create transaction, filter for application_id=actualAppId and also get that create transaction (along with potentially other ones).

That's what I would have expected to happen to be honest. My intention for searching for application-id=0 was to retrieve just application deployments, knowing the transaction itself didn't have one set one.

asset id

Asset id filtering's existing behavior returns transactions representing algo transfers, but it does not include asset creations (because like for application id, the created asset's id is what is stored underneath rather than a zero value).

Ah yes, I see what you mean. In my case I am explicitly setting tx-type=axfer, so I only get asset transfer transaction results. Doing this and searching for asset-id=0 works correctly. It returns just those axfer transactions where the asset-id is 0 (albeit they're rare).

I think searching for all asset creations is slightly different, since I'd be setting tx-type=acfg, but as you've mentioned, now asset-id doesn't work because it's looking for created-asset-index instead. I would probably expect it to work the same way as I mentioned above. tx-type=acfg&asset-id=0 returns just asset creation transactions. But also asset-id=actualAssetId (without a tx-type) would include the asset creation transaction.

amount

Sounds like that's been sorted! 👍