Add int2-vs-int8 comparison operators. These are now necessary because

the planner may try to generate them as a result of transitivity of the
existing int2-vs-int4 and int4-vs-int8 operators.  In fact, it is now
necessary that mergejoinable cross-datatype operators form closed sets.
Add an opr_sanity regress test to detect missing operators.

6 files changed, 206 insertions, 5 deletions

diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 773ec8b193d..b88fc5c45b0 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.23 2000/07/12 22:59:09 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.24 2000/07/28 05:07:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -111,7 +111,7 @@ int8out(PG_FUNCTION_ARGS)
 
 
 /*----------------------------------------------------------
- *	Relational operators for int8s.
+ *	Relational operators for int8s, including cross-data-type comparisons.
  *---------------------------------------------------------*/
 
 /* int8relop()
@@ -285,6 +285,120 @@ int48ge(PG_FUNCTION_ARGS)
 	PG_RETURN_BOOL(val1 >= val2);
 }
 
+/* int82relop()
+ * Is 64-bit val1 relop 16-bit val2?
+ */
+Datum
+int82eq(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 == val2);
+}
+
+Datum
+int82ne(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 != val2);
+}
+
+Datum
+int82lt(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 < val2);
+}
+
+Datum
+int82gt(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 > val2);
+}
+
+Datum
+int82le(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 <= val2);
+}
+
+Datum
+int82ge(PG_FUNCTION_ARGS)
+{
+	int64		val1 = PG_GETARG_INT64(0);
+	int16		val2 = PG_GETARG_INT16(1);
+
+	PG_RETURN_BOOL(val1 >= val2);
+}
+
+/* int28relop()
+ * Is 16-bit val1 relop 64-bit val2?
+ */
+Datum
+int28eq(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 == val2);
+}
+
+Datum
+int28ne(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 != val2);
+}
+
+Datum
+int28lt(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 < val2);
+}
+
+Datum
+int28gt(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 > val2);
+}
+
+Datum
+int28le(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 <= val2);
+}
+
+Datum
+int28ge(PG_FUNCTION_ARGS)
+{
+	int16		val1 = PG_GETARG_INT16(0);
+	int64		val2 = PG_GETARG_INT64(1);
+
+	PG_RETURN_BOOL(val1 >= val2);
+}
+
 
 /*----------------------------------------------------------
  *	Arithmetic operators on 64-bit integers.
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 20ea41ec0fb..e8881bf037a 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_operator.h,v 1.77 2000/07/17 03:05:23 tgl Exp $
+ * $Id: pg_operator.h,v 1.78 2000/07/28 05:07:42 tgl Exp $
  *
  * NOTES
  *	  the genbki.sh script reads this file and generates .bki
@@ -746,6 +746,20 @@ DATA(insert OID = 1815 (  "<<"	  PGUID 0 b t f 1562 1562 1562	  0    0	0	 0 varb
 DATA(insert OID = 1816 (  ">>"	  PGUID 0 b t f 1562 1562 1562	  0    0	0	 0 varbitshiftright - - ));
 DATA(insert OID = 1817 (  "||"	  PGUID 0 b t f 1562 1562 1562	  0    0	0	 0 varbitcat - - ));
 
+DATA(insert OID = 1862 ( "="	   PGUID 0 b t f  21  20  16 1868  1863  95 412 int28eq eqsel eqjoinsel ));
+DATA(insert OID = 1863 ( "<>"	   PGUID 0 b t f  21  20  16 1869  1862   0   0 int28ne neqsel neqjoinsel ));
+DATA(insert OID = 1864 ( "<"	   PGUID 0 b t f  21  20  16 1871  1867   0   0 int28lt scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1865 ( ">"	   PGUID 0 b t f  21  20  16 1870  1866   0   0 int28gt scalargtsel scalargtjoinsel ));
+DATA(insert OID = 1866 ( "<="	   PGUID 0 b t f  21  20  16 1873  1865   0   0 int28le scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1867 ( ">="	   PGUID 0 b t f  21  20  16 1872  1864   0   0 int28ge scalargtsel scalargtjoinsel ));
+
+DATA(insert OID = 1868 ( "="	   PGUID 0 b t f  20  21  16  1862 1869 412 95 int82eq eqsel eqjoinsel ));
+DATA(insert OID = 1869 ( "<>"	   PGUID 0 b t f  20  21  16  1863 1868   0  0 int82ne neqsel neqjoinsel ));
+DATA(insert OID = 1870 ( "<"	   PGUID 0 b t f  20  21  16  1865 1873   0  0 int82lt scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1871 ( ">"	   PGUID 0 b t f  20  21  16  1864 1872   0  0 int82gt scalargtsel scalargtjoinsel ));
+DATA(insert OID = 1872 ( "<="	   PGUID 0 b t f  20  21  16  1867 1871   0  0 int82le scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1873 ( ">="	   PGUID 0 b t f  20  21  16  1866 1870   0  0 int82ge scalargtsel scalargtjoinsel ));
+
 /*
  * function prototypes
  */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c92ce065fb3..56654c9f329 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.148 2000/07/17 03:05:25 tgl Exp $
+ * $Id: pg_proc.h,v 1.149 2000/07/28 05:07:42 tgl Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2491,6 +2491,32 @@ DESCR("aggregate transition function");
 DATA(insert OID = 1844 (  interval_avg	   PGUID 12 f t t t 1 f 1186 "1187" 100 0 0 100  interval_avg - ));
 DESCR("AVG aggregate final function");
 
+DATA(insert OID = 1850 (  int28eq		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28eq - ));
+DESCR("equal");
+DATA(insert OID = 1851 (  int28ne		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28ne - ));
+DESCR("not equal");
+DATA(insert OID = 1852 (  int28lt		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28lt - ));
+DESCR("less-than");
+DATA(insert OID = 1853 (  int28gt		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28gt - ));
+DESCR("greater-than");
+DATA(insert OID = 1854 (  int28le		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28le - ));
+DESCR("less-than-or-equal");
+DATA(insert OID = 1855 (  int28ge		   PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100  int28ge - ));
+DESCR("greater-than-or-equal");
+
+DATA(insert OID = 1856 (  int82eq		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82eq - ));
+DESCR("equal");
+DATA(insert OID = 1857 (  int82ne		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82ne - ));
+DESCR("not equal");
+DATA(insert OID = 1858 (  int82lt		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82lt - ));
+DESCR("less-than");
+DATA(insert OID = 1859 (  int82gt		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82gt - ));
+DESCR("greater-than");
+DATA(insert OID = 1860 (  int82le		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82le - ));
+DESCR("less-than-or-equal");
+DATA(insert OID = 1861 (  int82ge		   PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100  int82ge - ));
+DESCR("greater-than-or-equal");
+
 
 /*
  * prototypes for functions pg_proc.c
diff --git a/src/include/utils/int8.h b/src/include/utils/int8.h
index 77dc6d7212f..83791f281bb 100644
--- a/src/include/utils/int8.h
+++ b/src/include/utils/int8.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: int8.h,v 1.22 2000/06/13 07:35:30 tgl Exp $
+ * $Id: int8.h,v 1.23 2000/07/28 05:07:44 tgl Exp $
  *
  * NOTES
  * These data types are supported on all 64-bit architectures, and may
@@ -51,6 +51,20 @@ extern Datum int48gt(PG_FUNCTION_ARGS);
 extern Datum int48le(PG_FUNCTION_ARGS);
 extern Datum int48ge(PG_FUNCTION_ARGS);
 
+extern Datum int82eq(PG_FUNCTION_ARGS);
+extern Datum int82ne(PG_FUNCTION_ARGS);
+extern Datum int82lt(PG_FUNCTION_ARGS);
+extern Datum int82gt(PG_FUNCTION_ARGS);
+extern Datum int82le(PG_FUNCTION_ARGS);
+extern Datum int82ge(PG_FUNCTION_ARGS);
+
+extern Datum int28eq(PG_FUNCTION_ARGS);
+extern Datum int28ne(PG_FUNCTION_ARGS);
+extern Datum int28lt(PG_FUNCTION_ARGS);
+extern Datum int28gt(PG_FUNCTION_ARGS);
+extern Datum int28le(PG_FUNCTION_ARGS);
+extern Datum int28ge(PG_FUNCTION_ARGS);
+
 extern Datum int8um(PG_FUNCTION_ARGS);
 extern Datum int8pl(PG_FUNCTION_ARGS);
 extern Datum int8mi(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index e784486f81d..18582c1f5bb 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -310,6 +310,23 @@ WHERE p1.oprlsortop != 0 AND NOT
 -----+---------
 (0 rows)
 
+-- Mergejoinable operators across datatypes must come in closed sets, that
+-- is if you provide int2 = int4 and int4 = int8 then you must also provide
+-- int2 = int8 (and commutators of all these).  This is necessary because
+-- the planner tries to deduce additional qual clauses from transitivity
+-- of mergejoinable operators.  If there are clauses int2var = int4var and
+-- int4var = int8var, the planner will deduce int2var = int8var ... and it
+-- had better have a way to represent it.
+SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
+WHERE p1.oprlsortop != p1.oprrsortop AND
+      p1.oprrsortop = p2.oprlsortop AND
+      p2.oprlsortop != p2.oprrsortop AND
+      NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
+      p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
+ oid | oid 
+-----+-----
+(0 rows)
+
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index fb43748a1cb..a79d0eae754 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -256,6 +256,22 @@ WHERE p1.oprlsortop != 0 AND NOT
         p2.oprright = p1.oprright AND
         p2.oprkind = 'b');
 
+-- Mergejoinable operators across datatypes must come in closed sets, that
+-- is if you provide int2 = int4 and int4 = int8 then you must also provide
+-- int2 = int8 (and commutators of all these).  This is necessary because
+-- the planner tries to deduce additional qual clauses from transitivity
+-- of mergejoinable operators.  If there are clauses int2var = int4var and
+-- int4var = int8var, the planner will deduce int2var = int8var ... and it
+-- had better have a way to represent it.
+
+SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
+WHERE p1.oprlsortop != p1.oprrsortop AND
+      p1.oprrsortop = p2.oprlsortop AND
+      p2.oprlsortop != p2.oprrsortop AND
+      NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
+      p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
+
+
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".