Prevent internal overflows in date-vs-timestamp and related comparisons.

The date-vs-timestamp, date-vs-timestamptz, and timestamp-vs-timestamptz
comparators all worked by promoting the first type to the second and
then doing a simple same-type comparison.  This works fine, except
when the conversion result is out of range, in which case we throw an
entirely avoidable error.  The sources of such failures are
(a) type date can represent dates much farther in the future than
the timestamp types can;
(b) timezone rotation might cause a just-in-range timestamp value to
become a just-out-of-range timestamptz value.

Up to now we just ignored these corner-case issues, but now we have
an actual user complaint (bug #16657 from Huss EL-Sheikh), so let's
do something about it.

It turns out that commit 52ad1e659 already built all the necessary
infrastructure to support error-free comparisons, but neglected to
actually use it in the main-line code paths.  Fix that, do a little
bit of code style review, and remove the now-duplicate logic in
jsonpath_exec.c.

Back-patch to v13 where 52ad1e659 came in.  We could take this back
further by back-patching said infrastructure, but given the small
number of complaints so far, I don't feel a great need to.

Discussion: https://postgr.es/m/16657-cde2f876d8cc7971@postgresql.org

1 files changed, 97 insertions, 125 deletions

diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 057051fa855..a470cf890a2 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -554,15 +554,24 @@ date_mii(PG_FUNCTION_ARGS)
 /*
  * Promote date to timestamp.
  *
- * On overflow error is thrown if 'overflow' is NULL.  Otherwise, '*overflow'
- * is set to -1 (+1) when result value exceed lower (upper) boundary and zero
- * returned.
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the date is finite but out of the valid range for timestamp, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamp infinity.
+ *
+ * Note: *overflow = -1 is actually not possible currently, since both
+ * datatypes have the same lower bound, Julian day zero.
  */
 Timestamp
 date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
 {
 	Timestamp	result;
 
+	if (overflow)
+		*overflow = 0;
+
 	if (DATE_IS_NOBEGIN(dateVal))
 		TIMESTAMP_NOBEGIN(result);
 	else if (DATE_IS_NOEND(dateVal))
@@ -570,7 +579,6 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
 	else
 	{
 		/*
-		 * Date's range is wider than timestamp's, so check for boundaries.
 		 * Since dates have the same minimum values as timestamps, only upper
 		 * boundary need be checked for overflow.
 		 */
@@ -579,7 +587,8 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
 			if (overflow)
 			{
 				*overflow = 1;
-				return (Timestamp) 0;
+				TIMESTAMP_NOEND(result);
+				return result;
 			}
 			else
 			{
@@ -597,7 +606,7 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
 }
 
 /*
- * Single-argument version of date2timestamp_opt_overflow().
+ * Promote date to timestamp, throwing error for overflow.
  */
 static TimestampTz
 date2timestamp(DateADT dateVal)
@@ -608,9 +617,12 @@ date2timestamp(DateADT dateVal)
 /*
  * Promote date to timestamp with time zone.
  *
- * On overflow error is thrown if 'overflow' is NULL.  Otherwise, '*overflow'
- * is set to -1 (+1) when result value exceed lower (upper) boundary and zero
- * returned.
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the date is finite but out of the valid range for timestamptz, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamptz infinity.
  */
 TimestampTz
 date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
@@ -620,6 +632,9 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
 			   *tm = &tt;
 	int			tz;
 
+	if (overflow)
+		*overflow = 0;
+
 	if (DATE_IS_NOBEGIN(dateVal))
 		TIMESTAMP_NOBEGIN(result);
 	else if (DATE_IS_NOEND(dateVal))
@@ -627,7 +642,6 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
 	else
 	{
 		/*
-		 * Date's range is wider than timestamp's, so check for boundaries.
 		 * Since dates have the same minimum values as timestamps, only upper
 		 * boundary need be checked for overflow.
 		 */
@@ -636,7 +650,8 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
 			if (overflow)
 			{
 				*overflow = 1;
-				return (TimestampTz) 0;
+				TIMESTAMP_NOEND(result);
+				return result;
 			}
 			else
 			{
@@ -664,13 +679,15 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
 			if (overflow)
 			{
 				if (result < MIN_TIMESTAMP)
+				{
 					*overflow = -1;
+					TIMESTAMP_NOBEGIN(result);
+				}
 				else
 				{
-					Assert(result >= END_TIMESTAMP);
 					*overflow = 1;
+					TIMESTAMP_NOEND(result);
 				}
-				return (TimestampTz) 0;
 			}
 			else
 			{
@@ -685,7 +702,7 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
 }
 
 /*
- * Single-argument version of date2timestamptz_opt_overflow().
+ * Promote date to timestamptz, throwing error for overflow.
  */
 static TimestampTz
 date2timestamptz(DateADT dateVal)
@@ -726,16 +743,30 @@ date2timestamp_no_overflow(DateADT dateVal)
  * Crosstype comparison functions for dates
  */
 
+int32
+date_cmp_timestamp_internal(DateADT dateVal, Timestamp dt2)
+{
+	Timestamp	dt1;
+	int			overflow;
+
+	dt1 = date2timestamp_opt_overflow(dateVal, &overflow);
+	if (overflow > 0)
+	{
+		/* dt1 is larger than any finite timestamp, but less than infinity */
+		return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
+	}
+	Assert(overflow == 0);		/* -1 case cannot occur */
+
+	return timestamp_cmp_internal(dt1, dt2);
+}
+
 Datum
 date_eq_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
-
-	dt1 = date2timestamp(dateVal);
 
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) == 0);
 }
 
 Datum
@@ -743,11 +774,8 @@ date_ne_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) != 0);
 }
 
 Datum
@@ -755,11 +783,8 @@ date_lt_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) < 0);
 }
 
 Datum
@@ -767,11 +792,8 @@ date_gt_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) > 0);
 }
 
 Datum
@@ -779,11 +801,8 @@ date_le_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) <= 0);
 }
 
 Datum
@@ -791,11 +810,8 @@ date_ge_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) >= 0);
 }
 
 Datum
@@ -803,11 +819,29 @@ date_cmp_timestamp(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
-	Timestamp	dt1;
 
-	dt1 = date2timestamp(dateVal);
+	PG_RETURN_INT32(date_cmp_timestamp_internal(dateVal, dt2));
+}
 
-	PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+int32
+date_cmp_timestamptz_internal(DateADT dateVal, TimestampTz dt2)
+{
+	TimestampTz dt1;
+	int			overflow;
+
+	dt1 = date2timestamptz_opt_overflow(dateVal, &overflow);
+	if (overflow > 0)
+	{
+		/* dt1 is larger than any finite timestamp, but less than infinity */
+		return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
+	}
+	if (overflow < 0)
+	{
+		/* dt1 is less than any finite timestamp, but more than -infinity */
+		return TIMESTAMP_IS_NOBEGIN(dt2) ? +1 : -1;
+	}
+
+	return timestamptz_cmp_internal(dt1, dt2);
 }
 
 Datum
@@ -815,11 +849,8 @@ date_eq_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) == 0);
 }
 
 Datum
@@ -827,11 +858,8 @@ date_ne_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) != 0);
 }
 
 Datum
@@ -839,11 +867,8 @@ date_lt_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) < 0);
 }
 
 Datum
@@ -851,11 +876,8 @@ date_gt_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) > 0);
 }
 
 Datum
@@ -863,11 +885,8 @@ date_le_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) <= 0);
 }
 
 Datum
@@ -875,11 +894,8 @@ date_ge_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
 
-	dt1 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) >= 0);
 }
 
 Datum
@@ -887,11 +903,8 @@ date_cmp_timestamptz(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
-	TimestampTz dt1;
-
-	dt1 = date2timestamptz(dateVal);
 
-	PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
+	PG_RETURN_INT32(date_cmp_timestamptz_internal(dateVal, dt2));
 }
 
 Datum
@@ -899,11 +912,8 @@ timestamp_eq_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
 
-	dt2 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) == 0);
 }
 
 Datum
@@ -911,11 +921,8 @@ timestamp_ne_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
-
-	dt2 = date2timestamp(dateVal);
 
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) != 0);
 }
 
 Datum
@@ -923,11 +930,8 @@ timestamp_lt_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
 
-	dt2 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) > 0);
 }
 
 Datum
@@ -935,11 +939,8 @@ timestamp_gt_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
-
-	dt2 = date2timestamp(dateVal);
 
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) < 0);
 }
 
 Datum
@@ -947,11 +948,8 @@ timestamp_le_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
 
-	dt2 = date2timestamp(dateVal);
-
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) >= 0);
 }
 
 Datum
@@ -959,11 +957,8 @@ timestamp_ge_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
-
-	dt2 = date2timestamp(dateVal);
 
-	PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+	PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) <= 0);
 }
 
 Datum
@@ -971,11 +966,8 @@ timestamp_cmp_date(PG_FUNCTION_ARGS)
 {
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	Timestamp	dt2;
 
-	dt2 = date2timestamp(dateVal);
-
-	PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+	PG_RETURN_INT32(-date_cmp_timestamp_internal(dateVal, dt1));
 }
 
 Datum
@@ -983,11 +975,8 @@ timestamptz_eq_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
-
-	dt2 = date2timestamptz(dateVal);
 
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) == 0);
 }
 
 Datum
@@ -995,11 +984,8 @@ timestamptz_ne_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
 
-	dt2 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) != 0);
 }
 
 Datum
@@ -1007,11 +993,8 @@ timestamptz_lt_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
-
-	dt2 = date2timestamptz(dateVal);
 
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) > 0);
 }
 
 Datum
@@ -1019,11 +1002,8 @@ timestamptz_gt_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
 
-	dt2 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) < 0);
 }
 
 Datum
@@ -1031,11 +1011,8 @@ timestamptz_le_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
-
-	dt2 = date2timestamptz(dateVal);
 
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) >= 0);
 }
 
 Datum
@@ -1043,11 +1020,8 @@ timestamptz_ge_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
 
-	dt2 = date2timestamptz(dateVal);
-
-	PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
+	PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) <= 0);
 }
 
 Datum
@@ -1055,11 +1029,8 @@ timestamptz_cmp_date(PG_FUNCTION_ARGS)
 {
 	TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
 	DateADT		dateVal = PG_GETARG_DATEADT(1);
-	TimestampTz dt2;
-
-	dt2 = date2timestamptz(dateVal);
 
-	PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
+	PG_RETURN_INT32(-date_cmp_timestamptz_internal(dateVal, dt1));
 }
 
 /*
@@ -1079,6 +1050,7 @@ in_range_date_interval(PG_FUNCTION_ARGS)
 	Timestamp	valStamp;
 	Timestamp	baseStamp;
 
+	/* XXX we could support out-of-range cases here, perhaps */
 	valStamp = date2timestamp(val);
 	baseStamp = date2timestamp(base);