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, 'ABAB', 'b');
12
INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', '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;