Repository navigation
Error: There isn't a common type to coerce Binary and Utf8 in LIKE expression #7342
Description
Describe the bug
On running the 4 queries below on the Clickbench multi file dataset,
SELECT COUNT(*) FROM hits WHERE "URL" LIKE '%google%';
SELECT "SearchPhrase", MIN("URL"), COUNT(*) AS c FROM hits WHERE "URL" LIKE '%google%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10;
we get this error,
type_coercion caused by Error during planning: There isn't a common type to coerce Binary and Utf8 in LIKE expressionTo Reproduce
Download the data using,
./benchmarks/bench.sh data clickbench_partitionedA hits_multi directory with the parquet files will be created.
Execute the above queries,
datafusion-cli -c "CREATE EXTERNAL TABLE hits STORED AS PARQUET LOCATION 'hits_multi';" "{query}"Expected behavior
The queries should run successfully without erroring.
Additional context
Datafusion 29.0.0
Activity
alamb commentedon Aug 21, 2023
This looks similar to #7039 which @jonahgao fixed by adding a coercion from binary --> UTF8 for comparison. I think we could do something similar here.
alamb commentedon Aug 21, 2023
Marking as a good first issue as there is a reproducer and I think the fix should be relatively straightforward
JayjeetAtGithub commentedon Aug 21, 2023
Working on a fix
JayjeetAtGithub commentedon Aug 23, 2023
I looked into this issue a little bit. Looks like there needs to be changes in
arrow-stringwhich is basically a part ofarrow-rs. Specifically, I found out that there is no support forlikeoperation for binary operands inarrow-datafusion. It only exists forutf8operands. Support for thelikeoperation inutf8stems from this code inarrow-string. We basically need to add a binary version of this function inarrow-rs. Does that sound correct ?alamb commentedon Aug 23, 2023
I am not quite sure
I think that
LIKEonly really makes sense on string (UTF-8) data.Thus, a better approach might be to try and automatically coerce (cast) the
BinaryArrayto aStringArray(which will verify that the input in theBinaryArrayis valid UTF-8) and then call the existing String based LIKE kernel