You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "goldmedal (via GitHub)" <gi...@apache.org> on 2024/09/26 15:56:40 UTC
[I] The LIKE and ILIKE behavior for NULL handling in StringView differs from other string types [datafusion]
goldmedal opened a new issue, #12637:
URL: https://github.com/apache/datafusion/issues/12637
### Describe the bug
While working on #12415, I found the `LIKE` and `ILIKE` behavior differs between `StringView` and other string types. Given the following data and SQL:
```sql
DataFusion CLI v42.0.0
> create table test_source as values
('Andrew', 'X', 'datafusion📊🔥', '🔥'),
('Xiangpeng', 'Xiangpeng', 'datafusion数据融合', 'datafusion数据融合'),
('Raphael', 'R', 'datafusionДатаФусион', 'аФус'),
(NULL, 'R', NULL, '🔥');
0 row(s) fetched.
Elapsed 0.028 seconds.
> create table test_basic_operator_utf8view as
select
arrow_cast(column1, 'Utf8View') as ascii_1,
arrow_cast(column2, 'Utf8View') as ascii_2,
arrow_cast(column3, 'Utf8View') as unicode_1,
arrow_cast(column4, 'Utf8View') as unicode_2
from test_source;
0 row(s) fetched.
Elapsed 0.002 seconds.
> create table test_basic_operator_utf8 as
select
arrow_cast(column1, 'Utf8') as ascii_1,
arrow_cast(column2, 'Utf8') as ascii_2,
arrow_cast(column3, 'Utf8') as unicode_1,
arrow_cast(column4, 'Utf8') as unicode_2
from test_source;
0 row(s) fetched.
Elapsed 0.004 seconds.
-- StringView Table ( pattern contain % )
> select ascii_1, unicode_1,
ascii_1 like 'An%' as ascii_like,
unicode_1 like '%ion数据%' as unicode_like,
ascii_1 ilike 'An%' as ascii_ilike,
unicode_1 ilike '%ion数据%' as unicode_ilik
from test_basic_operator_utf8view;
+-----------+----------------------+------------+--------------+-------------+--------------+
| ascii_1 | unicode_1 | ascii_like | unicode_like | ascii_ilike | unicode_ilik |
+-----------+----------------------+------------+--------------+-------------+--------------+
| Andrew | datafusion📊🔥 | true | false | true | false |
| Xiangpeng | datafusion数据融合 | false | true | false | true |
| Raphael | datafusionДатаФусион | false | false | false | false |
| | | false | false | false | |
+-----------+----------------------+------------+--------------+-------------+--------------+
4 row(s) fetched.
Elapsed 0.004 seconds.
-- StringView Table ( pattern without % )
> select ascii_1, unicode_1,
ascii_1 like 'An' as ascii_like,
unicode_1 like '%ion数据' as unicode_like,
ascii_1 ilike 'An' as ascii_ilike,
unicode_1 ilike 'ion数据' as unicode_ilik
from test_basic_operator_utf8view;
+-----------+----------------------+------------+--------------+-------------+--------------+
| ascii_1 | unicode_1 | ascii_like | unicode_like | ascii_ilike | unicode_ilik |
+-----------+----------------------+------------+--------------+-------------+--------------+
| Andrew | datafusion📊🔥 | false | false | false | false |
| Xiangpeng | datafusion数据融合 | false | false | false | false |
| Raphael | datafusionДатаФусион | false | false | false | false |
| | | | false | | |
+-----------+----------------------+------------+--------------+-------------+--------------+
4 row(s) fetched.
Elapsed 0.004 seconds.
-- String Table (same as LargeString and DictionaryString)
> select ascii_1, unicode_1,
ascii_1 like 'An%' as ascii_like,
unicode_1 like '%ion数据%' as unicode_like,
ascii_1 ilike 'An%' as ascii_ilike,
unicode_1 ilike '%ion数据%' as unicode_ilik
from test_basic_operator_utf8;
+-----------+----------------------+------------+--------------+-------------+--------------+
| ascii_1 | unicode_1 | ascii_like | unicode_like | ascii_ilike | unicode_ilik |
+-----------+----------------------+------------+--------------+-------------+--------------+
| Andrew | datafusion📊🔥 | true | false | true | false |
| Xiangpeng | datafusion数据融合 | false | true | false | true |
| Raphael | datafusionДатаФусион | false | false | false | false |
| | | | | | |
+-----------+----------------------+------------+--------------+-------------+--------------+
4 row(s) fetched.
Elapsed 0.004 seconds.
```
When the input value is NULL, string type will return `NULL` but string view will return false. (Something is interesting about the ILIKE operation is different between ASCII-only and Unicode `StringView` 🤔 )
## Some testing for StringView ScalarValue
When the matching pattern contains `%`, it will return `false` instead `null`.
```sql
> select
arrow_cast(null, 'Utf8View') like '123' as utf8view_like_ascii,
arrow_cast(null, 'Utf8View') ilike '123' as utf8view_ilike_ascii,
arrow_cast(null, 'Utf8View') like '123%' as "utf8view_like_ascii%",
arrow_cast(null, 'Utf8View') ilike '123%' as "utf8view_ilike_ascii%";
+---------------------+----------------------+----------------------+-----------------------+
| utf8view_like_ascii | utf8view_ilike_ascii | utf8view_like_ascii% | utf8view_ilike_ascii% |
+---------------------+----------------------+----------------------+-----------------------+
| | | false | false |
+---------------------+----------------------+----------------------+-----------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select
arrow_cast(null, 'Utf8View') like '數據' as "utf8view_like_unicode",
arrow_cast(null, 'Utf8View') ilike '數據' as "utf8view_ilike_unicode",
arrow_cast(null, 'Utf8View') like '數據%' as "utf8view_like_unicode%",
arrow_cast(null, 'Utf8View') ilike '數據%' as "utf8view_ilike_unicode%";
+-----------------------+------------------------+------------------------+-------------------------+
| utf8view_like_unicode | utf8view_ilike_unicode | utf8view_like_unicode% | utf8view_ilike_unicode% |
+-----------------------+------------------------+------------------------+-------------------------+
| | | false | |
+-----------------------+------------------------+------------------------+-------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
## Some testing for String ScalarValue (Same as LargeString and DictionaryString)
```sql
> select
arrow_cast(null, 'Utf8') like '123' as utf8_like_ascii,
arrow_cast(null, 'Utf8') ilike '123' as utf8_ilike_ascii,
arrow_cast(null, 'Utf8') like '123%' as "utf8_like_ascii%",
arrow_cast(null, 'Utf8') ilike '123%' as "utf8_ilike_ascii%";
+-----------------+------------------+------------------+-------------------+
| utf8_like_ascii | utf8_ilike_ascii | utf8_like_ascii% | utf8_ilike_ascii% |
+-----------------+------------------+------------------+-------------------+
| | | | |
+-----------------+------------------+------------------+-------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select
arrow_cast(null, 'Utf8') like '數據' as "utf8_like_unicode",
arrow_cast(null, 'Utf8') ilike '數據' as "utf8_ilike_unicode",
arrow_cast(null, 'Utf8') like '數據%' as "utf8_like_unicode%",
arrow_cast(null, 'Utf8') ilike '數據%' as "utf8_ilike_unicode%";
+-------------------+--------------------+--------------------+---------------------+
| utf8_like_unicode | utf8_ilike_unicode | utf8_like_unicode% | utf8_ilike_unicode% |
+-------------------+--------------------+--------------------+---------------------+
| | | | |
+-------------------+--------------------+--------------------+---------------------+
1 row(s) fetched.
Elapsed 0.002 seconds.
````
### To Reproduce
Run the SQLs mentioned above.
### Expected behavior
I'm not really sure if the behavior of StringView is expected 🤔 but I think their behavior should be consistent.
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2450164587
LIKE -- https://github.com/apache/arrow-rs/pull/6662
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2457058113
Interesting, I haven't checked the source code but I tried different numbers of the `%` char. Only one and two will be wrong.
```
> select arrow_cast(null, 'Utf8View') like '%';
+--------------------------------------------------+
| arrow_cast(NULL,Utf8("Utf8View")) LIKE Utf8("%") |
+--------------------------------------------------+
| true |
+--------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select arrow_cast(null, 'Utf8View') like '%%';
+---------------------------------------------------+
| arrow_cast(NULL,Utf8("Utf8View")) LIKE Utf8("%%") |
+---------------------------------------------------+
| true |
+---------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select arrow_cast(null, 'Utf8View') like '%%%';
+----------------------------------------------------+
| arrow_cast(NULL,Utf8("Utf8View")) LIKE Utf8("%%%") |
+----------------------------------------------------+
| |
+----------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select arrow_cast(null, 'Utf8View') like '%%%%';
+-----------------------------------------------------+
| arrow_cast(NULL,Utf8("Utf8View")) LIKE Utf8("%%%%") |
+-----------------------------------------------------+
| |
+-----------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
It's a nice find 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2449571495
> @goldmedal can you please change issue title to something like `` Incorrect LIKE and ILIKE result for NULL input and `%` pattern ``?
Thanks for the suggestion 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] The LIKE and ILIKE behavior for NULL handling in StringView differs from other string types [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2449352017
Concise repro. This should return NULL
```
> select arrow_cast(null, 'Utf8') LIKE '%';
+----------------------------------------------+
| arrow_cast(NULL,Utf8("Utf8")) LIKE Utf8("%") |
+----------------------------------------------+
| true |
+----------------------------------------------+
```
@goldmedal can you please change issue title to something like ``Incorrect LIKE and ILIKE result for NULL input and `%` pattern``?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2454601791
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2456877116
For cases when pattern is static (majority), we can partially fix this issue by fixing a bug in the simplifier, and thus avoiding buggy execution path: https://github.com/apache/datafusion/pull/13259
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2468852215
> I plan to take care of ILIKE as well, once the above is accepted.
done in https://github.com/apache/arrow-rs/pull/6705
should we close this issue when we update arrow-rs dep?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] The LIKE and ILIKE behavior for NULL handling in StringView differs from other string types [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2377893100
> I'm not really sure if the behavior of StringView is expected 🤔 but I think their behavior should be consistent.
> When the input value is NULL, string type will return NULL but string view will return false. (Something is interesting about the ILIKE operation is different between ASCII-only and Unicode StringView 🤔 )
I think if the input is null, the string view should also return null
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #12637: Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern
URL: https://github.com/apache/datafusion/issues/12637
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2472554317
Marked as waiting on upstream
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] The LIKE and ILIKE behavior for NULL handling in StringView differs from other string types [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2378241398
> I think if the input is null, the string view should also return null
Thanks! I updated the expected behavior 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input, `%', and '%%' pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2457212670
It looks like a missed optimization opportunity. `%`, `%%`, `%...%%` patterns should all behave the same.
in https://github.com/apache/datafusion/pull/13260 i am adding a simplifier to make them the same.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2456941265
@goldmedal can you maybe edit issue title once again to cover `%%` patterns too?
`%` **static** patterns are being fixed in https://github.com/apache/datafusion/pull/13259
`%%` test cases in https://github.com/apache/datafusion/pull/13259 are commented out pointing at this issue
(i will see maybe i can fix repeated percent there too?)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect LIKE and ILIKE result for NULL input and `%` pattern [datafusion]
Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #12637:
URL: https://github.com/apache/datafusion/issues/12637#issuecomment-2454601853
I plan to take care of ILIKE as well, once the above is accepted.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org