Github user mgyucht commented on a diff in the pull request:
https://github.com/apache/spark/pull/21425#discussion_r190934130
--- Diff:
sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/not-in-unit-tests-multi-column-literal.sql.out
---
@@ -0,0 +1,97 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 7
+
+
+-- !query 0
+CREATE TEMPORARY VIEW m AS SELECT * FROM VALUES
+ (null, null),
+ (null, 1.0),
+ (2, 3.0),
+ (4, 5.0)
+ AS m(a, b)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+-- Case 1 (not possible to write a literal with no rows, so we ignore it.)
+ -- (subquery is empty -> row is returned)
+
+ -- Case 2
+ -- (subquery contains a row with null in all columns -> row not returned)
+SELECT *
+FROM m
+WHERE (a, b) NOT IN ((CAST (null AS INT), CAST (null AS DECIMAL(2, 1))))
+-- !query 1 schema
+struct<a:int,b:decimal(2,1)>
+-- !query 1 output
+2 3
+4 5
+NULL 1
+
+
+-- !query 2
+-- 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 ((0, 1.0), (2, 3.0), (4, CAST(null AS DECIMAL(2,
1))))
+-- !query 2 schema
+struct<a:int,b:decimal(2,1)>
+-- !query 2 output
+NULL NULL
+
+
+-- !query 3
+-- 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 ((0, 1.0), (2, 3.0), (4, CAST(null AS DECIMAL(2,
1))))
+-- !query 3 schema
+struct<a:int,b:decimal(2,1)>
+-- !query 3 output
+NULL 1
--- End diff --
Same here.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
