3 -- Test cases for queries with ordering terms missing from the target list.
4 -- This used to be called "junkfilter.sql".
5 -- The parser uses the term "resjunk" to handle these cases.
9 CREATE TABLE test_missing_target (a int, b int, c char(8), d char);
10 INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
11 INSERT INTO test_missing_target VALUES (1, 2, 'AAAA', 'b');
12 INSERT INTO test_missing_target VALUES (2, 2, 'AAAA', 'c');
13 INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
14 INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
15 INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
16 INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
17 INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
18 INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
19 INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
20 -- w/ existing GROUP BY target
21 SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
32 -- w/o existing GROUP BY target using a relation name in GROUP BY clause
33 SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
44 -- w/o existing GROUP BY target and w/o existing a different ORDER BY target
46 SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
47 ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function
48 LINE 1: ...ECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
50 -- w/o existing GROUP BY target and w/o existing same ORDER BY target
51 SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b;
60 -- w/ existing GROUP BY target using a relation name in target
61 SELECT test_missing_target.b, count(*)
62 FROM test_missing_target GROUP BY b ORDER BY b;
71 -- w/o existing GROUP BY target
72 SELECT c FROM test_missing_target ORDER BY a;
87 -- w/o existing ORDER BY target
88 SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc;
97 -- group using reference number
98 SELECT count(*) FROM test_missing_target ORDER BY 1 desc;
104 -- order using reference number
105 SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1;
116 -- group using reference number out of range
118 SELECT c, count(*) FROM test_missing_target GROUP BY 3;
119 ERROR: GROUP BY position 3 is not in select list
120 LINE 1: SELECT c, count(*) FROM test_missing_target GROUP BY 3;
122 -- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
124 SELECT count(*) FROM test_missing_target x, test_missing_target y
126 GROUP BY b ORDER BY b;
127 ERROR: column reference "b" is ambiguous
128 LINE 3: GROUP BY b ORDER BY b;
130 -- order w/ target under ambiguous condition
131 -- failure NOT expected
132 SELECT a, a FROM test_missing_target
148 -- order expression w/ target under ambiguous condition
149 -- failure NOT expected
150 SELECT a/2, a/2 FROM test_missing_target
153 ----------+----------
166 -- group expression w/ target under ambiguous condition
167 -- failure NOT expected
168 SELECT a/2, a/2 FROM test_missing_target
169 GROUP BY a/2 ORDER BY a/2;
171 ----------+----------
179 -- group w/ existing GROUP BY target under ambiguous condition
180 SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
182 GROUP BY x.b ORDER BY x.b;
191 -- group w/o existing GROUP BY target under ambiguous condition
192 SELECT count(*) FROM test_missing_target x, test_missing_target y
194 GROUP BY x.b ORDER BY x.b;
203 -- group w/o existing GROUP BY target under ambiguous condition
205 SELECT count(*) INTO TABLE test_missing_target2
206 FROM test_missing_target x, test_missing_target y
208 GROUP BY x.b ORDER BY x.b;
209 SELECT * FROM test_missing_target2;
218 -- Functions and expressions
219 -- w/ existing GROUP BY target
220 SELECT a%2, count(b) FROM test_missing_target
221 GROUP BY test_missing_target.a%2
222 ORDER BY test_missing_target.a%2;
229 -- w/o existing GROUP BY target using a relation name in GROUP BY clause
230 SELECT count(c) FROM test_missing_target
231 GROUP BY lower(test_missing_target.c)
232 ORDER BY lower(test_missing_target.c);
241 -- w/o existing GROUP BY target and w/o existing a different ORDER BY target
243 SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
244 ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function
245 LINE 1: ...ECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
247 -- w/o existing GROUP BY target and w/o existing same ORDER BY target
248 SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
256 -- w/ existing GROUP BY target using a relation name in target
257 SELECT lower(test_missing_target.c), count(c)
258 FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
267 -- w/o existing GROUP BY target
268 SELECT a FROM test_missing_target ORDER BY upper(d);
283 -- w/o existing ORDER BY target
284 SELECT count(b) FROM test_missing_target
285 GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
292 -- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
294 SELECT count(x.a) FROM test_missing_target x, test_missing_target y
296 GROUP BY b/2 ORDER BY b/2;
297 ERROR: column reference "b" is ambiguous
298 LINE 3: GROUP BY b/2 ORDER BY b/2;
300 -- group w/ existing GROUP BY target under ambiguous condition
301 SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y
303 GROUP BY x.b/2 ORDER BY x.b/2;
311 -- group w/o existing GROUP BY target under ambiguous condition
312 -- failure expected due to ambiguous b in count(b)
313 SELECT count(b) FROM test_missing_target x, test_missing_target y
316 ERROR: column reference "b" is ambiguous
317 LINE 1: SELECT count(b) FROM test_missing_target x, test_missing_tar...
319 -- group w/o existing GROUP BY target under ambiguous condition
321 SELECT count(x.b) INTO TABLE test_missing_target3
322 FROM test_missing_target x, test_missing_target y
324 GROUP BY x.b/2 ORDER BY x.b/2;
325 SELECT * FROM test_missing_target3;
334 DROP TABLE test_missing_target;
335 DROP TABLE test_missing_target2;
336 DROP TABLE test_missing_target3;