sql-tests/inputs/intersect-all.sql

0001 CREATE TEMPORARY VIEW tab1 AS SELECT * FROM VALUES
0002     (1, 2),
0003     (1, 2),
0004     (1, 3),
0005     (1, 3),
0006     (2, 3),
0007     (null, null),
0008     (null, null)
0009     AS tab1(k, v);
0010 CREATE TEMPORARY VIEW tab2 AS SELECT * FROM VALUES
0011     (1, 2),
0012     (1, 2),
0013     (2, 3),
0014     (3, 4),
0015     (null, null),
0016     (null, null)
0017     AS tab2(k, v);
0018
0019 -- Basic INTERSECT ALL
0020 SELECT * FROM tab1
0021 INTERSECT ALL
0022 SELECT * FROM tab2;
0023
0024 -- INTERSECT ALL same table in both branches
0025 SELECT * FROM tab1
0026 INTERSECT ALL
0027 SELECT * FROM tab1 WHERE k = 1;
0028
0029 -- Empty left relation
0030 SELECT * FROM tab1 WHERE k > 2
0031 INTERSECT ALL
0032 SELECT * FROM tab2;
0033
0034 -- Empty right relation
0035 SELECT * FROM tab1
0036 INTERSECT ALL
0037 SELECT * FROM tab2 WHERE k > 3;
0038
0039 -- Type Coerced INTERSECT ALL
0040 SELECT * FROM tab1
0041 INTERSECT ALL
0042 SELECT CAST(1 AS BIGINT), CAST(2 AS BIGINT);
0043
0044 -- Error as types of two side are not compatible
0045 SELECT * FROM tab1
0046 INTERSECT ALL
0047 SELECT array(1), 2;
0048
0049 -- Mismatch on number of columns across both branches
0050 SELECT k FROM tab1
0051 INTERSECT ALL
0052 SELECT k, v FROM tab2;
0053
0054 -- Basic
0055 SELECT * FROM tab2
0056 INTERSECT ALL
0057 SELECT * FROM tab1
0058 INTERSECT ALL
0059 SELECT * FROM tab2;
0060
0061 -- Chain of different `set operations
0062 SELECT * FROM tab1
0063 EXCEPT
0064 SELECT * FROM tab2
0065 UNION ALL
0066 SELECT * FROM tab1
0067 INTERSECT ALL
0068 SELECT * FROM tab2
0069 ;
0070
0071 -- Chain of different `set operations
0072 SELECT * FROM tab1
0073 EXCEPT
0074 SELECT * FROM tab2
0075 EXCEPT
0076 SELECT * FROM tab1
0077 INTERSECT ALL
0078 SELECT * FROM tab2
0079 ;
0080
0081 -- test use parenthesis to control order of evaluation
0082 (
0083   (
0084     (
0085       SELECT * FROM tab1
0086       EXCEPT
0087       SELECT * FROM tab2
0088     )
0089     EXCEPT
0090     SELECT * FROM tab1
0091   )
0092   INTERSECT ALL
0093   SELECT * FROM tab2
0094 )
0095 ;
0096
0097 -- Join under intersect all
0098 SELECT *
0099 FROM   (SELECT tab1.k,
0100                tab2.v
0101         FROM   tab1
0102                JOIN tab2
0103                  ON tab1.k = tab2.k)
0104 INTERSECT ALL
0105 SELECT *
0106 FROM   (SELECT tab1.k,
0107                tab2.v
0108         FROM   tab1
0109                JOIN tab2
0110                  ON tab1.k = tab2.k);
0111
0112 -- Join under intersect all (2)
0113 SELECT *
0114 FROM   (SELECT tab1.k,
0115                tab2.v
0116         FROM   tab1
0117                JOIN tab2
0118                  ON tab1.k = tab2.k)
0119 INTERSECT ALL
0120 SELECT *
0121 FROM   (SELECT tab2.v AS k,
0122                tab1.k AS v
0123         FROM   tab1
0124                JOIN tab2
0125                  ON tab1.k = tab2.k);
0126
0127 -- Group by under intersect all
0128 SELECT v FROM tab1 GROUP BY v
0129 INTERSECT ALL
0130 SELECT k FROM tab2 GROUP BY k;
0131
0132 -- Test pre spark2.4 behaviour of set operation precedence
0133 -- All the set operators are given equal precedence and are evaluated
0134 -- from left to right as they appear in the query.
0135
0136 -- Set the property
0137 SET spark.sql.legacy.setopsPrecedence.enabled= true;
0138
0139 SELECT * FROM tab1
0140 EXCEPT
0141 SELECT * FROM tab2
0142 UNION ALL
0143 SELECT * FROM tab1
0144 INTERSECT ALL
0145 SELECT * FROM tab2;
0146
0147 SELECT * FROM tab1
0148 EXCEPT
0149 SELECT * FROM tab2
0150 UNION ALL
0151 SELECT * FROM tab1
0152 INTERSECT
0153 SELECT * FROM tab2;
0154
0155 -- Restore the property
0156 SET spark.sql.legacy.setopsPrecedence.enabled = false;
0157
0158 -- Clean-up
0159 DROP VIEW IF EXISTS tab1;
0160 DROP VIEW IF EXISTS tab2;