Baymine commented on PR #66181:
URL: https://github.com/apache/doris/pull/66181#issuecomment-5174348131
> 我用ai分析了一下，好像rewrite的行为不一定是对的？
>
> element_at(split_by_string('abc', ','), 1) = 'abc'
>
> 三者实际行为如下：
>
> 表达式 结果 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ━━━━━━━━━ split_by_string('abc', ',')
['abc'] ───────────────────────────── ───────── element_at(['abc'], 1) 'abc'
───────────────────────────── ───────── split_part('abc', ',', 1) NULL
>
> 所以原谓词为 TRUE，PR 改写后为 NULL，Filter 会错误地过滤掉这一行。
>
> ```
> SELECT 'original_filter' AS test_case, COUNT(*) AS rows_kept
> FROM (
> SELECT CAST(number AS STRING) AS s
> FROM numbers("number" = "1")
> ) t
> WHERE element_at(split_by_string(s, ','), 1) = '0'
>
> UNION ALL
>
> SELECT 'rewritten_filter' AS test_case, COUNT(*) AS rows_kept
> FROM (
> SELECT CAST(number AS STRING) AS s
> FROM numbers("number" = "1")
> ) t
> WHERE split_part(s, ',', 1) = '0';
>
> +------------------+-----------+
> | test_case | rows_kept |
> +------------------+-----------+
> | original_filter | 1 |
> | rewritten_filter | 0 |
> +------------------+-----------+
> ```
Thx for review. Are you running the latest master branch HEAD? There appears
to be divergent behavior for this function between the master branch and the
4.0.5 release tag, `select split_part(0, ',', 1) = '0';`:
```sql
4.0:
> select split_part(0, ',', 1)
+----------------------------------------------+
| split_part(cast(0 as VARCHAR(65533)), ',', 1) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
1 row in set
2.1.6
> select split_part(0, ',', 1);
+-----------------------------------------------+
| split_part(cast(0 as VARCHAR(65533)), ',', 1) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
master:
> select split_part(0, ',', 1)
+-----------------------+
| split_part(0, ',', 1) |
+-----------------------+
| <null> |
+-----------------------+
```
I will investigate that further.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
