[BUG] - pool_owner.hash cannot be joined with stake_address.hash_raw

[tdiesler]

Other than pool_update.reward_addr and epoch_stake.addr_id, pool_owner.hash cannot be joined with stake_address.hash_raw.

Is this intentionally? Perhaps there is another way to get at the BECH32 for a pool owner?

[erikd]

Why can't pool_owner.hash be joined with stake_address.hash_raw ?

Ahh, now I see it. stake_address.hash_raw is 33 bytes (because if prepends a byte indicating the network) when pool_owner.hash is a 32 byte hash.

Yes, that is a pain in the neck!. The pool_owner.hash should be replaced with an index into the stake_address table. That would make joins easy and obvious.

[tdiesler]

Merci. Would it be safe to prepend the pool_owner.hash with '\xe1' for now?

[erikd]

Yes, that would help for now. Also, please post the working query here.

[tdiesler]

Here you go ... it actually removes the prefix from both and does a string compare

# Pool Update
SELECT pu.id, pu.active_epoch_no epoch, pu.meta_id, pu.pledge, pu.margin, sar.view reward_addr, sao.view owner_addr
FROM pool_update pu
LEFT JOIN pool_owner po ON pu.registered_tx_id = po.registered_tx_id
INNER JOIN stake_address sar ON pu.reward_addr = sar.hash_raw
INNER JOIN stake_address sao ON substring(cast(po.hash as varchar), 3) = substring(cast(sao.hash_raw as varchar), 5)
WHERE hash_id = 1806
ORDER BY pu.id DESC

I use the H2 Console for this

[tdiesler]

simplified, like this ...

SELECT pu.active_epoch_no epoch, pu.meta_id, pu.pledge, pu.margin, sar.view reward_addr, sao.view owner_addr
FROM pool_update pu
JOIN pool_owner po ON pu.registered_tx_id = po.registered_tx_id
JOIN stake_address sar ON pu.reward_addr = sar.hash_raw
JOIN stake_address sao ON substring(cast(po.hash as varchar), 3) = substring(cast(sao.hash_raw as varchar), 5)
WHERE pu.hash_id = 1806
ORDER BY pu.id DESC