inputs/postgreSQL/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
0013 -- load test data
0014 CREATE TABLE test_missing_target (a int, b int, c string, d string) using parquet;
0015 INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
0016 INSERT INTO test_missing_target VALUES (1, 2, 'ABAB', 'b');
0017 INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', 'c');
0018 INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
0019 INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
0020 INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
0021 INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
0022 INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
0023 INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
0024 INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
0025
0026
0027 --   w/ existing GROUP BY target
0028 SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
0029
0030 --   w/o existing GROUP BY target using a relation name in GROUP BY clause
0031 SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
0032
0033 --   w/o existing GROUP BY target and w/o existing a different ORDER BY target
0034 --   failure expected
0035 SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
0036
0037 --   w/o existing GROUP BY target and w/o existing same ORDER BY target
0038 SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b;
0039
0040 --   w/ existing GROUP BY target using a relation name in target
0041 SELECT test_missing_target.b, count(*)
0042   FROM test_missing_target GROUP BY b ORDER BY b;
0043
0044 --   w/o existing GROUP BY target
0045 SELECT c FROM test_missing_target ORDER BY a;
0046
0047 --   w/o existing ORDER BY target
0048 SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc;
0049
0050 --   group using reference number
0051 SELECT count(*) FROM test_missing_target ORDER BY 1 desc;
0052
0053 --   order using reference number
0054 SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1;
0055
0056 --   group using reference number out of range
0057 --   failure expected
0058 SELECT c, count(*) FROM test_missing_target GROUP BY 3;
0059
0060 --   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
0061 --   failure expected
0062 SELECT count(*) FROM test_missing_target x, test_missing_target y
0063         WHERE x.a = y.a
0064         GROUP BY b ORDER BY b;
0065
0066 --   order w/ target under ambiguous condition
0067 --   failure NOT expected
0068 SELECT a, a FROM test_missing_target
0069         ORDER BY a;
0070
0071 --   order expression w/ target under ambiguous condition
0072 --   failure NOT expected
0073 SELECT a/2, a/2 FROM test_missing_target
0074         ORDER BY a/2;
0075
0076 --   group expression w/ target under ambiguous condition
0077 --   failure NOT expected
0078 SELECT a/2, a/2 FROM test_missing_target
0079         GROUP BY a/2 ORDER BY a/2;
0080
0081 --   group w/ existing GROUP BY target under ambiguous condition
0082 SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
0083         WHERE x.a = y.a
0084         GROUP BY x.b ORDER BY x.b;
0085
0086 --   group w/o existing GROUP BY target under ambiguous condition
0087 SELECT count(*) FROM test_missing_target x, test_missing_target y
0088         WHERE x.a = y.a
0089         GROUP BY x.b ORDER BY x.b;
0090
0091 -- [SPARK-28329] SELECT INTO syntax
0092 --   group w/o existing GROUP BY target under ambiguous condition
0093 --   into a table
0094 -- SELECT count(*) INTO TABLE test_missing_target2
0095 -- FROM test_missing_target x, test_missing_target y
0096 --      WHERE x.a = y.a
0097 --      GROUP BY x.b ORDER BY x.b;
0098 -- SELECT * FROM test_missing_target2;
0099
0100
0101 --  Functions and expressions
0102
0103 --   w/ existing GROUP BY target
0104 SELECT a%2, count(b) FROM test_missing_target
0105 GROUP BY test_missing_target.a%2
0106 ORDER BY test_missing_target.a%2;
0107
0108 --   w/o existing GROUP BY target using a relation name in GROUP BY clause
0109 SELECT count(c) FROM test_missing_target
0110 GROUP BY lower(test_missing_target.c)
0111 ORDER BY lower(test_missing_target.c);
0112
0113 --   w/o existing GROUP BY target and w/o existing a different ORDER BY target
0114 --   failure expected
0115 SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
0116
0117 --   w/o existing GROUP BY target and w/o existing same ORDER BY target
0118 SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
0119
0120 --   w/ existing GROUP BY target using a relation name in target
0121 SELECT lower(test_missing_target.c), count(c)
0122   FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
0123
0124 --   w/o existing GROUP BY target
0125 SELECT a FROM test_missing_target ORDER BY upper(d);
0126
0127 --   w/o existing ORDER BY target
0128 SELECT count(b) FROM test_missing_target
0129         GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
0130
0131 --   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
0132 --   failure expected
0133 SELECT count(x.a) FROM test_missing_target x, test_missing_target y
0134         WHERE x.a = y.a
0135         GROUP BY b/2 ORDER BY b/2;
0136
0137 --   group w/ existing GROUP BY target under ambiguous condition
0138 SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y
0139         WHERE x.a = y.a
0140         GROUP BY x.b/2 ORDER BY x.b/2;
0141
0142 --   group w/o existing GROUP BY target under ambiguous condition
0143 --   failure expected due to ambiguous b in count(b)
0144 SELECT count(b) FROM test_missing_target x, test_missing_target y
0145         WHERE x.a = y.a
0146         GROUP BY x.b/2;
0147
0148 -- [SPARK-28329] SELECT INTO syntax
0149 --   group w/o existing GROUP BY target under ambiguous condition
0150 --   into a table
0151 -- SELECT count(x.b) INTO TABLE test_missing_target3
0152 -- FROM test_missing_target x, test_missing_target y
0153 --      WHERE x.a = y.a
0154 --      GROUP BY x.b/2 ORDER BY x.b/2;
0155 -- SELECT * FROM test_missing_target3;
0156
0157 --   Cleanup
0158 DROP TABLE test_missing_target;
0159 -- DROP TABLE test_missing_target2;
0160 -- DROP TABLE test_missing_target3;