Skip to content

Commit d2d7865

Browse files
tglsfdctglsfdc
committed
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.
1 parent b399b86 commit d2d7865

File tree

6 files changed

+206
-5
lines changed

6 files changed

+206
-5
lines changed

‎src/backend/utils/adt/int8.c

Lines changed: 116 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.23 2000/07/12 22:59:09 petere Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.24 2000/07/28 05:07:41 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -111,7 +111,7 @@ int8out(PG_FUNCTION_ARGS)
111111

112112

113113
/*----------------------------------------------------------
114-
* Relational operators for int8s.
114+
* Relational operators for int8s, including cross-data-type comparisons.
115115
*---------------------------------------------------------*/
116116

117117
/* int8relop()
@@ -285,6 +285,120 @@ int48ge(PG_FUNCTION_ARGS)
285285
PG_RETURN_BOOL(val1 >= val2);
286286
}
287287

288+
/* int82relop()
289+
* Is 64-bit val1 relop 16-bit val2?
290+
*/
291+
Datum
292+
int82eq(PG_FUNCTION_ARGS)
293+
{
294+
int64 val1 = PG_GETARG_INT64(0);
295+
int16 val2 = PG_GETARG_INT16(1);
296+
297+
PG_RETURN_BOOL(val1 == val2);
298+
}
299+
300+
Datum
301+
int82ne(PG_FUNCTION_ARGS)
302+
{
303+
int64 val1 = PG_GETARG_INT64(0);
304+
int16 val2 = PG_GETARG_INT16(1);
305+
306+
PG_RETURN_BOOL(val1 != val2);
307+
}
308+
309+
Datum
310+
int82lt(PG_FUNCTION_ARGS)
311+
{
312+
int64 val1 = PG_GETARG_INT64(0);
313+
int16 val2 = PG_GETARG_INT16(1);
314+
315+
PG_RETURN_BOOL(val1 < val2);
316+
}
317+
318+
Datum
319+
int82gt(PG_FUNCTION_ARGS)
320+
{
321+
int64 val1 = PG_GETARG_INT64(0);
322+
int16 val2 = PG_GETARG_INT16(1);
323+
324+
PG_RETURN_BOOL(val1 > val2);
325+
}
326+
327+
Datum
328+
int82le(PG_FUNCTION_ARGS)
329+
{
330+
int64 val1 = PG_GETARG_INT64(0);
331+
int16 val2 = PG_GETARG_INT16(1);
332+
333+
PG_RETURN_BOOL(val1 <= val2);
334+
}
335+
336+
Datum
337+
int82ge(PG_FUNCTION_ARGS)
338+
{
339+
int64 val1 = PG_GETARG_INT64(0);
340+
int16 val2 = PG_GETARG_INT16(1);
341+
342+
PG_RETURN_BOOL(val1 >= val2);
343+
}
344+
345+
/* int28relop()
346+
* Is 16-bit val1 relop 64-bit val2?
347+
*/
348+
Datum
349+
int28eq(PG_FUNCTION_ARGS)
350+
{
351+
int16 val1 = PG_GETARG_INT16(0);
352+
int64 val2 = PG_GETARG_INT64(1);
353+
354+
PG_RETURN_BOOL(val1 == val2);
355+
}
356+
357+
Datum
358+
int28ne(PG_FUNCTION_ARGS)
359+
{
360+
int16 val1 = PG_GETARG_INT16(0);
361+
int64 val2 = PG_GETARG_INT64(1);
362+
363+
PG_RETURN_BOOL(val1 != val2);
364+
}
365+
366+
Datum
367+
int28lt(PG_FUNCTION_ARGS)
368+
{
369+
int16 val1 = PG_GETARG_INT16(0);
370+
int64 val2 = PG_GETARG_INT64(1);
371+
372+
PG_RETURN_BOOL(val1 < val2);
373+
}
374+
375+
Datum
376+
int28gt(PG_FUNCTION_ARGS)
377+
{
378+
int16 val1 = PG_GETARG_INT16(0);
379+
int64 val2 = PG_GETARG_INT64(1);
380+
381+
PG_RETURN_BOOL(val1 > val2);
382+
}
383+
384+
Datum
385+
int28le(PG_FUNCTION_ARGS)
386+
{
387+
int16 val1 = PG_GETARG_INT16(0);
388+
int64 val2 = PG_GETARG_INT64(1);
389+
390+
PG_RETURN_BOOL(val1 <= val2);
391+
}
392+
393+
Datum
394+
int28ge(PG_FUNCTION_ARGS)
395+
{
396+
int16 val1 = PG_GETARG_INT16(0);
397+
int64 val2 = PG_GETARG_INT64(1);
398+
399+
PG_RETURN_BOOL(val1 >= val2);
400+
}
401+
288402

289403
/*----------------------------------------------------------
290404
* Arithmetic operators on 64-bit integers.

‎src/include/catalog/pg_operator.h

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $Id: pg_operator.h,v 1.77 2000/07/17 03:05:23 tgl Exp $
11+
* $Id: pg_operator.h,v 1.78 2000/07/28 05:07:42 tgl Exp $
1212
*
1313
* NOTES
1414
* 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
746746
DATA(insert OID = 1816 ( ">>" PGUID 0 b t f 1562 1562 1562 0 0 0 0 varbitshiftright - - ));
747747
DATA(insert OID = 1817 ( "||" PGUID 0 b t f 1562 1562 1562 0 0 0 0 varbitcat - - ));
748748

749+
DATA(insert OID = 1862 ( "=" PGUID 0 b t f 21 20 16 1868 1863 95 412 int28eq eqsel eqjoinsel ));
750+
DATA(insert OID = 1863 ( "<>" PGUID 0 b t f 21 20 16 1869 1862 0 0 int28ne neqsel neqjoinsel ));
751+
DATA(insert OID = 1864 ( "<" PGUID 0 b t f 21 20 16 1871 1867 0 0 int28lt scalarltsel scalarltjoinsel ));
752+
DATA(insert OID = 1865 ( ">" PGUID 0 b t f 21 20 16 1870 1866 0 0 int28gt scalargtsel scalargtjoinsel ));
753+
DATA(insert OID = 1866 ( "<=" PGUID 0 b t f 21 20 16 1873 1865 0 0 int28le scalarltsel scalarltjoinsel ));
754+
DATA(insert OID = 1867 ( ">=" PGUID 0 b t f 21 20 16 1872 1864 0 0 int28ge scalargtsel scalargtjoinsel ));
755+
756+
DATA(insert OID = 1868 ( "=" PGUID 0 b t f 20 21 16 1862 1869 412 95 int82eq eqsel eqjoinsel ));
757+
DATA(insert OID = 1869 ( "<>" PGUID 0 b t f 20 21 16 1863 1868 0 0 int82ne neqsel neqjoinsel ));
758+
DATA(insert OID = 1870 ( "<" PGUID 0 b t f 20 21 16 1865 1873 0 0 int82lt scalarltsel scalarltjoinsel ));
759+
DATA(insert OID = 1871 ( ">" PGUID 0 b t f 20 21 16 1864 1872 0 0 int82gt scalargtsel scalargtjoinsel ));
760+
DATA(insert OID = 1872 ( "<=" PGUID 0 b t f 20 21 16 1867 1871 0 0 int82le scalarltsel scalarltjoinsel ));
761+
DATA(insert OID = 1873 ( ">=" PGUID 0 b t f 20 21 16 1866 1870 0 0 int82ge scalargtsel scalargtjoinsel ));
762+
749763
/*
750764
* function prototypes
751765
*/

‎src/include/catalog/pg_proc.h

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: pg_proc.h,v 1.148 2000/07/17 03:05:25 tgl Exp $
10+
* $Id: pg_proc.h,v 1.149 2000/07/28 05:07:42 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2491,6 +2491,32 @@ DESCR("aggregate transition function");
24912491
DATA(insert OID = 1844 ( interval_avg PGUID 12 f t t t 1 f 1186 "1187" 100 0 0 100 interval_avg - ));
24922492
DESCR("AVG aggregate final function");
24932493

2494+
DATA(insert OID = 1850 ( int28eq PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28eq - ));
2495+
DESCR("equal");
2496+
DATA(insert OID = 1851 ( int28ne PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28ne - ));
2497+
DESCR("not equal");
2498+
DATA(insert OID = 1852 ( int28lt PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28lt - ));
2499+
DESCR("less-than");
2500+
DATA(insert OID = 1853 ( int28gt PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28gt - ));
2501+
DESCR("greater-than");
2502+
DATA(insert OID = 1854 ( int28le PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28le - ));
2503+
DESCR("less-than-or-equal");
2504+
DATA(insert OID = 1855 ( int28ge PGUID 12 f t t t 2 f 16 "21 20" 100 0 0 100 int28ge - ));
2505+
DESCR("greater-than-or-equal");
2506+
2507+
DATA(insert OID = 1856 ( int82eq PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82eq - ));
2508+
DESCR("equal");
2509+
DATA(insert OID = 1857 ( int82ne PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82ne - ));
2510+
DESCR("not equal");
2511+
DATA(insert OID = 1858 ( int82lt PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82lt - ));
2512+
DESCR("less-than");
2513+
DATA(insert OID = 1859 ( int82gt PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82gt - ));
2514+
DESCR("greater-than");
2515+
DATA(insert OID = 1860 ( int82le PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82le - ));
2516+
DESCR("less-than-or-equal");
2517+
DATA(insert OID = 1861 ( int82ge PGUID 12 f t t t 2 f 16 "20 21" 100 0 0 100 int82ge - ));
2518+
DESCR("greater-than-or-equal");
2519+
24942520

24952521
/*
24962522
* prototypes for functions pg_proc.c

‎src/include/utils/int8.h

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: int8.h,v 1.22 2000/06/13 07:35:30 tgl Exp $
10+
* $Id: int8.h,v 1.23 2000/07/28 05:07:44 tgl Exp $
1111
*
1212
* NOTES
1313
* These data types are supported on all 64-bit architectures, and may
@@ -51,6 +51,20 @@ extern Datum int48gt(PG_FUNCTION_ARGS);
5151
extern Datum int48le(PG_FUNCTION_ARGS);
5252
extern Datum int48ge(PG_FUNCTION_ARGS);
5353

54+
extern Datum int82eq(PG_FUNCTION_ARGS);
55+
extern Datum int82ne(PG_FUNCTION_ARGS);
56+
extern Datum int82lt(PG_FUNCTION_ARGS);
57+
extern Datum int82gt(PG_FUNCTION_ARGS);
58+
extern Datum int82le(PG_FUNCTION_ARGS);
59+
extern Datum int82ge(PG_FUNCTION_ARGS);
60+
61+
extern Datum int28eq(PG_FUNCTION_ARGS);
62+
extern Datum int28ne(PG_FUNCTION_ARGS);
63+
extern Datum int28lt(PG_FUNCTION_ARGS);
64+
extern Datum int28gt(PG_FUNCTION_ARGS);
65+
extern Datum int28le(PG_FUNCTION_ARGS);
66+
extern Datum int28ge(PG_FUNCTION_ARGS);
67+
5468
extern Datum int8um(PG_FUNCTION_ARGS);
5569
extern Datum int8pl(PG_FUNCTION_ARGS);
5670
extern Datum int8mi(PG_FUNCTION_ARGS);

‎src/test/regress/expected/opr_sanity.out

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -310,6 +310,23 @@ WHERE p1.oprlsortop != 0 AND NOT
310310
-----+---------
311311
(0 rows)
312312

313+
-- Mergejoinable operators across datatypes must come in closed sets, that
314+
-- is if you provide int2 = int4 and int4 = int8 then you must also provide
315+
-- int2 = int8 (and commutators of all these). This is necessary because
316+
-- the planner tries to deduce additional qual clauses from transitivity
317+
-- of mergejoinable operators. If there are clauses int2var = int4var and
318+
-- int4var = int8var, the planner will deduce int2var = int8var ... and it
319+
-- had better have a way to represent it.
320+
SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
321+
WHERE p1.oprlsortop != p1.oprrsortop AND
322+
p1.oprrsortop = p2.oprlsortop AND
323+
p2.oprlsortop != p2.oprrsortop AND
324+
NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
325+
p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
326+
oid | oid
327+
-----+-----
328+
(0 rows)
329+
313330
-- Hashing only works on simple equality operators "type = sametype",
314331
-- since the hash itself depends on the bitwise representation of the type.
315332
-- Check that allegedly hashable operators look like they might be "=".

‎src/test/regress/sql/opr_sanity.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -256,6 +256,22 @@ WHERE p1.oprlsortop != 0 AND NOT
256256
p2.oprright = p1.oprright AND
257257
p2.oprkind = 'b');
258258

259+
-- Mergejoinable operators across datatypes must come in closed sets, that
260+
-- is if you provide int2 = int4 and int4 = int8 then you must also provide
261+
-- int2 = int8 (and commutators of all these). This is necessary because
262+
-- the planner tries to deduce additional qual clauses from transitivity
263+
-- of mergejoinable operators. If there are clauses int2var = int4var and
264+
-- int4var = int8var, the planner will deduce int2var = int8var ... and it
265+
-- had better have a way to represent it.
266+
267+
SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
268+
WHERE p1.oprlsortop != p1.oprrsortop AND
269+
p1.oprrsortop = p2.oprlsortop AND
270+
p2.oprlsortop != p2.oprrsortop AND
271+
NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
272+
p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
273+
274+
259275
-- Hashing only works on simple equality operators "type = sametype",
260276
-- since the hash itself depends on the bitwise representation of the type.
261277
-- Check that allegedly hashable operators look like they might be "=".

0 commit comments

Comments
 (0)