inputs/udf/udf-intersect-all.sql

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