--
-- 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
 CCCC     |     2
 XXXX     |     1
 bbbb     |     1
 cccc     |     2
(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
     2
     1
     1
     2
(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
 CCCC     |     2
 XXXX     |     1
 bbbb     |     1
 cccc     |     2
(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;