text: -- Unit tests for simple NOT IN predicate subquery across multiple columns.
  --
  -- See not-in-single-column-unit-tests.sql for an introduction.
  --
  -- Test cases for multi-column ``WHERE a NOT IN (SELECT c FROM r ...)'':
  -- | # | does subquery include null?     | do filter columns contain null? | a = c? | b = d? | row included in result? |
  -- | 1 | empty                           | *                               | *      | *      | yes                     |
  -- | 2 | 1+ row has null for all columns | *                               | *      | *      | no                      |
  -- | 3 | no row has null for all columns | (yes, yes)                      | *      | *      | no                      |
  -- | 4 | no row has null for all columns | (no, yes)                       | yes    | *      | no                      |
  -- | 5 | no row has null for all columns | (no, yes)                       | no     | *      | yes                     |
  -- | 6 | no                              | (no, no)                        | yes    | yes    | no                      |
  -- | 7 | no                              | (no, no)                        | _      | _      | yes                     |
  --
  -- This can be generalized to include more tests for more columns, but it covers the main cases
  -- when there is more than one column.
  
  --CONFIG_DIM1 spark.sql.optimizeNullAwareAntiJoin=true
  --CONFIG_DIM1 spark.sql.optimizeNullAwareAntiJoin=false
  
  CREATE TEMPORARY VIEW m AS SELECT * FROM VALUES
    (null, null),
    (null, 1.0),
    (2, 3.0),
    (4, 5.0)
    AS m(a, b);
  
  CREATE TEMPORARY VIEW s AS SELECT * FROM VALUES
    (null, null),
    (0, 1.0),
    (2, 3.0),
    (4, null)
    AS s(c, d);
  
    -- Case 1
    -- (subquery is empty -> row is returned)
  SELECT *
  FROM   m
  WHERE  (a, b) NOT IN (SELECT *
                        FROM   s
                        WHERE  d > 5.0) -- Matches no rows
  ;
  
    -- Case 2
    -- (subquery contains a row with null in all columns -> row not returned)
  SELECT *
  FROM   m
  WHERE  (a, b) NOT IN (SELECT *
                        FROM s
                        WHERE c IS NULL AND d IS NULL) -- Matches only (null, null)
  ;
  
    -- Case 3
    -- (probe-side columns are all null -> row not returned)
  SELECT *
  FROM   m
  WHERE  a IS NULL AND b IS NULL -- Matches only (null, null)
         AND (a, b) NOT IN (SELECT *
                            FROM s
                            WHERE c IS NOT NULL) -- Matches (0, 1.0), (2, 3.0), (4, null)
  ;
  
    -- Case 4
    -- (one column null, other column matches a row in the subquery result -> row not returned)
  SELECT *
  FROM   m
  WHERE  b = 1.0 -- Matches (null, 1.0)
         AND (a, b) NOT IN (SELECT *
                            FROM s
                            WHERE c IS NOT NULL) -- Matches (0, 1.0), (2, 3.0), (4, null)
  ;
  
    -- Case 5
    -- (one null column with no match -> row is returned)
  SELECT *
  FROM   m
  WHERE  b = 1.0 -- Matches (null, 1.0)
         AND (a, b) NOT IN (SELECT *
                            FROM s
                            WHERE c = 2) -- Matches (2, 3.0)
  ;
  
    -- Case 6
    -- (no null columns with match -> row not returned)
  SELECT *
  FROM   m
  WHERE  b = 3.0 -- Matches (2, 3.0)
         AND (a, b) NOT IN (SELECT *
                            FROM s
                            WHERE c = 2) -- Matches (2, 3.0)
  ;
  
    -- Case 7
    -- (no null columns with no match -> row is returned)
  SELECT *
  FROM   m
  WHERE  b = 5.0 -- Matches (4, 5.0)
         AND (a, b) NOT IN (SELECT *
                            FROM s
                            WHERE c = 2) -- Matches (2, 3.0)
  ;
  