All columns in the all_chainstable that can contain a hexadecimal string are stored in byte code. For example 👇

Screen Shot 2022-10-26 at 2.44.05 pm.png

These fields include:

  1. block_hash
  2. block_parent_hash
  3. tx_hash
  4. tx_sender
  5. tx_recipient
  6. log_emitter
  7. topic0 - topic3
  8. data0 - data3
  9. data_rest

There are two places in a query where these fields might need to be transformed from byte code into their hexadecimal representation (e.g 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2):

  1. SELECT hex(log_emitter) turns byte code (4�f�P��Fq) into it's hexadecimal form ('0xC02aaA39b2..')
  2. WHERE log_emitter = unhex('0xC02aaA39b2..') turns hexadecimal format ('0xC02aaA39b2..') into byte code (4�f�P��Fq)

As you can see, hex() is used in the SELECT statement and unhex() in the WHERE statement.

SELECT hex(tx_hash), tx_hash
FROM blockchains.all_chains
LIMIT 10
-- Run to see the effects of hex()

SELECT *
FROM blockchains.all_chains
WHERE tx_hash = unhex('ffffffbf8bcb4d1d30609a2fbc044e9e0f7101d06f332a73c88a7dff383cfa13')
-- Run to see the effects of unhex()