subquery/in-subquery/not-in-unit-tests-multi-column-literal.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 -- This file has the same test cases as not-in-unit-tests-multi-column.sql with literals instead of
0005 -- subqueries. Small changes have been made to the literals to make them typecheck.
0006
0007 CREATE TEMPORARY VIEW m AS SELECT * FROM VALUES
0008   (null, null),
0009   (null, 1.0),
0010   (2, 3.0),
0011   (4, 5.0)
0012   AS m(a, b);
0013
0014 -- Case 1 (not possible to write a literal with no rows, so we ignore it.)
0015 -- (subquery is empty -> row is returned)
0016
0017 -- Cases 2, 3 and 4 are currently broken, so I have commented them out here.
0018 -- Filed https://issues.apache.org/jira/browse/SPARK-24395 to fix and restore these test cases.
0019
0020   -- Case 5
0021   -- (one null column with no match -> row is returned)
0022 SELECT *
0023 FROM   m
0024 WHERE  b = 1.0 -- Matches (null, 1.0)
0025        AND (a, b) NOT IN ((2, 3.0));
0026
0027   -- Case 6
0028   -- (no null columns with match -> row not returned)
0029 SELECT *
0030 FROM   m
0031 WHERE  b = 3.0 -- Matches (2, 3.0)
0032        AND (a, b) NOT IN ((2, 3.0));
0033
0034   -- Case 7
0035   -- (no null columns with no match -> row is returned)
0036 SELECT *
0037 FROM   m
0038 WHERE  b = 5.0 -- Matches (4, 5.0)
0039        AND (a, b) NOT IN ((2, 3.0));