Stabilize jsonb_path_query test case.

tglsfdc · tglsfdc · commit a358019159de · 2024-10-30T11:42:34.000-04:00
An operation like '12:34:56'::time_tz takes the UTC offset from the prevailing time zone, which means that the results change across DST transitions. One of the test cases added in ed055d2 failed to consider this. Per report from Bernhard Wiedemann. Back-patch to v17, as the test case was. Discussion: https://postgr.es/m/ba8e1bc0-8a99-45b7-8397-3f2e94415e03@suse.de
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2634,12 +2634,16 @@ select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
  "12:34:56+05:30"
 (1 row)
 
+-- this timetz usage will absorb the UTC offset of the current timezone setting
+begin;
+set local timezone = 'UTC-10';
 select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
  jsonb_path_query_tz 
 ---------------------
- "12:34:56-07:00"
+ "12:34:56+10:00"
 (1 row)
 
+rollback;
 select jsonb_path_query('"12:34:56"', '$.time().string()');
  jsonb_path_query 
 ------------------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -596,7 +596,11 @@ select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()')
 select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
 select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
 select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+-- this timetz usage will absorb the UTC offset of the current timezone setting
+begin;
+set local timezone = 'UTC-10';
 select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+rollback;
 select jsonb_path_query('"12:34:56"', '$.time().string()');
 select jsonb_path_query('"2023-08-15"', '$.date().string()');
 
