nft_custody view does not take into consideration event_index

aulneau commented 2 years ago

if you filter the data in nft_events to show events for asset_identifier: SP2KAF9RF86PVX3NEE27DFV1CQX0T4WGR41X3S45C.byzantion-stacks-parrots::stacks-parrots value: \x01000000000000000000000000000002b3

You'll see that it shows two rows, the first one being a mint, and the second one being a transfer (to a burn address). These both happened in the same tx/block.

[
  {
    "id": 208062,
    "event_index": 33,
    "tx_id": "\\xc780ed436bb4581def2b5cb88b3467f573ee68a09e57160edb72f02bb6329073",
    "tx_index": 15,
    "block_height": 38876,
    "index_block_hash": "\\xdc99a85822e35e7bb34d8c64ae0e549c475fb0095724d1e5ae6ce5551c4cd04d",
    "parent_index_block_hash": "\\xb37e482d3f59eaec59a4d030a72fce5394b465a0930c6b6479ed1c0cb512f10c",
    "microblock_hash": "\\x",
    "microblock_sequence": 2147483647,
    "microblock_canonical": true,
    "canonical": true,
    "asset_event_type_id": 2,
    "asset_identifier": "SP2KAF9RF86PVX3NEE27DFV1CQX0T4WGR41X3S45C.byzantion-stacks-parrots::stacks-parrots",
    "value": "\\x01000000000000000000000000000002b3",
    "sender": null,
    "recipient": "SP3K22XKPT9WJFCE957J94J6XXVZHP7747YNPDTFD"
  },
  {
    "id": 208064,
    "event_index": 35,
    "tx_id": "\\xc780ed436bb4581def2b5cb88b3467f573ee68a09e57160edb72f02bb6329073",
    "tx_index": 15,
    "block_height": 38876,
    "index_block_hash": "\\xdc99a85822e35e7bb34d8c64ae0e549c475fb0095724d1e5ae6ce5551c4cd04d",
    "parent_index_block_hash": "\\xb37e482d3f59eaec59a4d030a72fce5394b465a0930c6b6479ed1c0cb512f10c",
    "microblock_hash": "\\x",
    "microblock_sequence": 2147483647,
    "microblock_canonical": true,
    "canonical": true,
    "asset_event_type_id": 1,
    "asset_identifier": "SP2KAF9RF86PVX3NEE27DFV1CQX0T4WGR41X3S45C.byzantion-stacks-parrots::stacks-parrots",
    "value": "\\x01000000000000000000000000000002b3",
    "sender": "SP3K22XKPT9WJFCE957J94J6XXVZHP7747YNPDTFD",
    "recipient": "SP000000000000000000002Q6VF78"
  }
]

the /holdings view shows the owner of the asset as SP3K22XKPT9WJFCE957J94J6XXVZHP7747YNPDTFD

   {
      "asset_identifier": "SP2KAF9RF86PVX3NEE27DFV1CQX0T4WGR41X3S45C.byzantion-stacks-parrots::stacks-parrots",
      "value": {
        "hex": "0x01000000000000000000000000000002b3",
        "repr": "u691"
      },
      "tx_id": "0xc780ed436bb4581def2b5cb88b3467f573ee68a09e57160edb72f02bb6329073"
    },

I believe the issue is in the first query of the nft_custody materialized view:

SELECT
      DISTINCT ON(asset_identifier, value) asset_identifier, value, recipient, tx_id, nft.block_height
    FROM
      nft_events AS nft -- this takes the first event associated with a given NFT, if there are more than 1, it will ignore the remainder
    INNER JOIN
      txs USING (tx_id)
    WHERE
      txs.canonical = true
      AND txs.microblock_canonical = true
      AND nft.canonical = true
      AND nft.microblock_canonical = true
    ORDER BY
      asset_identifier,
      value,
      txs.block_height DESC,
      txs.microblock_sequence DESC,
      txs.tx_index DESC -- this takes into account tx_index, but not event_index

aulneau commented 2 years ago

In my tests, it looks like this fixes it:

select * from(SELECT DISTINCT ON (asset_identifier, value) asset_identifier,
                                             value,
                                             recipient,
                                             tx_id,
                                             event_index,
                                             nft.block_height
FROM nft_events AS nft
         INNER JOIN
     txs USING (tx_id)
  where txs.canonical = true
  AND txs.microblock_canonical = true
  AND nft.canonical = true
  AND nft.microblock_canonical = true
ORDER BY asset_identifier,
         value,
         txs.block_height DESC,
         txs.microblock_sequence DESC,
         txs.tx_index DESC,
         event_index DESC) nft_custody
WHERE nft_custody.recipient = 'SP3K22XKPT9WJFCE957J94J6XXVZHP7747YNPDTFD'
  and nft_custody.asset_identifier = 'SP2KAF9RF86PVX3NEE27DFV1CQX0T4WGR41X3S45C.byzantion-stacks-parrots::stacks-parrots'

saralab commented 2 years ago

Done and dusted

blockstack-devops commented 2 years ago

:tada: This issue has been resolved in version 2.2.0-beta.1 :tada:

The release is available on:

Your semantic-release bot :package::rocket:

blockstack-devops commented 2 years ago

:tada: This issue has been resolved in version 3.0.0-beta.1 :tada:

The release is available on:

Your semantic-release bot :package::rocket:

blockstack-devops commented 2 years ago

:tada: This issue has been resolved in version 3.0.0 :tada:

The release is available on:

Your semantic-release bot :package::rocket:

hirosystems / stacks-blockchain-api

nft_custody view does not take into consideration event_index #1060