From: David Hartwig <daybee@bellatlantic.net>

I have attached a patch to allow GROUP BY and/or ORDER BY function or
expressions.  Note worthy items:

1. The expression or function need not be in the target list.
Example:
            SELECT  name FROM foo GROUP BY lower(name);

2.   Simplified the grammar to use expressions only.

3.  Cleaned up earlier patch in this area to make use of existing
utility functions.

3.  Reduced some of the members in the SortGroupBy parse node.   The
original data members were redundant with the new expression node.
(MUST do a "make clean" now)

4.  Added a new parse node "JoinUsing".   The JOIN USING clause was
overloading this SortGroupBy structure.   With the afore mentioned
reduction of members, the two clauses lost all their commonality.

5.  A bug still exist where, if a function or expression is GROUPed BY,
and an aggregate function does not include a attribute from the
expression or function, the backend crashes.   (or something like
that)   The bug pre-dates this patch.    Example:

    SELECT lower(a) AS lowcase, count(b) FROM foo GROUP BY lowcase;
                 *** BOOM  ***

    --Also when not in target list
    SELECT  count(b) FROM foo GROUP BY lower(a);
                *** BOOM  AGAIN ***

2 files changed, 228 insertions, 19 deletions

diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out
index 42af780a61f..22df795676c 100644
--- a/src/test/regress/expected/select_implicit.out
+++ b/src/test/regress/expected/select_implicit.out
@@ -4,28 +4,32 @@ QUERY: INSERT INTO test_missing_target VALUES (1, 2, 'AAAA');
 QUERY: INSERT INTO test_missing_target VALUES (2, 2, 'AAAA');
 QUERY: INSERT INTO test_missing_target VALUES (3, 3, 'BBBB');
 QUERY: INSERT INTO test_missing_target VALUES (4, 3, 'BBBB');
-QUERY: INSERT INTO test_missing_target VALUES (5, 3, 'BBBB');
-QUERY: INSERT INTO test_missing_target VALUES (6, 4, 'CCCC');
-QUERY: INSERT INTO test_missing_target VALUES (7, 4, 'CCCC');
+QUERY: INSERT INTO test_missing_target VALUES (5, 3, 'bbbb');
+QUERY: INSERT INTO test_missing_target VALUES (6, 4, 'cccc');
+QUERY: INSERT INTO test_missing_target VALUES (7, 4, 'cccc');
 QUERY: INSERT INTO test_missing_target VALUES (8, 4, 'CCCC');
 QUERY: INSERT INTO test_missing_target VALUES (9, 4, 'CCCC');
 QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c;
 c       |count
 --------+-----
 AAAA    |    2
-BBBB    |    3
-CCCC    |    4
+BBBB    |    2
+CCCC    |    2
 XXXX    |    1
-(4 rows)
+bbbb    |    1
+cccc    |    2
+(6 rows)
 
 QUERY: SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c;
 count
 -----
     2
-    3
-    4
+    2
+    2
     1
-(4 rows)
+    1
+    2
+(6 rows)
 
 QUERY: SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
 ERROR:  parser: illegal use of aggregates or non-group column in target list
@@ -56,9 +60,9 @@ AAAA
 AAAA    
 BBBB    
 BBBB    
-BBBB    
-CCCC    
-CCCC    
+bbbb    
+cccc    
+cccc    
 CCCC    
 CCCC    
 (10 rows)
@@ -82,17 +86,54 @@ QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 1;
 c       |count
 --------+-----
 AAAA    |    2
-BBBB    |    3
-CCCC    |    4
+BBBB    |    2
+CCCC    |    2
 XXXX    |    1
-(4 rows)
+bbbb    |    1
+cccc    |    2
+(6 rows)
 
 QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 3;
-ERROR:  ORDER/GROUP BY position 3 is not in target list
+ERROR:  GROUP BY position 3 is not in target list
 QUERY: SELECT count(*) FROM test_missing_target x, test_missing_target y
 	WHERE x.a = y.a
 	GROUP BY b ORDER BY b;
 ERROR:  Column b is ambiguous
+QUERY: 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)
+
+QUERY: 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)
+
+QUERY: SELECT a/2, a/2 FROM test_missing_target
+	GROUP BY a/2;
+ERROR:  GROUP BY has ambiguous expression
 QUERY: SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y
 	WHERE x.a = y.a
 	GROUP BY x.b;
@@ -128,5 +169,100 @@ count
     4
 (4 rows)
 
+QUERY: SELECT a%2, count(a) FROM test_missing_target GROUP BY test_missing_target.a%2;
+?column?|count
+--------+-----
+       0|    5
+       1|    5
+(2 rows)
+
+QUERY: /*
+	NOTE: as of 1998-08-01 a bug was detected unrelated to this feature which
+	requires the aggragate function argument to be the same as some non-agragate
+	in the target list.   (i.e. count(*) and count(b) crash the backend.)
+*/
+SELECT count(c) FROM test_missing_target GROUP BY lower(test_missing_target.c);
+count
+-----
+    2
+    3
+    4
+    1
+(4 rows)
+
+QUERY: SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b;
+ERROR:  parser: illegal use of aggregates or non-group column in target list
+QUERY: SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
+count
+-----
+    1
+    5
+    4
+(3 rows)
+
+QUERY: 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)
+
+QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
+a
+-
+1
+2
+3
+4
+5
+6
+7
+8
+9
+0
+(10 rows)
+
+QUERY: SELECT count(b) FROM test_missing_target
+	GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
+count
+-----
+    7
+    3
+(2 rows)
+
+QUERY: 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 b is ambiguous
+QUERY: 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;
+?column?|count
+--------+-----
+       0|    1
+       1|    5
+       2|    4
+(3 rows)
+
+QUERY: SELECT count(b) FROM test_missing_target x, test_missing_target y
+	WHERE x.a = y.a
+	GROUP BY x.b/2;
+ERROR:  Column b is ambiguous
+QUERY: 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;
+QUERY: SELECT * FROM test_missing_target3;
+count
+-----
+    1
+    5
+    4
+(3 rows)
+
 QUERY: DROP TABLE test_missing_target;
 QUERY: DROP TABLE test_missing_target2;
+QUERY: DROP TABLE test_missing_target3;
diff --git a/src/test/regress/sql/select_implicit.sql b/src/test/regress/sql/select_implicit.sql
index 44e06a95b62..a867b9b5564 100644
--- a/src/test/regress/sql/select_implicit.sql
+++ b/src/test/regress/sql/select_implicit.sql
@@ -13,9 +13,9 @@ INSERT INTO test_missing_target VALUES (1, 2, 'AAAA');
 INSERT INTO test_missing_target VALUES (2, 2, 'AAAA');
 INSERT INTO test_missing_target VALUES (3, 3, 'BBBB');
 INSERT INTO test_missing_target VALUES (4, 3, 'BBBB');
-INSERT INTO test_missing_target VALUES (5, 3, 'BBBB');
-INSERT INTO test_missing_target VALUES (6, 4, 'CCCC');
-INSERT INTO test_missing_target VALUES (7, 4, 'CCCC');
+INSERT INTO test_missing_target VALUES (5, 3, 'bbbb');
+INSERT INTO test_missing_target VALUES (6, 4, 'cccc');
+INSERT INTO test_missing_target VALUES (7, 4, 'cccc');
 INSERT INTO test_missing_target VALUES (8, 4, 'CCCC');
 INSERT INTO test_missing_target VALUES (9, 4, 'CCCC');
 
@@ -59,6 +59,21 @@ SELECT count(*) FROM test_missing_target x, test_missing_target y
 	WHERE x.a = y.a
 	GROUP BY b ORDER BY b;
 
+--   order w/ target under ambigious condition
+--   failure NOT expected
+SELECT a, a FROM test_missing_target
+	ORDER BY a;
+
+--   order expression w/ target under ambigious condition
+--   failure NOT expected
+SELECT a/2, a/2 FROM test_missing_target
+	ORDER BY a/2;
+
+--   group expression w/ target under ambigious condition
+--   failure expected
+SELECT a/2, a/2 FROM test_missing_target
+	GROUP BY a/2;
+
 --   group w/ existing GROUP BY target under ambigious condition
 SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y 
 	WHERE x.a = y.a
@@ -77,7 +92,65 @@ FROM test_missing_target x, test_missing_target y
 	GROUP BY x.b;
 SELECT * FROM test_missing_target2;
 
+
+--  Functions and expressions
+
+--   w/ existing GROUP BY target
+SELECT a%2, count(a) FROM test_missing_target GROUP BY test_missing_target.a%2;
+/*
+	NOTE: as of 1998-08-01 a bug was detected unrelated to this feature which 
+	requires the aggragate function argument to be the same as some non-agragate 
+	in the target list.   (i.e. count(*) and count(b) crash the backend.)
+*/
+
+--   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);
+
+--   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;
+
+--   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;
+
+--   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);
+
+--   w/o existing GROUP BY target
+SELECT a FROM test_missing_target ORDER BY upper(c);
+
+--   w/o existing ORDER BY target
+SELECT count(b) FROM test_missing_target
+	GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc;
+
+--   group w/o existing GROUP BY and ORDER BY target under ambigious 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;
+
+--   group w/ existing GROUP BY target under ambigious 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;
+
+--   group w/o existing GROUP BY target under ambigious condition
+SELECT count(b) FROM test_missing_target x, test_missing_target y 
+	WHERE x.a = y.a
+	GROUP BY x.b/2;
+
+--   group w/o existing GROUP BY target under ambigious 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;
+SELECT * FROM test_missing_target3;
+
 --   Cleanup
 DROP TABLE test_missing_target;
 DROP TABLE test_missing_target2;
+DROP TABLE test_missing_target3;
+