subquery/in-subquery/not-in-unit-tests-single-column-literal.sql

0001 -- Unit tests for simple NOT IN with a literal expression of a single column
0002 --
0003 -- More information can be found in not-in-unit-tests-single-column.sql.
0004 -- This file has the same test cases as not-in-unit-tests-single-column.sql with literals instead of
0005 -- subqueries.
0006
0007 CREATE TEMPORARY VIEW m AS SELECT * FROM VALUES
0008   (null, 1.0),
0009   (2, 3.0),
0010   (4, 5.0)
0011   AS m(a, b);
0012
0013   -- Uncorrelated NOT IN Subquery test cases
0014   -- Case 1 (not possible to write a literal with no rows, so we ignore it.)
0015   -- (empty subquery -> all rows returned)
0016
0017   -- Case 2
0018   -- (subquery includes null -> no rows returned)
0019 SELECT *
0020 FROM   m
0021 WHERE  a NOT IN (null);
0022
0023   -- Case 3
0024   -- (probe column is null -> row not returned)
0025 SELECT *
0026 FROM   m
0027 WHERE  b = 1.0 -- Only matches (null, 1.0)
0028        AND a NOT IN (2);
0029
0030   -- Case 4
0031   -- (probe column matches subquery row -> row not returned)
0032 SELECT *
0033 FROM   m
0034 WHERE  b = 3.0 -- Only matches (2, 3.0)
0035        AND a NOT IN (2);
0036
0037   -- Case 5
0038   -- (probe column does not match subquery row -> row is returned)
0039 SELECT *
0040 FROM   m
0041 WHERE  b = 3.0 -- Only matches (2, 3.0)
0042        AND a NOT IN (6);