udf/postgreSQL/udf-select_implicit.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- SELECT_IMPLICIT
0006 -- Test cases for queries with ordering terms missing from the target list.
0007 -- This used to be called "junkfilter.sql".
0008 -- The parser uses the term "resjunk" to handle these cases.
0009 -- - thomas 1998-07-09
0010 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/select_implicit.sql
0011 --
0012 -- This test file was converted from postgreSQL/select_implicit.sql
0013
0014 -- load test data
0015 CREATE TABLE test_missing_target (a int, b int, c string, d string) using parquet;
0016 INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
0017 INSERT INTO test_missing_target VALUES (1, 2, 'ABAB', 'b');
0018 INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', 'c');
0019 INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
0020 INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
0021 INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
0022 INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
0023 INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
0024 INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
0025 INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
0026
0027
0028 --   w/ existing GROUP BY target
0029 SELECT udf(c), udf(count(*)) FROM test_missing_target GROUP BY
0030 udf(test_missing_target.c)
0031 ORDER BY udf(c);
0032
0033 --   w/o existing GROUP BY target using a relation name in GROUP BY clause
0034 SELECT udf(count(*)) FROM test_missing_target GROUP BY udf(test_missing_target.c)
0035 ORDER BY udf(c);
0036
0037 --   w/o existing GROUP BY target and w/o existing a different ORDER BY target
0038 --   failure expected
0039 SELECT udf(count(*)) FROM test_missing_target GROUP BY udf(a) ORDER BY udf(b);
0040
0041 --   w/o existing GROUP BY target and w/o existing same ORDER BY target
0042 SELECT udf(count(*)) FROM test_missing_target GROUP BY udf(b) ORDER BY udf(b);
0043
0044 --   w/ existing GROUP BY target using a relation name in target
0045 SELECT udf(test_missing_target.b), udf(count(*))
0046   FROM test_missing_target GROUP BY udf(b) ORDER BY udf(b);
0047
0048 --   w/o existing GROUP BY target
0049 SELECT udf(c) FROM test_missing_target ORDER BY udf(a);
0050
0051 --   w/o existing ORDER BY target
0052 SELECT udf(count(*)) FROM test_missing_target GROUP BY udf(b) ORDER BY udf(b) desc;
0053
0054 --   group using reference number
0055 SELECT udf(count(*)) FROM test_missing_target ORDER BY udf(1) desc;
0056
0057 --   order using reference number
0058 SELECT udf(c), udf(count(*)) FROM test_missing_target GROUP BY 1 ORDER BY 1;
0059
0060 --   group using reference number out of range
0061 --   failure expected
0062 SELECT udf(c), udf(count(*)) FROM test_missing_target GROUP BY 3;
0063
0064 --   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
0065 --   failure expected
0066 SELECT udf(count(*)) FROM test_missing_target x, test_missing_target y
0067         WHERE udf(x.a) = udf(y.a)
0068         GROUP BY udf(b) ORDER BY udf(b);
0069
0070 --   order w/ target under ambiguous condition
0071 --   failure NOT expected
0072 SELECT udf(a), udf(a) FROM test_missing_target
0073         ORDER BY udf(a);
0074
0075 --   order expression w/ target under ambiguous condition
0076 --   failure NOT expected
0077 SELECT udf(udf(a)/2), udf(udf(a)/2) FROM test_missing_target
0078         ORDER BY udf(udf(a)/2);
0079
0080 --   group expression w/ target under ambiguous condition
0081 --   failure NOT expected
0082 SELECT udf(a/2), udf(a/2) FROM test_missing_target
0083         GROUP BY udf(a/2) ORDER BY udf(a/2);
0084
0085 --   group w/ existing GROUP BY target under ambiguous condition
0086 SELECT udf(x.b), udf(count(*)) FROM test_missing_target x, test_missing_target y
0087         WHERE udf(x.a) = udf(y.a)
0088         GROUP BY udf(x.b) ORDER BY udf(x.b);
0089
0090 --   group w/o existing GROUP BY target under ambiguous condition
0091 SELECT udf(count(*)) FROM test_missing_target x, test_missing_target y
0092         WHERE udf(x.a) = udf(y.a)
0093         GROUP BY udf(x.b) ORDER BY udf(x.b);
0094
0095 -- [SPARK-28329] SELECT INTO syntax
0096 --   group w/o existing GROUP BY target under ambiguous condition
0097 --   into a table
0098 -- SELECT count(*) INTO TABLE test_missing_target2
0099 -- FROM test_missing_target x, test_missing_target y
0100 --      WHERE x.a = y.a
0101 --      GROUP BY x.b ORDER BY x.b;
0102 -- SELECT * FROM test_missing_target2;
0103
0104
0105 --  Functions and expressions
0106
0107 --   w/ existing GROUP BY target
0108 SELECT udf(a%2), udf(count(udf(b))) FROM test_missing_target
0109 GROUP BY udf(test_missing_target.a%2)
0110 ORDER BY udf(test_missing_target.a%2);
0111
0112 --   w/o existing GROUP BY target using a relation name in GROUP BY clause
0113 SELECT udf(count(c)) FROM test_missing_target
0114 GROUP BY udf(lower(test_missing_target.c))
0115 ORDER BY udf(lower(test_missing_target.c));
0116
0117 --   w/o existing GROUP BY target and w/o existing a different ORDER BY target
0118 --   failure expected
0119 SELECT udf(count(udf(a))) FROM test_missing_target GROUP BY udf(a) ORDER BY udf(b);
0120
0121 --   w/o existing GROUP BY target and w/o existing same ORDER BY target
0122 SELECT udf(count(b)) FROM test_missing_target GROUP BY udf(b/2) ORDER BY udf(b/2);
0123
0124 --   w/ existing GROUP BY target using a relation name in target
0125 SELECT udf(lower(test_missing_target.c)), udf(count(udf(c)))
0126   FROM test_missing_target GROUP BY udf(lower(c)) ORDER BY udf(lower(c));
0127
0128 --   w/o existing GROUP BY target
0129 SELECT udf(a) FROM test_missing_target ORDER BY udf(upper(udf(d)));
0130
0131 --   w/o existing ORDER BY target
0132 SELECT udf(count(b)) FROM test_missing_target
0133         GROUP BY udf((b + 1) / 2) ORDER BY udf((b + 1) / 2) desc;
0134
0135 --   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
0136 --   failure expected
0137 SELECT udf(count(udf(x.a))) FROM test_missing_target x, test_missing_target y
0138         WHERE udf(x.a) = udf(y.a)
0139         GROUP BY udf(b/2) ORDER BY udf(b/2);
0140
0141 --   group w/ existing GROUP BY target under ambiguous condition
0142 SELECT udf(x.b/2), udf(count(udf(x.b))) FROM test_missing_target x,
0143 test_missing_target y
0144         WHERE udf(x.a) = udf(y.a)
0145         GROUP BY udf(x.b/2) ORDER BY udf(x.b/2);
0146
0147 --   group w/o existing GROUP BY target under ambiguous condition
0148 --   failure expected due to ambiguous b in count(b)
0149 SELECT udf(count(udf(b))) FROM test_missing_target x, test_missing_target y
0150         WHERE udf(x.a) = udf(y.a)
0151         GROUP BY udf(x.b/2);
0152
0153 -- [SPARK-28329] SELECT INTO syntax
0154 --   group w/o existing GROUP BY target under ambiguous condition
0155 --   into a table
0156 -- SELECT count(x.b) INTO TABLE test_missing_target3
0157 -- FROM test_missing_target x, test_missing_target y
0158 --      WHERE x.a = y.a
0159 --      GROUP BY x.b/2 ORDER BY x.b/2;
0160 -- SELECT * FROM test_missing_target3;
0161
0162 --   Cleanup
0163 DROP TABLE test_missing_target;
0164 -- DROP TABLE test_missing_target2;
0165 -- DROP TABLE test_missing_target3;