Odd mismatch between reward table abd block table

[erikd]

On mainnent, a simple query of the rewards table:

cexplorer=# select reward.* from pool_hash
              inner join reward on reward.pool_id = pool_hash.id
              where pool_hash.hash_raw = '\xbd17f6db0e4ac339ba552216969d70d6cd6c31e0266055f365b98caf' ;
   id   | addr_id |   amount   | epoch_no | pool_id | block_id 
--------+---------+------------+----------+---------+----------
 367982 |   99440 | 1073789826 |      220 |    1189 |  4789479
(1 row)

which suggests that the pool with pool_id == 1189 minted a block in epoch 220.

However no blocks are found to have neen minted by that pool:

cexplorer=# select block.block_no, slot_leader.pool_hash_id
              from block inner join slot_leader on block.slot_leader = slot_leader.id
              where pool_hash_id = 1189 ;
 block_no | pool_hash_id 
----------+--------------
(0 rows)

[erikd]

Lets look at the delegation for the specific stake address:

cexplorer=# select * from delegation where addr_id = 99440 ; 
  id   | addr_id | cert_index | pool_hash_id | active_epoch_no |  tx_id  
-------+---------+------------+--------------+-----------------+---------
 84980 |   99440 |          1 |         1189 |             220 | 2734364
 85885 |   99440 |          0 |         1225 |             220 | 2738424

so this address delegated twice in the same epoch, with the both delegations becoming active in epoch 220 and the second pool did indeed mint a block:

cexplorer=# select block.block_no, block.epoch_no, slot_leader.pool_hash_id
              from block inner join slot_leader on block.slot_leader = slot_leader.id
              where pool_hash_id = 1225 ;
 block_no | epoch_no | pool_hash_id 
----------+----------+--------------
  4746137 |      220 |         1225
(1 row)

And searching for other rewards for this address in epoch 220 in the rewards table:

cexplorer=# select * from reward where addr_id = 99440 and epoch_no = 220 ;
   id   | addr_id |   amount   | epoch_no | pool_id | block_id 
--------+---------+------------+----------+---------+----------
 367982 |   99440 | 1073789826 |      220 |    1189 |  4789479
(1 row)

only shows the incorrect reward entry.

That means this issue is due to the pool_id column being incorrectly populated.

[erikd]

Ok:

cexplorer=# SELECT reward.*
              FROM pool_hash inner JOIN reward ON reward.pool_id = pool_hash.id
              WHERE pool_hash.hash_raw = '\xbd17f6db0e4ac339ba552216969d70d6cd6c31e0266055f365b98caf' ;
 id | addr_id | amount | epoch_no | pool_id | block_id 
----+---------+--------+----------+---------+----------
(0 rows)

and

cexplorer=# select * from reward where addr_id = 99440 and epoch_no = 220 ;
   id   | addr_id |   amount   | epoch_no | pool_id | block_id 
--------+---------+------------+----------+---------+----------
 367982 |   99440 | 1073789826 |      220 |    1225 |  4817920
(1 row)

[dmitrystas]

Looks good!