inputs/udf/udf-except.sql

0001 -- This test file was converted from except.sql.
0002 -- Tests different scenarios of except operation
0003 create temporary view t1 as select * from values
0004   ("one", 1),
0005   ("two", 2),
0006   ("three", 3),
0007   ("one", NULL)
0008   as t1(k, v);
0009
0010 create temporary view t2 as select * from values
0011   ("one", 1),
0012   ("two", 22),
0013   ("one", 5),
0014   ("one", NULL),
0015   (NULL, 5)
0016   as t2(k, v);
0017
0018
0019 -- Except operation that will be replaced by left anti join
0020 SELECT udf(k), udf(v) FROM t1 EXCEPT SELECT udf(k), udf(v) FROM t2;
0021
0022
0023 -- Except operation that will be replaced by Filter: SPARK-22181
0024 SELECT * FROM t1 EXCEPT SELECT * FROM t1 where udf(v) <> 1 and v <> udf(2);
0025
0026
0027 -- Except operation that will be replaced by Filter: SPARK-22181
0028 SELECT * FROM t1 where udf(v) <> 1 and v <> udf(22) EXCEPT SELECT * FROM t1 where udf(v) <> 2 and v >= udf(3);
0029
0030
0031 -- Except operation that will be replaced by Filter: SPARK-22181
0032 SELECT t1.* FROM t1, t2 where t1.k = t2.k
0033 EXCEPT
0034 SELECT t1.* FROM t1, t2 where t1.k = t2.k and t1.k != udf('one');
0035
0036
0037 -- Except operation that will be replaced by left anti join
0038 SELECT * FROM t2 where v >= udf(1) and udf(v) <> 22 EXCEPT SELECT * FROM t1;
0039
0040
0041 -- Except operation that will be replaced by left anti join
0042 SELECT (SELECT min(udf(k)) FROM t2 WHERE t2.k = t1.k) min_t2 FROM t1
0043 MINUS
0044 SELECT (SELECT udf(min(k)) FROM t2) abs_min_t2 FROM t1 WHERE  t1.k = udf('one');
0045
0046
0047 -- Except operation that will be replaced by left anti join
0048 SELECT t1.k
0049 FROM   t1
0050 WHERE  t1.v <= (SELECT   udf(max(udf(t2.v)))
0051                 FROM     t2
0052                 WHERE    udf(t2.k) = udf(t1.k))
0053 MINUS
0054 SELECT t1.k
0055 FROM   t1
0056 WHERE  udf(t1.v) >= (SELECT   min(udf(t2.v))
0057                 FROM     t2
0058                 WHERE    t2.k = t1.k);