Repository navigation
where clause incorrectly reject NULL literal (by SQLancer-NoREC) #11248
Description
Describe the bug
DataFusion CLI v39.0.0
> select 1 where null;
Error during planning: Cannot create filter with non-boolean predicate 'NULL' returning NullThis query should return an empty set instead of planning error:
NULL in SQL means the absence of a value, not a specific type.
In the above query, it should interpret NULL as a missing boolean value.
PostgreSQL reference:
postgres=# select 1 where null;
?column?
----------
(0 rows)DataFusion also exeucte it correctly if Null value is within a column
DataFusion CLI v39.0.0
> create table t1(v1 boolean);
0 row(s) fetched.
Elapsed 0.076 seconds.
> insert into t1 values (true),(false),(NULL);
+-------+
| count |
+-------+
| 3 |
+-------+
1 row(s) fetched.
Elapsed 0.055 seconds.
> select * from t1;
+-------+
| v1 |
+-------+
| true |
| false |
| |
+-------+
3 row(s) fetched.
Elapsed 0.005 seconds.
> select * from t1 where t1.v1;
+------+
| v1 |
+------+
| true |
+------+
1 row(s) fetched.
Elapsed 0.010 seconds.Found by SQLancer's NoREC oracle #11030
To Reproduce
No response
Expected behavior
No response
Additional context
No response
Activity
xinlifoobar commentedon Jul 4, 2024
This seems not an easy fix... It would break about 34 sqllogictests.