Search code examples
sqlperformancevarbinarydunedune-analytics

Left side of LIKE expression must evaluate to a varchar (actual: varbinary). What's an alternative to converting from varbinary to varchar?

I have the following query on dune.com:

SELECT
  th.value / 1e18 as amount,
  tr.success,
  th."from",
  th.to,
  tr.hash,
  tr.data,
FROM
  table1 tr
  table2 th ON tr.hash = th.evt_hash
WHERE
  th.to = sjhd21
  AND tr.success = true
  AND (
    th.value / 1e18 > 10
    OR th."from" = h123g
  ) 
  AND CAST(tr.data AS varchar) NOT LIKE '0xbc4b3365%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x447e346f%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x0100670b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x2d1fb389%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xb9181611%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x47e7ef24%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xdb6b5246%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xf80dec97%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x8129fc1c%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x8da5cb5b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x7729d644%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xd6c9b6a5%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x143531c0%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x715018a6%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x4fb2e45d%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xf2fde38b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x4f065632%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x7a78b9c7%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x535b355c%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x9c66c25d%'

This query takes a long time to process. I assume this is because I'm converting the data column in the WHERE clause.

If I don't convert it I get this error:

Left side of LIKE expression must evaluate to a varchar (actual: varbinary)

I have tried using a CTE to convert the data but it still takes just as long so I gave up on that.

What's an alternative to this? Is there an expression that will work with data as varbinary?

Solution

  • The casting on the left hand argument of the (not) like is quite probably the cause of the slowness, especially if that column is indexed.

    Looking at the conditions you have there, you aren't really using the full-blown power of the like operator, but only using it to check for a prefix. This can be done more efficiently with the varbinary_starts_with function:

    AND NOT varbinary_starts_with(tr.data, 0xbc4b3365)
-- etc...