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