bug: WHERE clause not working as expected with null column values and string functions (like, in, etc)

[malwaregarry]

Search before asking

• I had searched in the issues and found no similar issues.

Version

8.0.26-v1.2.453-d7c9a83f6b(rust-1.77.0-nightly-2024-05-06T12:12:40.122758622Z)

What's Wrong?

Given a select query with WHERE clause on column containing null values passed into LIKE and IN functions, we do not get the corresponding result

Example:

CREATE TABLE t1(c1 int, c2 VARCHAR NULL);
INSERT INTO t1(c1) VALUES (5);

This query returns true:

SELECT ((t1.c2) IN ('1') IS NULL) from t1;

But we do not get any rows returned with this select query

SELECT t1.c1 FROM t1 WHERE ((t1.c2) IN ('1') IS NULL);

How to Reproduce?

CREATE TABLE t1(c1 int, c2 VARCHAR NULL);
INSERT INTO t1(c1) VALUES (5);

SELECT ((t1.c2) LIKE ('1') IS NULL) from t1; -- return 1
SELECT t1.c1 FROM t1 WHERE ((t1.c2) LIKE ('1') IS NULL); --  empty set

SELECT ((t1.c2) IN ('1') IS NULL) from t1;  -- return 1
SELECT t1.c1 FROM t1 WHERE ((t1.c2) IN ('1') IS NULL);  --  empty set

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[malwaregarry]

this could also be affected by comparison (=) and BETWEEN

[sundy-li]

🐳 :) explain SELECT t1.c1 FROM t1 WHERE ((t1.c2) IN ('1') IS NULL);
-[ EXPLAIN ]-----------------------------------
TableScan
├── table: default.default.t1
├── output columns: [c1 (#0)]
├── read rows: 0
├── read size: 0
├── partitions total: 1
├── partitions scanned: 0
├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 1 to 1, bloom pruning: 1 to 0>]
├── push downs: [filters: [NOT is_not_null(t1.c2 (#1) = '1')], limit: NONE]
└── estimated rows: 0.80

10 rows explain in 0.066 sec. Processed 0 rows, 0B (0 row/s, 0B/s)

Let's forbid the Expr::IsNull and Expr::Not in bf. @b41sh

[sundy-li]

same issue in #15572