1--
2-- SELECT_IMPLICIT
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.
6-- - thomas 1998-07-09
7--
8
9-- load test data
10CREATE TABLE test_missing_target (a int, b int, c char(8), d char);
11INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
12INSERT INTO test_missing_target VALUES (1, 2, 'ABAB', 'b');
13INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', 'c');
14INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
15INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
16INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
17INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
18INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
19INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
20INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
21
22
23--   w/ existing GROUP BY target
24SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
25
26--   w/o existing GROUP BY target using a relation name in GROUP BY clause
27SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
28
29--   w/o existing GROUP BY target and w/o existing a different ORDER BY target
30--   failure expected
31SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
32
33--   w/o existing GROUP BY target and w/o existing same ORDER BY target
34SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b;
35
36--   w/ existing GROUP BY target using a relation name in target
37SELECT test_missing_target.b, count(*)
38  FROM test_missing_target GROUP BY b ORDER BY b;
39
40--   w/o existing GROUP BY target
41SELECT c FROM test_missing_target ORDER BY a;
42
43--   w/o existing ORDER BY target
44SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc;
45
46--   group using reference number
47SELECT count(*) FROM test_missing_target ORDER BY 1 desc;
48
49--   order using reference number
50SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1;
51
52--   group using reference number out of range
53--   failure expected
54SELECT c, count(*) FROM test_missing_target GROUP BY 3;
55
56--   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
57--   failure expected
58SELECT count(*) FROM test_missing_target x, test_missing_target y
59	WHERE x.a = y.a
60	GROUP BY b ORDER BY b;
61
62--   order w/ target under ambiguous condition
63--   failure NOT expected
64SELECT a, a FROM test_missing_target
65	ORDER BY a;
66
67--   order expression w/ target under ambiguous condition
68--   failure NOT expected
69SELECT a/2, a/2 FROM test_missing_target
70	ORDER BY a/2;
71
72--   group expression w/ target under ambiguous condition
73--   failure NOT expected
74SELECT a/2, a/2 FROM test_missing_target
75	GROUP BY a/2 ORDER BY a/2;
76
77--   group w/ existing GROUP BY target under ambiguous condition
78SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
79	WHERE x.a = y.a
80	GROUP BY x.b ORDER BY x.b;
81
82--   group w/o existing GROUP BY target under ambiguous condition
83SELECT count(*) FROM test_missing_target x, test_missing_target y
84	WHERE x.a = y.a
85	GROUP BY x.b ORDER BY x.b;
86
87--   group w/o existing GROUP BY target under ambiguous condition
88--   into a table
89SELECT count(*) INTO TABLE test_missing_target2
90FROM test_missing_target x, test_missing_target y
91	WHERE x.a = y.a
92	GROUP BY x.b ORDER BY x.b;
93SELECT * FROM test_missing_target2;
94
95
96--  Functions and expressions
97
98--   w/ existing GROUP BY target
99SELECT a%2, count(b) FROM test_missing_target
100GROUP BY test_missing_target.a%2
101ORDER BY test_missing_target.a%2;
102
103--   w/o existing GROUP BY target using a relation name in GROUP BY clause
104SELECT count(c) FROM test_missing_target
105GROUP BY lower(test_missing_target.c)
106ORDER BY lower(test_missing_target.c);
107
108--   w/o existing GROUP BY target and w/o existing a different ORDER BY target
109--   failure expected
110SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
111
112--   w/o existing GROUP BY target and w/o existing same ORDER BY target
113SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
114
115--   w/ existing GROUP BY target using a relation name in target
116SELECT lower(test_missing_target.c), count(c)
117  FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
118
119--   w/o existing GROUP BY target
120SELECT a FROM test_missing_target ORDER BY upper(d);
121
122--   w/o existing ORDER BY target
123SELECT count(b) FROM test_missing_target
124	GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
125
126--   group w/o existing GROUP BY and ORDER BY target under ambiguous condition
127--   failure expected
128SELECT count(x.a) FROM test_missing_target x, test_missing_target y
129	WHERE x.a = y.a
130	GROUP BY b/2 ORDER BY b/2;
131
132--   group w/ existing GROUP BY target under ambiguous condition
133SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y
134	WHERE x.a = y.a
135	GROUP BY x.b/2 ORDER BY x.b/2;
136
137--   group w/o existing GROUP BY target under ambiguous condition
138--   failure expected due to ambiguous b in count(b)
139SELECT count(b) FROM test_missing_target x, test_missing_target y
140	WHERE x.a = y.a
141	GROUP BY x.b/2;
142
143--   group w/o existing GROUP BY target under ambiguous condition
144--   into a table
145SELECT count(x.b) INTO TABLE test_missing_target3
146FROM test_missing_target x, test_missing_target y
147	WHERE x.a = y.a
148	GROUP BY x.b/2 ORDER BY x.b/2;
149SELECT * FROM test_missing_target3;
150
151--   Cleanup
152DROP TABLE test_missing_target;
153DROP TABLE test_missing_target2;
154DROP TABLE test_missing_target3;
155