subquery/in-subquery/not-in-unit-tests-multi-column.sql

0001 -- Unit tests for simple NOT IN predicate subquery across multiple columns.
0002 --
0003 -- See not-in-single-column-unit-tests.sql for an introduction.
0004 --
0005 -- Test cases for multi-column ``WHERE a NOT IN (SELECT c FROM r ...)'':
0006 -- | # | does subquery include null?     | do filter columns contain null? | a = c? | b = d? | row included in result? |
0007 -- | 1 | empty                           | *                               | *      | *      | yes                     |
0008 -- | 2 | 1+ row has null for all columns | *                               | *      | *      | no                      |
0009 -- | 3 | no row has null for all columns | (yes, yes)                      | *      | *      | no                      |
0010 -- | 4 | no row has null for all columns | (no, yes)                       | yes    | *      | no                      |
0011 -- | 5 | no row has null for all columns | (no, yes)                       | no     | *      | yes                     |
0012 -- | 6 | no                              | (no, no)                        | yes    | yes    | no                      |
0013 -- | 7 | no                              | (no, no)                        | _      | _      | yes                     |
0014 --
0015 -- This can be generalized to include more tests for more columns, but it covers the main cases
0016 -- when there is more than one column.
0017
0018 CREATE TEMPORARY VIEW m AS SELECT * FROM VALUES
0019   (null, null),
0020   (null, 1.0),
0021   (2, 3.0),
0022   (4, 5.0)
0023   AS m(a, b);
0024
0025 CREATE TEMPORARY VIEW s AS SELECT * FROM VALUES
0026   (null, null),
0027   (0, 1.0),
0028   (2, 3.0),
0029   (4, null)
0030   AS s(c, d);
0031
0032   -- Case 1
0033   -- (subquery is empty -> row is returned)
0034 SELECT *
0035 FROM   m
0036 WHERE  (a, b) NOT IN (SELECT *
0037                       FROM   s
0038                       WHERE  d > 5.0) -- Matches no rows
0039 ;
0040
0041   -- Case 2
0042   -- (subquery contains a row with null in all columns -> row not returned)
0043 SELECT *
0044 FROM   m
0045 WHERE  (a, b) NOT IN (SELECT *
0046                       FROM s
0047                       WHERE c IS NULL AND d IS NULL) -- Matches only (null, null)
0048 ;
0049
0050   -- Case 3
0051   -- (probe-side columns are all null -> row not returned)
0052 SELECT *
0053 FROM   m
0054 WHERE  a IS NULL AND b IS NULL -- Matches only (null, null)
0055        AND (a, b) NOT IN (SELECT *
0056                           FROM s
0057                           WHERE c IS NOT NULL) -- Matches (0, 1.0), (2, 3.0), (4, null)
0058 ;
0059
0060   -- Case 4
0061   -- (one column null, other column matches a row in the subquery result -> row not returned)
0062 SELECT *
0063 FROM   m
0064 WHERE  b = 1.0 -- Matches (null, 1.0)
0065        AND (a, b) NOT IN (SELECT *
0066                           FROM s
0067                           WHERE c IS NOT NULL) -- Matches (0, 1.0), (2, 3.0), (4, null)
0068 ;
0069
0070   -- Case 5
0071   -- (one null column with no match -> row is returned)
0072 SELECT *
0073 FROM   m
0074 WHERE  b = 1.0 -- Matches (null, 1.0)
0075        AND (a, b) NOT IN (SELECT *
0076                           FROM s
0077                           WHERE c = 2) -- Matches (2, 3.0)
0078 ;
0079
0080   -- Case 6
0081   -- (no null columns with match -> row not returned)
0082 SELECT *
0083 FROM   m
0084 WHERE  b = 3.0 -- Matches (2, 3.0)
0085        AND (a, b) NOT IN (SELECT *
0086                           FROM s
0087                           WHERE c = 2) -- Matches (2, 3.0)
0088 ;
0089
0090   -- Case 7
0091   -- (no null columns with no match -> row is returned)
0092 SELECT *
0093 FROM   m
0094 WHERE  b = 5.0 -- Matches (4, 5.0)
0095        AND (a, b) NOT IN (SELECT *
0096                           FROM s
0097                           WHERE c = 2) -- Matches (2, 3.0)
0098 ;