diff options
Diffstat (limited to 'src/test/regress/expected/select_implicit_1.out')
| -rw-r--r-- | src/test/regress/expected/select_implicit_1.out | 338 |
1 files changed, 338 insertions, 0 deletions
diff --git a/src/test/regress/expected/select_implicit_1.out b/src/test/regress/expected/select_implicit_1.out new file mode 100644 index 0000000..d67521e --- /dev/null +++ b/src/test/regress/expected/select_implicit_1.out @@ -0,0 +1,338 @@ +-- +-- 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, 'ABAB', 'b'); +INSERT INTO test_missing_target VALUES (2, 2, 'ABAB', '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 +----------+------- + ABAB | 2 + BBBB | 2 + bbbb | 1 + 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 + 2 + 1 + 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 +LINE 1: ...ECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; + ^ +-- 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 + ABAB + ABAB + 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 +----------+------- + ABAB | 2 + BBBB | 2 + bbbb | 1 + 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 +LINE 1: SELECT c, count(*) FROM test_missing_target GROUP BY 3; + ^ +-- 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 +LINE 3: GROUP BY b ORDER BY b; + ^ +-- 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 +CREATE TABLE test_missing_target2 AS +SELECT count(*) +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 +LINE 1: ...ECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; + ^ +-- 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 +-------+------- + abab | 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 +LINE 3: GROUP BY b/2 ORDER BY b/2; + ^ +-- 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 +LINE 1: SELECT count(b) FROM test_missing_target x, test_missing_tar... + ^ +-- group w/o existing GROUP BY target under ambiguous condition +-- into a table +CREATE TABLE test_missing_target3 AS +SELECT 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; +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; |