select_implicit_2.out 7.64 KiB
--
-- SELECT_IMPLICIT
-- Test cases for queries with ordering terms missing from the target list.
-- This used to be called "junkfilter.sql".
-- The parser uses the term "resjunk" to handle these cases.
-- - thomas 1998-07-09
--
-- load test data
CREATE TABLE test_missing_target (a int, b int, c char(8), d char);
INSERT INTO test_missing_target VALUES (0, 1, 'XXXX', 'A');
INSERT INTO test_missing_target VALUES (1, 2, 'AAAA', 'b');
INSERT INTO test_missing_target VALUES (2, 2, 'AAAA', 'c');
INSERT INTO test_missing_target VALUES (3, 3, 'BBBB', 'D');
INSERT INTO test_missing_target VALUES (4, 3, 'BBBB', 'e');
INSERT INTO test_missing_target VALUES (5, 3, 'bbbb', 'F');
INSERT INTO test_missing_target VALUES (6, 4, 'cccc', 'g');
INSERT INTO test_missing_target VALUES (7, 4, 'cccc', 'h');
INSERT INTO test_missing_target VALUES (8, 4, 'CCCC', 'I');
INSERT INTO test_missing_target VALUES (9, 4, 'CCCC', 'j');
-- w/ existing GROUP BY target
SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
c | count
----------+-------
AAAA | 2
bbbb | 1
BBBB | 2
cccc | 2
CCCC | 2
XXXX | 1
(6 rows)
-- w/o existing GROUP BY target using a relation name in GROUP BY clause
SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c ORDER BY c;
count
-------
2
1
2
2
2
1
(6 rows)
-- w/o existing GROUP BY target and w/o existing a different ORDER BY target
-- failure expected
SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function
-- w/o existing GROUP BY target and w/o existing same ORDER BY target
SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b;
count
-------
1
2
3
4
(4 rows)
-- w/ existing GROUP BY target using a relation name in target
SELECT test_missing_target.b, count(*)
FROM test_missing_target GROUP BY b ORDER BY b;
b | count
---+-------
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
-- w/o existing GROUP BY target
SELECT c FROM test_missing_target ORDER BY a; c
----------
XXXX
AAAA
AAAA
BBBB
BBBB
bbbb
cccc
cccc
CCCC
CCCC
(10 rows)
-- w/o existing ORDER BY target
SELECT count(*) FROM test_missing_target GROUP BY b ORDER BY b desc;
count
-------
4
3
2
1
(4 rows)
-- group using reference number
SELECT count(*) FROM test_missing_target ORDER BY 1 desc;
count
-------
10
(1 row)
-- order using reference number
SELECT c, count(*) FROM test_missing_target GROUP BY 1 ORDER BY 1;
c | count
----------+-------
AAAA | 2
bbbb | 1
BBBB | 2
cccc | 2
CCCC | 2
XXXX | 1
(6 rows)
-- group using reference number out of range
-- failure expected
SELECT c, count(*) FROM test_missing_target GROUP BY 3;
ERROR: GROUP BY position 3 is not in select list
-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
-- failure expected
SELECT count(*) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY b ORDER BY b;
ERROR: column reference "b" is ambiguous
-- order w/ target under ambiguous condition
-- failure NOT expected
SELECT a, a FROM test_missing_target
ORDER BY a;
a | a
---+---
0 | 0
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
(10 rows)
-- order expression w/ target under ambiguous condition
-- failure NOT expected
SELECT a/2, a/2 FROM test_missing_target
ORDER BY a/2;
?column? | ?column?
----------+----------
0 | 0
0 | 0
1 | 1
1 | 1
2 | 2
2 | 2
3 | 3
3 | 3
4 | 4
4 | 4
(10 rows)
-- group expression w/ target under ambiguous condition
-- failure NOT expected
SELECT a/2, a/2 FROM test_missing_target
GROUP BY a/2 ORDER BY a/2;
?column? | ?column?
----------+----------
0 | 0
1 | 1
2 | 2
3 | 3
4 | 4
(5 rows)
-- group w/ existing GROUP BY target under ambiguous condition
SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
b | count
---+-------
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
-- group w/o existing GROUP BY target under ambiguous condition
SELECT count(*) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
count
-------
1
2
3
4
(4 rows)
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
SELECT count(*) INTO TABLE test_missing_target2
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
SELECT * FROM test_missing_target2;
count
-------
1
2
3
4
(4 rows)
-- Functions and expressions
-- w/ existing GROUP BY target
SELECT a%2, count(b) FROM test_missing_target
GROUP BY test_missing_target.a%2
ORDER BY test_missing_target.a%2;
?column? | count
----------+-------
0 | 5
1 | 5
(2 rows)
-- w/o existing GROUP BY target using a relation name in GROUP BY clause
SELECT count(c) FROM test_missing_target
GROUP BY lower(test_missing_target.c)
ORDER BY lower(test_missing_target.c);
count
-------
2
3
4
1
(4 rows)
-- w/o existing GROUP BY target and w/o existing a different ORDER BY target
-- failure expected
SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
ERROR: column "test_missing_target.b" must appear in the GROUP BY clause or be used in an aggregate function
-- w/o existing GROUP BY target and w/o existing same ORDER BY target
SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
count
-------
1
5
4
(3 rows)
-- w/ existing GROUP BY target using a relation name in target
SELECT lower(test_missing_target.c), count(c)
FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
lower | count
-------+-------
aaaa | 2
bbbb | 3
cccc | 4
xxxx | 1
(4 rows)
-- w/o existing GROUP BY target
SELECT a FROM test_missing_target ORDER BY upper(d);
a
---
0
1
2
3
4
5
6
7
8
9
(10 rows)
-- w/o existing ORDER BY target
SELECT count(b) FROM test_missing_target
GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
count
-------
7 3
(2 rows)
-- group w/o existing GROUP BY and ORDER BY target under ambiguous condition
-- failure expected
SELECT count(x.a) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY b/2 ORDER BY b/2;
ERROR: column reference "b" is ambiguous
-- group w/ existing GROUP BY target under ambiguous condition
SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
?column? | count
----------+-------
0 | 1
1 | 5
2 | 4
(3 rows)
-- group w/o existing GROUP BY target under ambiguous condition
-- failure expected due to ambiguous b in count(b)
SELECT count(b) FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2;
ERROR: column reference "b" is ambiguous
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
SELECT count(x.b) INTO TABLE test_missing_target3
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
SELECT * FROM test_missing_target3;
count
-------
1
5
4
(3 rows)
-- Cleanup
DROP TABLE test_missing_target;
DROP TABLE test_missing_target2;
DROP TABLE test_missing_target3;