Redesign DISTINCT ON as discussed in pgsql-sql 1/25/00: syntax is now

SELECT DISTINCT ON (expr [, expr ...]) targetlist ...
and there is a check to make sure that the user didn't specify an ORDER BY
that's incompatible with the DISTINCT operation.
Reimplement nodeUnique and nodeGroup to use the proper datatype-specific
equality function for each column being compared --- they used to do
bitwise comparisons or convert the data to text strings and strcmp().
(To add insult to injury, they'd look up the conversion functions once
for each tuple...)  Parse/plan representation of DISTINCT is now a list
of SortClause nodes.
initdb forced by querytree change...

4 files changed, 80 insertions, 24 deletions

diff --git a/src/test/regress/expected/errors.out b/src/test/regress/expected/errors.out
index 6a280f321a5..61f1dfd0129 100644
--- a/src/test/regress/expected/errors.out
+++ b/src/test/regress/expected/errors.out
@@ -29,12 +29,12 @@ ERROR:  attribute 'nonesuch' not found
 -- bad attribute name on rhs of operator
 select * from pg_database where pg_database.datname = nonesuch;
 ERROR:  attribute 'nonesuch' not found
--- bad select distinct on  syntax,  distinct attribute missing
-select distinct on foobar from pg_database;
+-- bad select distinct on syntax, distinct attribute missing
+select distinct on (foobar) from pg_database;
 ERROR:  parser: parse error at or near "from"
 -- bad select distinct on syntax, distinct attribute not in target list
-select distinct on foobar * from pg_database;
-ERROR:  All fields in the UNIQUE ON clause must appear in the target list
+select distinct on (foobar) * from pg_database;
+ERROR:  attribute 'foobar' not found
 --
 -- DELETE
  
diff --git a/src/test/regress/expected/select_distinct_on.out b/src/test/regress/expected/select_distinct_on.out
index 067f74db4f6..c4b03e95f76 100644
--- a/src/test/regress/expected/select_distinct_on.out
+++ b/src/test/regress/expected/select_distinct_on.out
@@ -1,18 +1,66 @@
 --
 -- SELECT_DISTINCT_ON
 --
-SELECT DISTINCT ON string4 two, string4, ten
-	   FROM tmp
-   ORDER BY two using <, string4 using <, ten using <;
- two | string4 | ten 
------+---------+-----
-   0 | AAAAxx  |   0
-   0 | HHHHxx  |   0
-   0 | OOOOxx  |   0
-   0 | VVVVxx  |   0
-   1 | AAAAxx  |   1
-   1 | HHHHxx  |   1
-   1 | OOOOxx  |   1
-   1 | VVVVxx  |   1
-(8 rows)
+SELECT DISTINCT ON (string4) string4, two, ten
+   FROM tmp
+   ORDER BY string4 using <, two using >, ten using <;
+ string4 | two | ten 
+---------+-----+-----
+ AAAAxx  |   1 |   1
+ HHHHxx  |   1 |   1
+ OOOOxx  |   1 |   1
+ VVVVxx  |   1 |   1
+(4 rows)
+
+-- this will fail due to conflict of ordering requirements
+SELECT DISTINCT ON (string4, ten) string4, two, ten
+   FROM tmp
+   ORDER BY string4 using <, two using <, ten using <;
+ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
+SELECT DISTINCT ON (string4, ten) string4, ten, two
+   FROM tmp
+   ORDER BY string4 using <, ten using >, two using <;
+ string4 | ten | two 
+---------+-----+-----
+ AAAAxx  |   9 |   1
+ AAAAxx  |   8 |   0
+ AAAAxx  |   7 |   1
+ AAAAxx  |   6 |   0
+ AAAAxx  |   5 |   1
+ AAAAxx  |   4 |   0
+ AAAAxx  |   3 |   1
+ AAAAxx  |   2 |   0
+ AAAAxx  |   1 |   1
+ AAAAxx  |   0 |   0
+ HHHHxx  |   9 |   1
+ HHHHxx  |   8 |   0
+ HHHHxx  |   7 |   1
+ HHHHxx  |   6 |   0
+ HHHHxx  |   5 |   1
+ HHHHxx  |   4 |   0
+ HHHHxx  |   3 |   1
+ HHHHxx  |   2 |   0
+ HHHHxx  |   1 |   1
+ HHHHxx  |   0 |   0
+ OOOOxx  |   9 |   1
+ OOOOxx  |   8 |   0
+ OOOOxx  |   7 |   1
+ OOOOxx  |   6 |   0
+ OOOOxx  |   5 |   1
+ OOOOxx  |   4 |   0
+ OOOOxx  |   3 |   1
+ OOOOxx  |   2 |   0
+ OOOOxx  |   1 |   1
+ OOOOxx  |   0 |   0
+ VVVVxx  |   9 |   1
+ VVVVxx  |   8 |   0
+ VVVVxx  |   7 |   1
+ VVVVxx  |   6 |   0
+ VVVVxx  |   5 |   1
+ VVVVxx  |   4 |   0
+ VVVVxx  |   3 |   1
+ VVVVxx  |   2 |   0
+ VVVVxx  |   1 |   1
+ VVVVxx  |   0 |   0
+(40 rows)
 
diff --git a/src/test/regress/sql/errors.sql b/src/test/regress/sql/errors.sql
index cd43cb909b0..ffcb5fcdb74 100644
--- a/src/test/regress/sql/errors.sql
+++ b/src/test/regress/sql/errors.sql
@@ -34,12 +34,12 @@ select * from pg_database where nonesuch = pg_database.datname;
 select * from pg_database where pg_database.datname = nonesuch;
 
 
--- bad select distinct on  syntax,  distinct attribute missing
-select distinct on foobar from pg_database;
+-- bad select distinct on syntax, distinct attribute missing
+select distinct on (foobar) from pg_database;
 
 
 -- bad select distinct on syntax, distinct attribute not in target list
-select distinct on foobar * from pg_database;
+select distinct on (foobar) * from pg_database;
 
 
 --
diff --git a/src/test/regress/sql/select_distinct_on.sql b/src/test/regress/sql/select_distinct_on.sql
index 87001a0c088..54d98ca6979 100644
--- a/src/test/regress/sql/select_distinct_on.sql
+++ b/src/test/regress/sql/select_distinct_on.sql
@@ -2,7 +2,15 @@
 -- SELECT_DISTINCT_ON
 --
 
-SELECT DISTINCT ON string4 two, string4, ten
-	   FROM tmp
-   ORDER BY two using <, string4 using <, ten using <;
+SELECT DISTINCT ON (string4) string4, two, ten
+   FROM tmp
+   ORDER BY string4 using <, two using >, ten using <;
 
+-- this will fail due to conflict of ordering requirements
+SELECT DISTINCT ON (string4, ten) string4, two, ten
+   FROM tmp
+   ORDER BY string4 using <, two using <, ten using <;
+
+SELECT DISTINCT ON (string4, ten) string4, ten, two
+   FROM tmp
+   ORDER BY string4 using <, ten using >, two using <;