inputs/udf/udf-except-all.sql

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