How to solve this SQL error varbinary = varchar(42)

Question

I am trying to learn something new, might I know what is the error message mean?

I tried to change the code in below, then work fine. What is the difference between them?

Answer 1

Simply, Dune is picky about the type of the arguments you give him, then 0x40, 64, and '@' can't be compared. (Note that all these values can be compared on MySQL, but, 64=0x40, 64='64' and 0x40='@' which can be weird : a single number written in different ways equals different strings).

Type strictness can be handy, since it avoids errors which can be easilly detected by the computer. I tend to prefer such languages.

Note that if you type SELECT '\x1234', you have the result \x1234, a 6 characters string, not exactly what you would mean. This is confirmed with select length('\x1234'); (6). The \ is not interpreted.

Note that on MySQL, select length ('\\') returns 1, and on Dune returns 2. It seems \ has not the common behaviour of SQL.

Answer 2

Use from_hex('0xc2132d05d31c914a87c6611c10748aeb04b58e8f') method to convert varchar to varbinary in Dune.

from_hex(varchar) → varbinary :

Converts a varbinary expression in datatype string to varbinary datatype

Example:

SELECT * 
FROM ethereum.transactions tx
WHERE tx."from" = from_heb('0xc2132d05d31c914a87c6611c10748aeb04b58e8f')

For more info read the official doc:

https://dune.com/docs/query/DuneSQL-reference/Functions-and-operators/varbinary/#from_utf8