summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/select_implicit_1.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/select_implicit_1.out')
-rw-r--r--src/test/regress/expected/select_implicit_1.out338
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;