apache / datafusion

Apache DataFusion SQL Query Engine
https://datafusion.apache.org/
Apache License 2.0
5.47k stars 1.01k forks source link

Implement `LIKE` for StringView arrays #11024

Open alamb opened 1 week ago

alamb commented 1 week ago

Is your feature request related to a problem or challenge?

Part of https://github.com/apache/datafusion/issues/10918 where we are integrating StringView into DataFusion, initially targeting making ClickBench queries faster

In the ClickBench queries there are several LIKE predicates on String columns such as

SELECT COUNT(*) FROM hits WHERE "URL" LIKE '%google%';

https://github.com/apache/datafusion/blob/5bfc11ba4ac4f11eaf9793c668e4a064fb697e6e/benchmarks/queries/clickbench/queries.sql#L21-L24

Describe the solution you'd like

Given a table with StringView data such as:

> CREATE OR REPLACE TABLE string_views AS VALUES (arrow_cast('Andrew', 'Utf8View'), 'A Much Longer String Than 12 Characters',  'Nonsense', 'A Much', NULL);
0 row(s) fetched.
Elapsed 0.006 seconds.

> select arrow_typeof(column1) from string_views limit 1;
+------------------------------------+
| arrow_typeof(string_views.column1) |
+------------------------------------+
| Utf8View                           |
+------------------------------------+
1 row(s) fetched.
Elapsed 0.012 seconds.

I would like queries using LIKE and NOT LIKE to work:

> select column1 from string_views WHERE column1 LIKE 'A%';
Arrow error: Invalid argument error: Invalid string operation: Utf8View LIKE Utf8View
> select column1 from string_views WHERE column1 NOT LIKE 'A%';
Arrow error: Invalid argument error: Invalid string operation: Utf8View NLIKE Utf8View
> select column1 from string_views WHERE column1 LIKE '%e';
Arrow error: Invalid argument error: Invalid string operation: Utf8View LIKE Utf8View
> select column1 from string_views WHERE column1 LIKE '%A%';
Arrow error: Invalid argument error: Invalid string operation: Utf8View LIKE Utf8View
>

Describe alternatives you've considered

We could add a coercion rule to LIKE to automatically coerce Utf8View to Utf8, however that is inefficient as it will involve copying all the strings. It would be much better to actually implement LIKE for Utf8View arrays directly

Currently LIKE and ILIKE are implemented in arrow-rs kernels, such as https://docs.rs/arrow/latest/arrow/compute/kernels/comparison/fn.like.html

The DataFusion implementation is here: https://github.com/apache/datafusion/blob/main/datafusion/physical-expr/src/expressions/like.rs

I think there are two potential implementations:

  1. Add special case code to datafusion (at least temporarily)
  2. Add support upstream in arrow-rs

Additional context

Please remember to target the string-view branch in DataFusion, rather than main with your PR

XiangpengHao commented 1 week ago

take