modules/calcite/src/test/sql/function/generic/test_in.test

# name: test/sql/function/generic/test_in.test
# description: Test IN statement
# group: [generic]

statement ok
CREATE TABLE integers(i INTEGER)

statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL);

query I
SELECT * FROM integers WHERE i IN (1, 2) ORDER BY i
----
1
2

query I
SELECT * FROM integers WHERE i IN (1, 2, 3, 4, 5, 6, 7, 8) ORDER BY i
----
1
2
3

query IT
SELECT i, i IN (1, 2, 3, 4, 5, 6, 7, 8) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	true
NULL	NULL

query IT
SELECT i, i NOT IN (1, 2, 3, 4, 5, 6, 7, 8) FROM integers ORDER BY i NULLS LAST
----
1	false
2	false
3	false
NULL	NULL

query IT
SELECT i, i IN (1, 2, NULL, 4, 5, 6, 7, 8) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	NULL
NULL	NULL

query IT
SELECT i, i IN (i + 1) FROM integers ORDER BY i NULLS LAST
----
1	false
2	false
3	false
NULL	NULL

query IT
SELECT i, i IN (i + 1, 42, i) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	true
NULL	NULL

query IT
SELECT i, 1 IN (i - 1, i, i + 1) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	false
NULL	NULL

query IT
SELECT i, 1 NOT IN (i - 1, i, i + 1) FROM integers ORDER BY i NULLS LAST
----
1	false
2	false
3	true
NULL	NULL

query IT
SELECT i, i IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	true
NULL	NULL

query IT
SELECT i, i NOT IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i NULLS LAST
----
1	false
2	false
3	false
NULL	NULL

query IT
SELECT i, 1 IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i NULLS LAST
----
1	true
2	true
3	true
NULL	true

query IT
SELECT i, 1 NOT IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i NULLS LAST
----
1	false
2	false
3	false
NULL	false

query IT
SELECT i, i IN ((SELECT MAX(i) FROM integers), (SELECT MIN(i) FROM integers)) FROM integers ORDER BY i NULLS LAST
----
1	true
2	false
3	true
NULL	NULL

query III
SELECT i, (SELECT MAX(i) FROM integers WHERE i <> i1.i), (SELECT MIN(i) FROM integers WHERE i <= i1.i) FROM integers i1 ORDER BY i NULLS LAST
----
1	3	1
2	3	1
3	2	1
NULL	NULL	NULL

query IT
SELECT i, i IN ((SELECT MAX(i) FROM integers WHERE i <> i1.i), (SELECT MIN(i) FROM integers WHERE i <= i1.i)) FROM integers i1 ORDER BY i NULLS LAST
----
1	true
2	false
3	false
NULL	NULL