aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/date.c40
-rw-r--r--src/backend/utils/adt/jsonpath_exec.c262
-rw-r--r--src/backend/utils/adt/timestamp.c31
-rw-r--r--src/include/utils/date.h4
-rw-r--r--src/include/utils/timestamp.h4
-rw-r--r--src/test/regress/expected/jsonb_jsonpath.out37
-rw-r--r--src/test/regress/sql/jsonb_jsonpath.sql3
7 files changed, 237 insertions, 144 deletions
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index fa50d79c05d..709bbaaf5a0 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -554,11 +554,12 @@ date_mii(PG_FUNCTION_ARGS)
/*
* Promote date to timestamp.
*
- * If 'have_error' is NULL, then errors are thrown, else '*have_error' is set
- * and zero is returned.
+ * 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.
*/
Timestamp
-date2timestamp_opt_error(DateADT dateVal, bool *have_error)
+date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
{
Timestamp result;
@@ -575,9 +576,9 @@ date2timestamp_opt_error(DateADT dateVal, bool *have_error)
*/
if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
{
- if (have_error)
+ if (overflow)
{
- *have_error = true;
+ *overflow = 1;
return (Timestamp) 0;
}
else
@@ -596,22 +597,23 @@ date2timestamp_opt_error(DateADT dateVal, bool *have_error)
}
/*
- * Single-argument version of date2timestamp_opt_error().
+ * Single-argument version of date2timestamp_opt_overflow().
*/
static TimestampTz
date2timestamp(DateADT dateVal)
{
- return date2timestamp_opt_error(dateVal, NULL);
+ return date2timestamp_opt_overflow(dateVal, NULL);
}
/*
* Promote date to timestamp with time zone.
*
- * If 'have_error' is NULL, then errors are thrown, else '*have_error' is set
- * and zero is returned.
+ * 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.
*/
TimestampTz
-date2timestamptz_opt_error(DateADT dateVal, bool *have_error)
+date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
{
TimestampTz result;
struct pg_tm tt,
@@ -631,9 +633,9 @@ date2timestamptz_opt_error(DateADT dateVal, bool *have_error)
*/
if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
{
- if (have_error)
+ if (overflow)
{
- *have_error = true;
+ *overflow = 1;
return (TimestampTz) 0;
}
else
@@ -659,9 +661,15 @@ date2timestamptz_opt_error(DateADT dateVal, bool *have_error)
*/
if (!IS_VALID_TIMESTAMP(result))
{
- if (have_error)
+ if (overflow)
{
- *have_error = true;
+ if (result < MIN_TIMESTAMP)
+ *overflow = -1;
+ else
+ {
+ Assert(result >= END_TIMESTAMP);
+ *overflow = 1;
+ }
return (TimestampTz) 0;
}
else
@@ -677,12 +685,12 @@ date2timestamptz_opt_error(DateADT dateVal, bool *have_error)
}
/*
- * Single-argument version of date2timestamptz_opt_error().
+ * Single-argument version of date2timestamptz_opt_overflow().
*/
static TimestampTz
date2timestamptz(DateADT dateVal)
{
- return date2timestamptz_opt_error(dateVal, NULL);
+ return date2timestamptz_opt_overflow(dateVal, NULL);
}
/*
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index a35f718b96e..e2c1bfb5a77 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -2298,16 +2298,16 @@ compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2, bool useTz)
break;
case jbvDatetime:
{
- bool have_error = false;
+ bool cast_error;
cmp = compareDatetime(jb1->val.datetime.value,
jb1->val.datetime.typid,
jb2->val.datetime.value,
jb2->val.datetime.typid,
useTz,
- &have_error);
+ &cast_error);
- if (have_error)
+ if (cast_error)
return jpbUnknown;
}
break;
@@ -2571,15 +2571,128 @@ wrapItemsInArray(const JsonValueList *items)
return pushJsonbValue(&ps, WJB_END_ARRAY, NULL);
}
+/* Check if the timezone required for casting from type1 to type2 is used */
+static void
+checkTimezoneIsUsedForCast(bool useTz, const char *type1, const char *type2)
+{
+ if (!useTz)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert value from %s to %s without timezone usage",
+ type1, type2),
+ errhint("Use *_tz() function for timezone support.")));
+}
+
+/* Convert time datum to timetz datum */
+static Datum
+castTimeToTimeTz(Datum time, bool useTz)
+{
+ checkTimezoneIsUsedForCast(useTz, "time", "timetz");
+
+ return DirectFunctionCall1(time_timetz, time);
+}
+
+/*---
+ * Compares 'ts1' and 'ts2' timestamp, assuming that ts1 might be overflowed
+ * during cast from another datatype.
+ *
+ * 'overflow1' specifies overflow of 'ts1' value:
+ * 0 - no overflow,
+ * -1 - exceed lower boundary,
+ * 1 - exceed upper boundary.
+ */
+static int
+cmpTimestampWithOverflow(Timestamp ts1, int overflow1, Timestamp ts2)
+{
+ /*
+ * All the timestamps we deal with in jsonpath are produced by
+ * to_datetime() method. So, they should be valid.
+ */
+ Assert(IS_VALID_TIMESTAMP(ts2));
+
+ /*
+ * Timestamp, which exceed lower (upper) bound, is always lower (higher)
+ * than any valid timestamp except minus (plus) infinity.
+ */
+ if (overflow1)
+ {
+ if (overflow1 < 0)
+ {
+ if (TIMESTAMP_IS_NOBEGIN(ts2))
+ return 1;
+ else
+ return -1;
+ }
+ if (overflow1 > 0)
+ {
+ if (TIMESTAMP_IS_NOEND(ts2))
+ return -1;
+ else
+ return 1;
+ }
+ }
+
+ return timestamp_cmp_internal(ts1, ts2);
+}
+
+/*
+ * Compare date to timestamptz without throwing overflow error during cast.
+ */
+static int
+cmpDateToTimestamp(DateADT date1, Timestamp ts2, bool useTz)
+{
+ TimestampTz ts1;
+ int overflow = 0;
+
+ ts1 = date2timestamp_opt_overflow(date1, &overflow);
+
+ return cmpTimestampWithOverflow(ts1, overflow, ts2);
+}
+
+/*
+ * Compare date to timestamptz without throwing overflow error during cast.
+ */
+static int
+cmpDateToTimestampTz(DateADT date1, TimestampTz tstz2, bool useTz)
+{
+ TimestampTz tstz1;
+ int overflow = 0;
+
+ checkTimezoneIsUsedForCast(useTz, "date", "timestamptz");
+
+ tstz1 = date2timestamptz_opt_overflow(date1, &overflow);
+
+ return cmpTimestampWithOverflow(tstz1, overflow, tstz2);
+}
+
+/*
+ * Compare timestamp to timestamptz without throwing overflow error during cast.
+ */
+static int
+cmpTimestampToTimestampTz(Timestamp ts1, TimestampTz tstz2, bool useTz)
+{
+ TimestampTz tstz1;
+ int overflow = 0;
+
+ checkTimezoneIsUsedForCast(useTz, "timestamp", "timestamptz");
+
+ tstz1 = timestamp2timestamptz_opt_overflow(ts1, &overflow);
+
+ return cmpTimestampWithOverflow(tstz1, overflow, tstz2);
+}
+
/*
* Cross-type comparison of two datetime SQL/JSON items. If items are
- * uncomparable, 'error' flag is set.
+ * uncomparable *cast_error flag is set, otherwise *cast_error is unset.
+ * If the cast requires timezone and it is not used, then explicit error is thrown.
*/
static int
compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
- bool useTz, bool *have_error)
+ bool useTz, bool *cast_error)
{
- PGFunction cmpfunc = NULL;
+ PGFunction cmpfunc;
+
+ *cast_error = false;
switch (typid1)
{
@@ -2592,31 +2705,23 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
break;
case TIMESTAMPOID:
- val1 = TimestampGetDatum(date2timestamp_opt_error(DatumGetDateADT(val1), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return cmpDateToTimestamp(DatumGetDateADT(val1),
+ DatumGetTimestamp(val2),
+ useTz);
case TIMESTAMPTZOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "date", "timestamptz"),
- errhint("use *_tz() function for timezone support")));
- val1 = TimestampTzGetDatum(date2timestamptz_opt_error(DatumGetDateADT(val1), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return cmpDateToTimestampTz(DatumGetDateADT(val1),
+ DatumGetTimestampTz(val2),
+ useTz);
case TIMEOID:
case TIMETZOID:
- *have_error = true;
+ *cast_error = true; /* uncomparable types */
return 0;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
+ typid2);
}
break;
@@ -2629,13 +2734,7 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
break;
case TIMETZOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "time", "timetz"),
- errhint("use *_tz() function for timezone support")));
- val1 = DirectFunctionCall1(time_timetz, val1);
+ val1 = castTimeToTimeTz(val1, useTz);
cmpfunc = timetz_cmp;
break;
@@ -2643,8 +2742,12 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
case DATEOID:
case TIMESTAMPOID:
case TIMESTAMPTZOID:
- *have_error = true;
+ *cast_error = true; /* uncomparable types */
return 0;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
+ typid2);
}
break;
@@ -2652,13 +2755,7 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
switch (typid2)
{
case TIMEOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "time", "timetz"),
- errhint("use *_tz() function for timezone support")));
- val2 = DirectFunctionCall1(time_timetz, val2);
+ val2 = castTimeToTimeTz(val2, useTz);
cmpfunc = timetz_cmp;
break;
@@ -2671,8 +2768,12 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
case DATEOID:
case TIMESTAMPOID:
case TIMESTAMPTZOID:
- *have_error = true;
+ *cast_error = true; /* uncomparable types */
return 0;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
+ typid2);
}
break;
@@ -2680,12 +2781,9 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
switch (typid2)
{
case DATEOID:
- val2 = TimestampGetDatum(date2timestamp_opt_error(DatumGetDateADT(val2), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return -cmpDateToTimestamp(DatumGetDateADT(val2),
+ DatumGetTimestamp(val1),
+ useTz);
case TIMESTAMPOID:
cmpfunc = timestamp_cmp;
@@ -2693,23 +2791,18 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
break;
case TIMESTAMPTZOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "timestamp", "timestamptz"),
- errhint("use *_tz() function for timezone support")));
- val1 = TimestampTzGetDatum(timestamp2timestamptz_opt_error(DatumGetTimestamp(val1), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return cmpTimestampToTimestampTz(DatumGetTimestamp(val1),
+ DatumGetTimestampTz(val2),
+ useTz);
case TIMEOID:
case TIMETZOID:
- *have_error = true;
+ *cast_error = true; /* uncomparable types */
return 0;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
+ typid2);
}
break;
@@ -2717,32 +2810,14 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
switch (typid2)
{
case DATEOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "date", "timestamptz"),
- errhint("use *_tz() function for timezone support")));
- val2 = TimestampTzGetDatum(date2timestamptz_opt_error(DatumGetDateADT(val2), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return -cmpDateToTimestampTz(DatumGetDateADT(val2),
+ DatumGetTimestampTz(val1),
+ useTz);
case TIMESTAMPOID:
- if (!useTz)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert value from %s to %s without timezone usage",
- "timestamp", "timestamptz"),
- errhint("use *_tz() function for timezone support")));
- val2 = TimestampTzGetDatum(timestamp2timestamptz_opt_error(DatumGetTimestamp(val2), have_error));
- if (have_error && *have_error)
- return 0;
- cmpfunc = timestamp_cmp;
-
- break;
+ return -cmpTimestampToTimestampTz(DatumGetTimestamp(val2),
+ DatumGetTimestampTz(val1),
+ useTz);
case TIMESTAMPTZOID:
cmpfunc = timestamp_cmp;
@@ -2751,24 +2826,21 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
case TIMEOID:
case TIMETZOID:
- *have_error = true;
+ *cast_error = true; /* uncomparable types */
return 0;
+
+ default:
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
+ typid2);
}
break;
default:
- elog(ERROR, "unrecognized SQL/JSON datetime type oid: %d",
- typid1);
+ elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u", typid1);
}
- if (*have_error)
- return 0;
-
- if (!cmpfunc)
- elog(ERROR, "unrecognized SQL/JSON datetime type oid: %d",
- typid2);
-
- *have_error = false;
+ if (*cast_error)
+ return 0; /* cast error */
return DatumGetInt32(DirectFunctionCall2(cmpfunc, val1, val2));
}
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 90ebb50e1f5..1dc4c820de2 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5190,12 +5190,12 @@ timestamp_timestamptz(PG_FUNCTION_ARGS)
/*
* Convert timestamp to timestamp with time zone.
*
- * If 'have_error' is NULL, then errors are thrown, else '*have_error' is set
- * and zero is returned.
+ * 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.
*/
-
TimestampTz
-timestamp2timestamptz_opt_error(Timestamp timestamp, bool *have_error)
+timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
{
TimestampTz result;
struct pg_tm tt,
@@ -5216,30 +5216,33 @@ timestamp2timestamptz_opt_error(Timestamp timestamp, bool *have_error)
{
return result;
}
- else if (have_error)
+ else if (overflow)
{
- *have_error = true;
+ if (result < MIN_TIMESTAMP)
+ *overflow = -1;
+ else
+ {
+ Assert(result >= END_TIMESTAMP);
+ *overflow = 1;
+ }
return (TimestampTz) 0;
}
}
- if (have_error)
- *have_error = true;
- else
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of range")));
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
return 0;
}
/*
- * Single-argument version of timestamp2timestamptz_opt_error().
+ * Single-argument version of timestamp2timestamptz_opt_overflow().
*/
static TimestampTz
timestamp2timestamptz(Timestamp timestamp)
{
- return timestamp2timestamptz_opt_error(timestamp, NULL);
+ return timestamp2timestamptz_opt_overflow(timestamp, NULL);
}
/* timestamptz_timestamp()
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index c29f13aaf04..7352b1f4fec 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -70,8 +70,8 @@ typedef struct
/* date.c */
extern int32 anytime_typmod_check(bool istz, int32 typmod);
extern double date2timestamp_no_overflow(DateADT dateVal);
-extern Timestamp date2timestamp_opt_error(DateADT dateVal, bool *have_error);
-extern TimestampTz date2timestamptz_opt_error(DateADT dateVal, bool *have_error);
+extern Timestamp date2timestamp_opt_overflow(DateADT dateVal, int *overflow);
+extern TimestampTz date2timestamptz_opt_overflow(DateADT dateVal, int *overflow);
extern void EncodeSpecialDate(DateADT dt, char *str);
extern DateADT GetSQLCurrentDate(void);
extern TimeTzADT *GetSQLCurrentTime(int32 typmod);
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index e884d4405f5..7652b41eaec 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -97,8 +97,8 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
/* timestamp comparison works for timestamptz also */
#define timestamptz_cmp_internal(dt1,dt2) timestamp_cmp_internal(dt1, dt2)
-extern TimestampTz timestamp2timestamptz_opt_error(Timestamp timestamp,
- bool *have_error);
+extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
+ int *overflow);
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 063f1c27711..ef8db2d060a 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1949,17 +1949,17 @@ select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR: cannot convert value from date to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR: cannot convert value from date to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
'$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR: cannot convert value from date to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query_tz(
'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
@@ -1996,17 +1996,17 @@ select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
'$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
@@ -2041,17 +2041,17 @@ select jsonb_path_query(
'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))');
ERROR: cannot convert value from time to timetz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query_tz(
'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
@@ -2087,17 +2087,17 @@ select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
@@ -2134,17 +2134,17 @@ select jsonb_path_query(
'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query(
'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR: cannot convert value from timestamp to timestamptz without timezone usage
-HINT: use *_tz() function for timezone support
+HINT: Use *_tz() function for timezone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
@@ -2178,6 +2178,13 @@ select jsonb_path_query_tz(
"2017-03-10"
(4 rows)
+-- overflow during comparison
+select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath);
+ jsonb_path_query
+------------------
+ true
+(1 row)
+
set time zone default;
-- jsonpath operators
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 246e38b9edd..591be00278f 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -516,6 +516,9 @@ select jsonb_path_query_tz(
'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
+-- overflow during comparison
+select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath);
+
set time zone default;
-- jsonpath operators
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-22 18:22:22 +0000