modules/calcite/src/test/sql/join/test_not_distinct_from.test

# name: test/sql/join/test_not_distinct_from.test
# description: Test join on is not distinct from query
# group: [join]


statement ok
create table tbl_1 (a integer, b integer)

statement ok
insert into tbl_1 values (1,NULL),(2,3),(NULL,NULL)

statement ok
create table tbl_2 (b integer)

statement ok
insert into tbl_2 values (1),(2),(NULL)

query I rowsort
SELECT a from tbl_1 WHERE a IS DISTINCT FROM b
----
1
2

query I
SELECT a from tbl_1 WHERE a IS NOT DISTINCT FROM b
----
NULL

query II rowsort
select a,tbl_2.b from tbl_1 inner join tbl_2 on (a IS NOT DISTINCT FROM tbl_2.b)
----
1	1
2	2
NULL	NULL

query II
select a,tbl_2.b from tbl_1 inner join tbl_2 on (a IS DISTINCT FROM tbl_2.b) order by a,tbl_2.b
----
NULL	1
NULL	2
1	NULL
1	2
2	NULL
2	1

#Vector with vector
query III
select a.a, b.b, a.a IS NOT DISTINCT FROM b.b AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES(1), (2), (NULL)) AS b (b) ORDER BY a.a NULLS LAST;
----
1	1	true
1	2	false
1	NULL	false
2	1	false
2	2	true
2	NULL	false
NULL	1	false
NULL	2	false
NULL	NULL	true

query III
select a.a, b.b, a.a IS DISTINCT FROM b.b AS "Is Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b) ORDER BY a.a NULLS LAST;
----
1	1	false
1	2	true
1	NULL	true
2	1	true
2	2	false
2	NULL	true
NULL	1	true
NULL	2	true
NULL	NULL	false

#constant with constant
query I
select null is not distinct from null;
----
true

query I
select null is distinct from null;
----
false

# Vector with constant
query II
select a.a, a.a IS NOT DISTINCT FROM 1 AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a) ORDER BY a.a NULLS LAST;
----
1	true
2	false
NULL	false

query II
select a.a, a.a IS DISTINCT FROM 1 AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a) ORDER BY a.a NULLS LAST;
----
1	false
2	true
NULL	true

query II
select a.a, a.a IS NOT DISTINCT FROM NULL AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a) ORDER BY a.a NULLS LAST;
----
1	false
2	false
NULL	true

query II
select a.a, a.a IS DISTINCT FROM NULL AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a) ORDER BY a.a NULLS LAST;
----
1	true
2	true
NULL	false

# More types (all numeric types, strings, dates, times, timestamps, interval)
statement ok
CREATE TABLE test_types(a tinyint, b smallint, c integer, d bigint, e double, f real, g varchar);

statement ok
INSERT INTO test_types values (NULL,NULL,NULL,NULL,NULL,NULL,NULL),(1,1,1,1,1,1,'1'),(10,10,10,10,10,10,'10')

query I
select count(*) FROM test_types where a IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where b IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where c IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where d IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where e IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where f IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where g IS DISTINCT FROM NULL
----
2

query I
select count(*) FROM test_types where a IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where b IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where c IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where d IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where e IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where f IS NOT DISTINCT FROM NULL
----
1

query I
select count(*) FROM test_types where g IS NOT DISTINCT FROM NULL
----
1

query T
SELECT INTERVAL '30' DAY is distinct from NULL
----
true

query T
SELECT INTERVAL '30' DAY is not distinct from NULL
----
false

# Use on a bigger table (~10K elements)
statement ok
CREATE table big(a int);

statement ok
insert into big select x from table(system_range(1, 9999));

statement ok
insert into big values (NULL)

query I
select count(*) from big  inner join tbl_2 on (a IS NOT DISTINCT FROM tbl_2.b)
----
3

# Use with filter (e.g. SELECT x IS NOT DISTINCT FROM y FROM tbl WHERE z%2=0, where the expression filters like 50% of the rows)
query I
select count(*) from big  inner join tbl_2 on (a IS NOT DISTINCT FROM tbl_2.b) where a >1  and a < 5000
----
1

# Use in subqueries with correlated expressions
query II rowsort
SELECT a, (select count(*) from tbl_1 where tbl_1.a is distinct from t.b) from tbl_1 as t ORDER BY a NULLS LAST
----
1	2
2	3
NULL	2