You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit f031ee7
Browse filesBrowse the repository at this point in the historyBrowse files
fix: Decide NOT IN null-aware joins from expression nullability, not just column nullability
closes: apache#25473closes: apache#25474
`join_keys_may_be_null` only checked the schema nullability of the columns
a join filter references. A NULL constant or an expression that is nullable
even though every column it reads is NOT NULL (CASE without ELSE, NULLIF,
arithmetic with a NULL operand) was therefore never treated as needing
null-aware semantics, so a NOT IN predicate that should be UNKNOWN silently
kept the outer row instead.
Replace the column walk with a per-equality-conjunct nullability check that
evaluates each operand as an expression against whichever schema it
resolves to, falling back to nullable when that can't be determined.
# An outer expression that is nullable even though the only column it reads
1024
+
# (`x`) is `NOT NULL`. `on2` is the same data with a nullable `x`, as a
1025
+
# control that this was already correct via column nullability.
1026
+
statement ok
1027
+
CREATE TABLE nainull_e_o(x INT NOT NULL) AS VALUES (1), (2);
1028
+
1029
+
statement ok
1030
+
CREATE TABLE nainull_e_o2(x INT) AS VALUES (1), (2);
1031
+
1032
+
statement ok
1033
+
CREATE TABLE nainull_e_i(id INT NOT NULL) AS VALUES (1), (3);
1034
+
1035
+
# `CASE` without `ELSE` is NULL for every row here.
1036
+
query I
1037
+
SELECT x FROM nainull_e_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nainull_e_i) ORDER BY x;
1038
+
----
1039
+
1040
+
query I
1041
+
SELECT x FROM nainull_e_o WHERE NULLIF(x, x) NOT IN (SELECT id FROM nainull_e_i) ORDER BY x;
1042
+
----
1043
+
1044
+
query I
1045
+
SELECT x FROM nainull_e_o WHERE x + CAST(NULL AS INT) NOT IN (SELECT id FROM nainull_e_i) ORDER BY x;
1046
+
----
1047
+
1048
+
query I
1049
+
SELECT x FROM nainull_e_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nainull_e_i) OR x = 99 ORDER BY x;
1050
+
----
1051
+
1052
+
# Same expression, but the column it reads is nullable: must give the same
1053
+
# (correct) result as the `NOT NULL` cases above.
1054
+
query I
1055
+
SELECT x FROM nainull_e_o2 WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nainull_e_i) ORDER BY x;
1056
+
----
1057
+
1058
+
query TT
1059
+
EXPLAIN SELECT x FROM nainull_e_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nainull_e_i);
1060
+
----
1061
+
logical_plan
1062
+
01)LeftAnti Join: CASE WHEN nainull_e_o.x > Int32(100) THEN Int64(1) END = __correlated_sq_1.nainull_e_i.id null_aware
1063
+
02)--TableScan: nainull_e_o projection=[x]
1064
+
03)--SubqueryAlias: __correlated_sq_1
1065
+
04)----Projection: CAST(nainull_e_i.id AS Int64)
1066
+
05)------TableScan: nainull_e_i projection=[id]
1067
+
physical_plan
1068
+
01)HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(nainull_e_i.id@0, CASE WHEN nainull_e_o.x > Int32(100) THEN Int64(1) END@1)], projection=[x@0], null_aware
1069
+
02)--ProjectionExec: expr=[CAST(id@0 AS Int64) as nainull_e_i.id]
0 commit comments