Implement SQL-compliant treatment of row comparisons for < <= > >= cases

(previously we only did = and <> correctly).  Also, allow row comparisons
with any operators that are in btree opclasses, not only those with these
specific names.  This gets rid of a whole lot of indefensible assumptions
about the behavior of particular operators based on their names ... though
it's still true that IN and NOT IN expand to "= ANY".  The patch adds a
RowCompareExpr expression node type, and makes some changes in the
representation of ANY/ALL/ROWCOMPARE SubLinks so that they can share code
with RowCompareExpr.

I have not yet done anything about making RowCompareExpr an indexable
operator, but will look at that soon.

initdb forced due to changes in stored rules.

2 files changed, 154 insertions, 0 deletions

diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index ce3bd80db7..f0b4791df4 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -117,3 +117,125 @@ select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
  Jim   | abcdefghijklabcdefgh | 1200000
 (2 rows)
 
+-- Test row comparison semantics.  Prior to PG 8.2 we did this in a totally
+-- non-spec-compliant way.
+select ROW(1,2) < ROW(1,3) as true;
+ true 
+------
+ t
+(1 row)
+
+select ROW(1,2) < ROW(1,1) as false;
+ false 
+-------
+ f
+(1 row)
+
+select ROW(1,2) < ROW(1,NULL) as null;
+ null 
+------
+ 
+(1 row)
+
+select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined
+ true 
+------
+ t
+(1 row)
+
+select ROW(11,'ABC') < ROW(11,'DEF') as true;
+ true 
+------
+ t
+(1 row)
+
+select ROW(11,'ABC') > ROW(11,'DEF') as false;
+ false 
+-------
+ f
+(1 row)
+
+select ROW(12,'ABC') > ROW(11,'DEF') as true;
+ true 
+------
+ t
+(1 row)
+
+-- = and <> have different NULL-behavior than < etc
+select ROW(1,2,3) < ROW(1,NULL,4) as null;
+ null 
+------
+ 
+(1 row)
+
+select ROW(1,2,3) = ROW(1,NULL,4) as false;
+ false 
+-------
+ f
+(1 row)
+
+select ROW(1,2,3) <> ROW(1,NULL,4) as true;
+ true 
+------
+ t
+(1 row)
+
+-- We allow operators beyond the six standard ones, if they have btree
+-- operator classes.
+select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true;
+ true 
+------
+ t
+(1 row)
+
+select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false;
+ false 
+-------
+ f
+(1 row)
+
+select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail;
+ERROR:  could not determine interpretation of row comparison operator ~~
+HINT:  Row comparison operators must be associated with btree operator classes.
+-- Check row comparison with a subselect
+select unique1, unique2 from tenk1
+where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3);
+ unique1 | unique2 
+---------+---------
+       1 |    2838
+       0 |    9998
+(2 rows)
+
+-- Also check row comparison with an indexable condition
+select thousand, tenthous from tenk1
+where (thousand, tenthous) >= (997, 5000)
+order by thousand, tenthous;
+ thousand | tenthous 
+----------+----------
+      997 |     5997
+      997 |     6997
+      997 |     7997
+      997 |     8997
+      997 |     9997
+      998 |      998
+      998 |     1998
+      998 |     2998
+      998 |     3998
+      998 |     4998
+      998 |     5998
+      998 |     6998
+      998 |     7998
+      998 |     8998
+      998 |     9998
+      999 |      999
+      999 |     1999
+      999 |     2999
+      999 |     3999
+      999 |     4999
+      999 |     5999
+      999 |     6999
+      999 |     7999
+      999 |     8999
+      999 |     9999
+(25 rows)
+
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index d09ff662ab..613c4e91f9 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -72,3 +72,35 @@ insert into pp values (repeat('abcdefghijkl', 100000));
 insert into people select ('Jim', f1, null)::fullname, current_date from pp;
 
 select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
+
+-- Test row comparison semantics.  Prior to PG 8.2 we did this in a totally
+-- non-spec-compliant way.
+
+select ROW(1,2) < ROW(1,3) as true;
+select ROW(1,2) < ROW(1,1) as false;
+select ROW(1,2) < ROW(1,NULL) as null;
+select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined
+select ROW(11,'ABC') < ROW(11,'DEF') as true;
+select ROW(11,'ABC') > ROW(11,'DEF') as false;
+select ROW(12,'ABC') > ROW(11,'DEF') as true;
+
+-- = and <> have different NULL-behavior than < etc
+select ROW(1,2,3) < ROW(1,NULL,4) as null;
+select ROW(1,2,3) = ROW(1,NULL,4) as false;
+select ROW(1,2,3) <> ROW(1,NULL,4) as true;
+
+-- We allow operators beyond the six standard ones, if they have btree
+-- operator classes.
+select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true;
+select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false;
+select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail;
+
+-- Check row comparison with a subselect
+select unique1, unique2 from tenk1
+where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3);
+
+-- Also check row comparison with an indexable condition
+select thousand, tenthous from tenk1
+where (thousand, tenthous) >= (997, 5000)
+order by thousand, tenthous;
+