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-- load test data 9CREATE TABLE test_missing_target (a int, b int, c char(8), d char); 10INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A'); 11INSERT INTO test_missing_target VALUES (1, 2, 'ABAB', 'b'); 12INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', 'c'); 13INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D'); 14INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e'); 15INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F'); 16INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g'); 17INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h'); 18INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I'); 19INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j'); 20-- w/ existing GROUP BY target 21SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c; 22 c | count 23----------+------- 24 ABAB | 2 25 BBBB | 2 26 CCCC | 2 27 XXXX | 1 28 bbbb | 1 29 cccc | 2 30(6 rows) 31 32-- w/o existing GROUP BY target using a relation name in GROUP BY clause 33SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c; 34 count 35------- 36 2 37 2 38 2 39 1 40 1 41 2 42(6 rows) 43 44-- w/o existing GROUP BY target and w/o existing a different ORDER BY target 45-- failure expected 46SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; 47ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function 48LINE 1: ...ECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; 49 ^ 50-- w/o existing GROUP BY target and w/o existing same ORDER BY target 51SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b; 52 count 53------- 54 1 55 2 56 3 57 4 58(4 rows) 59 60-- w/ existing GROUP BY target using a relation name in target 61SELECT test_missing_target.b, count(*) 62 FROM test_missing_target GROUP BY b ORDER BY b; 63 b | count 64---+------- 65 1 | 1 66 2 | 2 67 3 | 3 68 4 | 4 69(4 rows) 70 71-- w/o existing GROUP BY target 72SELECT c FROM test_missing_target ORDER BY a; 73 c 74---------- 75 XXXX 76 ABAB 77 ABAB 78 BBBB 79 BBBB 80 bbbb 81 cccc 82 cccc 83 CCCC 84 CCCC 85(10 rows) 86 87-- w/o existing ORDER BY target 88SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc; 89 count 90------- 91 4 92 3 93 2 94 1 95(4 rows) 96 97-- group using reference number 98SELECT count(*) FROM test_missing_target ORDER BY 1 desc; 99 count 100------- 101 10 102(1 row) 103 104-- order using reference number 105SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1; 106 c | count 107----------+------- 108 ABAB | 2 109 BBBB | 2 110 CCCC | 2 111 XXXX | 1 112 bbbb | 1 113 cccc | 2 114(6 rows) 115 116-- group using reference number out of range 117-- failure expected 118SELECT c, count(*) FROM test_missing_target GROUP BY 3; 119ERROR: GROUP BY position 3 is not in select list 120LINE 1: SELECT c, count(*) FROM test_missing_target GROUP BY 3; 121 ^ 122-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition 123-- failure expected 124SELECT count(*) FROM test_missing_target x, test_missing_target y 125 WHERE x.a = y.a 126 GROUP BY b ORDER BY b; 127ERROR: column reference "b" is ambiguous 128LINE 3: GROUP BY b ORDER BY b; 129 ^ 130-- order w/ target under ambiguous condition 131-- failure NOT expected 132SELECT a, a FROM test_missing_target 133 ORDER BY a; 134 a | a 135---+--- 136 0 | 0 137 1 | 1 138 2 | 2 139 3 | 3 140 4 | 4 141 5 | 5 142 6 | 6 143 7 | 7 144 8 | 8 145 9 | 9 146(10 rows) 147 148-- order expression w/ target under ambiguous condition 149-- failure NOT expected 150SELECT a/2, a/2 FROM test_missing_target 151 ORDER BY a/2; 152 ?column? | ?column? 153----------+---------- 154 0 | 0 155 0 | 0 156 1 | 1 157 1 | 1 158 2 | 2 159 2 | 2 160 3 | 3 161 3 | 3 162 4 | 4 163 4 | 4 164(10 rows) 165 166-- group expression w/ target under ambiguous condition 167-- failure NOT expected 168SELECT a/2, a/2 FROM test_missing_target 169 GROUP BY a/2 ORDER BY a/2; 170 ?column? | ?column? 171----------+---------- 172 0 | 0 173 1 | 1 174 2 | 2 175 3 | 3 176 4 | 4 177(5 rows) 178 179-- group w/ existing GROUP BY target under ambiguous condition 180SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y 181 WHERE x.a = y.a 182 GROUP BY x.b ORDER BY x.b; 183 b | count 184---+------- 185 1 | 1 186 2 | 2 187 3 | 3 188 4 | 4 189(4 rows) 190 191-- group w/o existing GROUP BY target under ambiguous condition 192SELECT count(*) FROM test_missing_target x, test_missing_target y 193 WHERE x.a = y.a 194 GROUP BY x.b ORDER BY x.b; 195 count 196------- 197 1 198 2 199 3 200 4 201(4 rows) 202 203-- group w/o existing GROUP BY target under ambiguous condition 204-- into a table 205SELECT count(*) INTO TABLE test_missing_target2 206FROM test_missing_target x, test_missing_target y 207 WHERE x.a = y.a 208 GROUP BY x.b ORDER BY x.b; 209SELECT * FROM test_missing_target2; 210 count 211------- 212 1 213 2 214 3 215 4 216(4 rows) 217 218-- Functions and expressions 219-- w/ existing GROUP BY target 220SELECT a%2, count(b) FROM test_missing_target 221GROUP BY test_missing_target.a%2 222ORDER BY test_missing_target.a%2; 223 ?column? | count 224----------+------- 225 0 | 5 226 1 | 5 227(2 rows) 228 229-- w/o existing GROUP BY target using a relation name in GROUP BY clause 230SELECT count(c) FROM test_missing_target 231GROUP BY lower(test_missing_target.c) 232ORDER BY lower(test_missing_target.c); 233 count 234------- 235 2 236 3 237 4 238 1 239(4 rows) 240 241-- w/o existing GROUP BY target and w/o existing a different ORDER BY target 242-- failure expected 243SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; 244ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function 245LINE 1: ...ECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; 246 ^ 247-- w/o existing GROUP BY target and w/o existing same ORDER BY target 248SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; 249 count 250------- 251 1 252 5 253 4 254(3 rows) 255 256-- w/ existing GROUP BY target using a relation name in target 257SELECT lower(test_missing_target.c), count(c) 258 FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); 259 lower | count 260-------+------- 261 abab | 2 262 bbbb | 3 263 cccc | 4 264 xxxx | 1 265(4 rows) 266 267-- w/o existing GROUP BY target 268SELECT a FROM test_missing_target ORDER BY upper(d); 269 a 270--- 271 0 272 1 273 2 274 3 275 4 276 5 277 6 278 7 279 8 280 9 281(10 rows) 282 283-- w/o existing ORDER BY target 284SELECT count(b) FROM test_missing_target 285 GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc; 286 count 287------- 288 7 289 3 290(2 rows) 291 292-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition 293-- failure expected 294SELECT count(x.a) FROM test_missing_target x, test_missing_target y 295 WHERE x.a = y.a 296 GROUP BY b/2 ORDER BY b/2; 297ERROR: column reference "b" is ambiguous 298LINE 3: GROUP BY b/2 ORDER BY b/2; 299 ^ 300-- group w/ existing GROUP BY target under ambiguous condition 301SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y 302 WHERE x.a = y.a 303 GROUP BY x.b/2 ORDER BY x.b/2; 304 ?column? | count 305----------+------- 306 0 | 1 307 1 | 5 308 2 | 4 309(3 rows) 310 311-- group w/o existing GROUP BY target under ambiguous condition 312-- failure expected due to ambiguous b in count(b) 313SELECT count(b) FROM test_missing_target x, test_missing_target y 314 WHERE x.a = y.a 315 GROUP BY x.b/2; 316ERROR: column reference "b" is ambiguous 317LINE 1: SELECT count(b) FROM test_missing_target x, test_missing_tar... 318 ^ 319-- group w/o existing GROUP BY target under ambiguous condition 320-- into a table 321SELECT count(x.b) INTO TABLE test_missing_target3 322FROM test_missing_target x, test_missing_target y 323 WHERE x.a = y.a 324 GROUP BY x.b/2 ORDER BY x.b/2; 325SELECT * FROM test_missing_target3; 326 count 327------- 328 1 329 5 330 4 331(3 rows) 332 333-- Cleanup 334DROP TABLE test_missing_target; 335DROP TABLE test_missing_target2; 336DROP TABLE test_missing_target3; 337