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.
10
CREATE TABLE test_missing_target (a int, b int, c char(8), d char);
11
INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
12
INSERT INTO test_missing_target VALUES (1, 2, 'AAAA', 'b');
13
INSERT INTO test_missing_target VALUES (2, 2, 'AAAA', 'c');
14
INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
15
INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
16
INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
17
INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
18
INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
19
INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
20
INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
23
-- w/ existing GROUP BY target
24
SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
26
-- w/o existing GROUP BY target using a relation name in GROUP BY clause
27
SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
29
-- w/o existing GROUP BY target and w/o existing a different ORDER BY target
31
SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
33
-- w/o existing GROUP BY target and w/o existing same ORDER BY target
34
SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b;
36
-- w/ existing GROUP BY target using a relation name in target
37
SELECT test_missing_target.b, count(*)
38
FROM test_missing_target GROUP BY b ORDER BY b;
40
-- w/o existing GROUP BY target
41
SELECT c FROM test_missing_target ORDER BY a;
43
-- w/o existing ORDER BY target
44
SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc;
46
-- group using reference number
47
SELECT count(*) FROM test_missing_target ORDER BY 1 desc;
49
-- order using reference number
50
SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1;
52
-- group using reference number out of range
54
SELECT c, count(*) FROM test_missing_target GROUP BY 3;
56
-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
58
SELECT count(*) FROM test_missing_target x, test_missing_target y
60
GROUP BY b ORDER BY b;
62
-- order w/ target under ambiguous condition
63
-- failure NOT expected
64
SELECT a, a FROM test_missing_target
67
-- order expression w/ target under ambiguous condition
68
-- failure NOT expected
69
SELECT a/2, a/2 FROM test_missing_target
72
-- group expression w/ target under ambiguous condition
73
-- failure NOT expected
74
SELECT a/2, a/2 FROM test_missing_target
75
GROUP BY a/2 ORDER BY a/2;
77
-- group w/ existing GROUP BY target under ambiguous condition
78
SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
80
GROUP BY x.b ORDER BY x.b;
82
-- group w/o existing GROUP BY target under ambiguous condition
83
SELECT count(*) FROM test_missing_target x, test_missing_target y
85
GROUP BY x.b ORDER BY x.b;
87
-- group w/o existing GROUP BY target under ambiguous condition
89
SELECT count(*) INTO TABLE test_missing_target2
90
FROM test_missing_target x, test_missing_target y
92
GROUP BY x.b ORDER BY x.b;
93
SELECT * FROM test_missing_target2;
96
-- Functions and expressions
98
-- w/ existing GROUP BY target
99
SELECT a%2, count(b) FROM test_missing_target
100
GROUP BY test_missing_target.a%2
101
ORDER BY test_missing_target.a%2;
103
-- w/o existing GROUP BY target using a relation name in GROUP BY clause
104
SELECT count(c) FROM test_missing_target
105
GROUP BY lower(test_missing_target.c)
106
ORDER BY lower(test_missing_target.c);
108
-- w/o existing GROUP BY target and w/o existing a different ORDER BY target
110
SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
112
-- w/o existing GROUP BY target and w/o existing same ORDER BY target
113
SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
115
-- w/ existing GROUP BY target using a relation name in target
116
SELECT lower(test_missing_target.c), count(c)
117
FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
119
-- w/o existing GROUP BY target
120
SELECT a FROM test_missing_target ORDER BY upper(d);
122
-- w/o existing ORDER BY target
123
SELECT count(b) FROM test_missing_target
124
GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
126
-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
128
SELECT count(x.a) FROM test_missing_target x, test_missing_target y
130
GROUP BY b/2 ORDER BY b/2;
132
-- group w/ existing GROUP BY target under ambiguous condition
133
SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y
135
GROUP BY x.b/2 ORDER BY x.b/2;
137
-- group w/o existing GROUP BY target under ambiguous condition
138
-- failure expected due to ambiguous b in count(b)
139
SELECT count(b) FROM test_missing_target x, test_missing_target y
143
-- group w/o existing GROUP BY target under ambiguous condition
145
SELECT count(x.b) INTO TABLE test_missing_target3
146
FROM test_missing_target x, test_missing_target y
148
GROUP BY x.b/2 ORDER BY x.b/2;
149
SELECT * FROM test_missing_target3;
152
DROP TABLE test_missing_target;
153
DROP TABLE test_missing_target2;
154
DROP TABLE test_missing_target3;