Skip to content

Commit 851c83f

Browse files
tglsfdctglsfdc
committed
Return FALSE instead of throwing error for comparisons with empty ranges.
Change range_before, range_after, range_adjacent to return false rather than throwing an error when one or both input ranges are empty. The original definition is unnecessarily difficult to use, and also can result in undesirable planner failures since the planner could try to compare an empty range to something else while deriving statistical estimates. (This was, in fact, the cause of repeatable regression test failures on buildfarm member jaguar, as well as intermittent failures elsewhere.) Also tweak rangetypes regression test to not drop all the objects it creates, so that the final state of the regression database contains some rangetype objects for pg_dump testing.
1 parent f158536 commit 851c83f

File tree

7 files changed

+101
-103
lines changed

7 files changed

+101
-103
lines changed

‎doc/src/sgml/filelist.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@
2525
<!ENTITY mvcc SYSTEM "mvcc.sgml">
2626
<!ENTITY perform SYSTEM "perform.sgml">
2727
<!ENTITY queries SYSTEM "queries.sgml">
28-
<!entity rangetypes SYSTEM "rangetypes.sgml">
28+
<!ENTITY rangetypes SYSTEM "rangetypes.sgml">
2929
<!ENTITY rowtypes SYSTEM "rowtypes.sgml">
3030
<!ENTITY syntax SYSTEM "syntax.sgml">
3131
<!ENTITY textsearch SYSTEM "textsearch.sgml">

‎doc/src/sgml/func.sgml

Lines changed: 43 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -10460,6 +10460,10 @@ SELECT NULLIF(value, '(none)') ...
1046010460
<sect1 id="functions-range">
1046110461
<title>Range Functions and Operators</title>
1046210462

10463+
<para>
10464+
See <xref linkend="rangetypes"> for an overview of range types.
10465+
</para>
10466+
1046310467
<para>
1046410468
<xref linkend="range-operators-table"> shows the operators
1046510469
available for range types.
@@ -10556,65 +10560,74 @@ SELECT NULLIF(value, '(none)') ...
1055610560

1055710561
<row>
1055810562
<entry> <literal>&amp;&lt;</literal> </entry>
10559-
<entry>Does not extend to the right of?</entry>
10563+
<entry>does not extend to the right of</entry>
1056010564
<entry><literal>int8range(1,20) &amp;&lt; int8range(18,20)</literal></entry>
1056110565
<entry><literal>t</literal></entry>
1056210566
</row>
1056310567

1056410568
<row>
1056510569
<entry> <literal>&amp;&gt;</literal> </entry>
10566-
<entry>Does not extend to the left of?</entry>
10570+
<entry>does not extend to the left of</entry>
1056710571
<entry><literal>int8range(7,20) &amp;&gt; int8range(5,10)</literal></entry>
1056810572
<entry><literal>t</literal></entry>
1056910573
</row>
1057010574

1057110575
<row>
1057210576
<entry> <literal>-|-</literal> </entry>
10573-
<entry>adjacent?</entry>
10577+
<entry>is adjacent to</entry>
1057410578
<entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry>
1057510579
<entry><literal>t</literal></entry>
1057610580
</row>
1057710581

1057810582
<row>
1057910583
<entry> <literal>+</literal> </entry>
10580-
<entry>Union</entry>
10584+
<entry>union</entry>
1058110585
<entry><literal>numrange(5,15) + numrange(10,20)</literal></entry>
1058210586
<entry><literal>[5,20)</literal></entry>
1058310587
</row>
1058410588

10585-
<row>
10586-
<entry> <literal>-</literal> </entry>
10587-
<entry>Difference</entry>
10588-
<entry><literal>int8range(5,15) - int8range(10,20)</literal></entry>
10589-
<entry><literal>[5,10)</literal></entry>
10590-
</row>
10591-
1059210589
<row>
1059310590
<entry> <literal>*</literal> </entry>
10594-
<entry>Intersection</entry>
10591+
<entry>intersection</entry>
1059510592
<entry><literal>int8range(5,15) * int8range(10,20)</literal></entry>
1059610593
<entry><literal>[10,15)</literal></entry>
1059710594
</row>
1059810595

10596+
<row>
10597+
<entry> <literal>-</literal> </entry>
10598+
<entry>difference</entry>
10599+
<entry><literal>int8range(5,15) - int8range(10,20)</literal></entry>
10600+
<entry><literal>[5,10)</literal></entry>
10601+
</row>
10602+
1059910603
</tbody>
1060010604
</tgroup>
1060110605
</table>
1060210606

1060310607
<para>
10604-
Range comparisons compare the lower bounds first, and only if
10605-
equal, compare the upper bounds. This is generally most useful for
10606-
B-tree indexes, rather than being useful comparisons by themselves.
10608+
The simple comparison operators <literal>&lt;</literal>,
10609+
<literal>&gt;</literal>, <literal>&lt;=</literal>, and
10610+
<literal>&gt;=</literal> compare the lower bounds first, and only if those
10611+
are equal, compare the upper bounds. These comparisons are not usually
10612+
very useful for ranges, but are provided to allow B-tree indexes to be
10613+
constructed on ranges.
1060710614
</para>
1060810615

1060910616
<para>
10610-
See <xref linkend="rangetypes"> for more details about range operator
10611-
behavior.
10617+
The left-of/right-of/adjacent operators always return false when an empty
10618+
range is involved; that is, an empty range is not considered to be either
10619+
before or after any other range.
10620+
</para>
10621+
10622+
<para>
10623+
The union and difference operators will fail if the resulting range would
10624+
need to contain two disjoint sub-ranges, as such a range cannot be
10625+
represented.
1061210626
</para>
1061310627

1061410628
<para>
1061510629
<xref linkend="range-functions-table"> shows the functions
10616-
available for use with range types. See <xref linkend="rangetypes">
10617-
for more information and examples of the use of these functions.
10630+
available for use with range types.
1061810631
</para>
1061910632

1062010633
<indexterm>
@@ -10658,7 +10671,7 @@ SELECT NULLIF(value, '(none)') ...
1065810671
<function>lower</function>(<type>anyrange</type>)
1065910672
</literal>
1066010673
</entry>
10661-
<entry><type>anyrange</type></entry>
10674+
<entry>range's element type</entry>
1066210675
<entry>lower bound of range</entry>
1066310676
<entry><literal>lower(numrange(1.1,2.2))</literal></entry>
1066410677
<entry><literal>1.1</literal></entry>
@@ -10669,7 +10682,7 @@ SELECT NULLIF(value, '(none)') ...
1066910682
<function>upper</function>(<type>anyrange</type>)
1067010683
</literal>
1067110684
</entry>
10672-
<entry><type>anyrange</type></entry>
10685+
<entry>range's element type</entry>
1067310686
<entry>upper bound of range</entry>
1067410687
<entry><literal>upper(numrange(1.1,2.2))</literal></entry>
1067510688
<entry><literal>2.2</literal></entry>
@@ -10680,7 +10693,7 @@ SELECT NULLIF(value, '(none)') ...
1068010693
<function>isempty</function>(<type>anyrange</type>)
1068110694
</literal>
1068210695
</entry>
10683-
<entry><type>anyrange</type></entry>
10696+
<entry><type>boolean</type></entry>
1068410697
<entry>is the range empty?</entry>
1068510698
<entry><literal>isempty(numrange(1.1,2.2))</literal></entry>
1068610699
<entry><literal>false</literal></entry>
@@ -10691,8 +10704,8 @@ SELECT NULLIF(value, '(none)') ...
1069110704
<function>lower_inc</function>(<type>anyrange</type>)
1069210705
</literal>
1069310706
</entry>
10694-
<entry><type>anyrange</type></entry>
10695-
<entry>is the lower bound of the range inclusive?</entry>
10707+
<entry><type>boolean</type></entry>
10708+
<entry>is the lower bound inclusive?</entry>
1069610709
<entry><literal>lower_inc(numrange(1.1,2.2))</literal></entry>
1069710710
<entry><literal>true</literal></entry>
1069810711
</row>
@@ -10702,8 +10715,8 @@ SELECT NULLIF(value, '(none)') ...
1070210715
<function>upper_inc</function>(<type>anyrange</type>)
1070310716
</literal>
1070410717
</entry>
10705-
<entry><type>anyrange</type></entry>
10706-
<entry>is the upper bound of the range inclusive?</entry>
10718+
<entry><type>boolean</type></entry>
10719+
<entry>is the upper bound inclusive?</entry>
1070710720
<entry><literal>upper_inc(numrange(1.1,2.2))</literal></entry>
1070810721
<entry><literal>false</literal></entry>
1070910722
</row>
@@ -10713,8 +10726,8 @@ SELECT NULLIF(value, '(none)') ...
1071310726
<function>lower_inf</function>(<type>anyrange</type>)
1071410727
</literal>
1071510728
</entry>
10716-
<entry><type>anyrange</type></entry>
10717-
<entry>is the lower bound of the range infinite?</entry>
10729+
<entry><type>boolean</type></entry>
10730+
<entry>is the lower bound infinite?</entry>
1071810731
<entry><literal>lower_inf('(,)'::daterange)</literal></entry>
1071910732
<entry><literal>true</literal></entry>
1072010733
</row>
@@ -10724,8 +10737,8 @@ SELECT NULLIF(value, '(none)') ...
1072410737
<function>upper_inf</function>(<type>anyrange</type>)
1072510738
</literal>
1072610739
</entry>
10727-
<entry><type>anyrange</type></entry>
10728-
<entry>is the upper bound of the range infinite?</entry>
10740+
<entry><type>boolean</type></entry>
10741+
<entry>is the upper bound infinite?</entry>
1072910742
<entry><literal>upper_inf('(,)'::daterange)</literal></entry>
1073010743
<entry><literal>true</literal></entry>
1073110744
</row>

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

Lines changed: 14 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -701,15 +701,11 @@ range_before(PG_FUNCTION_ARGS)
701701
lower1.rngtypid != upper2.rngtypid)
702702
elog(ERROR, "range types do not match");
703703

704+
/* An empty range is neither before nor after any other range */
704705
if (empty1 || empty2)
705-
ereport(ERROR,
706-
(errcode(ERRCODE_DATA_EXCEPTION),
707-
errmsg("input range is empty")));
708-
709-
if (range_cmp_bounds(fcinfo, &upper1, &lower2) < 0)
710-
PG_RETURN_BOOL(true);
711-
else
712706
PG_RETURN_BOOL(false);
707+
708+
PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &upper1, &lower2) < 0);
713709
}
714710

715711
Datum
@@ -732,15 +728,11 @@ range_after(PG_FUNCTION_ARGS)
732728
lower1.rngtypid != upper2.rngtypid)
733729
elog(ERROR, "range types do not match");
734730

731+
/* An empty range is neither before nor after any other range */
735732
if (empty1 || empty2)
736-
ereport(ERROR,
737-
(errcode(ERRCODE_DATA_EXCEPTION),
738-
errmsg("input range is empty")));
739-
740-
if (range_cmp_bounds(fcinfo, &lower1, &upper2) > 0)
741-
PG_RETURN_BOOL(true);
742-
else
743733
PG_RETURN_BOOL(false);
734+
735+
PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &lower1, &upper2) > 0);
744736
}
745737

746738
Datum
@@ -764,10 +756,9 @@ range_adjacent(PG_FUNCTION_ARGS)
764756
lower1.rngtypid != upper2.rngtypid)
765757
elog(ERROR, "range types do not match");
766758

759+
/* An empty range is not adjacent to any other range */
767760
if (empty1 || empty2)
768-
ereport(ERROR,
769-
(errcode(ERRCODE_DATA_EXCEPTION),
770-
errmsg("undefined for empty ranges")));
761+
PG_RETURN_BOOL(false);
771762

772763
/*
773764
* For two ranges to be adjacent, the lower boundary of one range has to
@@ -819,6 +810,7 @@ range_overlaps(PG_FUNCTION_ARGS)
819810
lower1.rngtypid != upper2.rngtypid)
820811
elog(ERROR, "range types do not match");
821812

813+
/* An empty range does not overlap any other range */
822814
if (empty1 || empty2)
823815
PG_RETURN_BOOL(false);
824816

@@ -853,6 +845,7 @@ range_overleft(PG_FUNCTION_ARGS)
853845
lower1.rngtypid != upper2.rngtypid)
854846
elog(ERROR, "range types do not match");
855847

848+
/* An empty range is neither before nor after any other range */
856849
if (empty1 || empty2)
857850
PG_RETURN_BOOL(false);
858851

@@ -882,6 +875,7 @@ range_overright(PG_FUNCTION_ARGS)
882875
lower1.rngtypid != upper2.rngtypid)
883876
elog(ERROR, "range types do not match");
884877

878+
/* An empty range is neither before nor after any other range */
885879
if (empty1 || empty2)
886880
PG_RETURN_BOOL(false);
887881

@@ -917,6 +911,7 @@ range_minus(PG_FUNCTION_ARGS)
917911
lower1.rngtypid != upper2.rngtypid)
918912
elog(ERROR, "range types do not match");
919913

914+
/* if either is empty, r1 is the correct answer */
920915
if (empty1 || empty2)
921916
PG_RETURN_RANGE(r1);
922917

@@ -971,6 +966,7 @@ range_union(PG_FUNCTION_ARGS)
971966
range_deserialize(fcinfo, r1, &lower1, &upper1, &empty1);
972967
range_deserialize(fcinfo, r2, &lower2, &upper2, &empty2);
973968

969+
/* if either is empty, the other is the correct answer */
974970
if (empty1)
975971
PG_RETURN_RANGE(r2);
976972
if (empty2)
@@ -1051,6 +1047,7 @@ range_cmp(PG_FUNCTION_ARGS)
10511047
lower1.rngtypid != upper2.rngtypid)
10521048
elog(ERROR, "range types do not match");
10531049

1050+
/* For b-tree use, empty ranges sort before all else */
10541051
if (empty1 && empty2)
10551052
PG_RETURN_INT32(0);
10561053
else if (empty1)

0 commit comments

Comments
 (0)