Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 506 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/horology.out b/yql/essentials/tests/postgresql/cases/horology.out
new file mode 100644
index 0000000000..8fd3192ac9
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/horology.out
@@ -0,0 +1,506 @@
+--
+-- HOROLOGY
+--
+SET DateStyle = 'Postgres, MDY';
+-- should fail in mdy mode:
+SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+ERROR:  date/time field value out of range: "27/12/2001 04:05:06.789-08"
+LINE 1: SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'...
+                                        ^
+HINT:  Perhaps you need a different "datestyle" setting.
+set datestyle to dmy;
+reset datestyle;
+SET DateStyle = 'German';
+SET DateStyle = 'ISO';
+-- As of 7.4, allow time without time zone having a time zone specified
+SELECT time without time zone '040506.789+08';
+     time     
+--------------
+ 04:05:06.789
+(1 row)
+
+SELECT time without time zone '040506.789-08';
+     time     
+--------------
+ 04:05:06.789
+(1 row)
+
+SELECT time without time zone 'T040506.789+08';
+     time     
+--------------
+ 04:05:06.789
+(1 row)
+
+SELECT time without time zone 'T040506.789-08';
+     time     
+--------------
+ 04:05:06.789
+(1 row)
+
+SELECT time with time zone '040506.789+08';
+     timetz      
+-----------------
+ 04:05:06.789+08
+(1 row)
+
+SELECT time with time zone '040506.789-08';
+     timetz      
+-----------------
+ 04:05:06.789-08
+(1 row)
+
+SELECT time with time zone 'T040506.789+08';
+     timetz      
+-----------------
+ 04:05:06.789+08
+(1 row)
+
+SELECT time with time zone 'T040506.789-08';
+     timetz      
+-----------------
+ 04:05:06.789-08
+(1 row)
+
+SELECT time with time zone 'T040506.789 +08';
+     timetz      
+-----------------
+ 04:05:06.789+08
+(1 row)
+
+SELECT time with time zone 'T040506.789 -08';
+     timetz      
+-----------------
+ 04:05:06.789-08
+(1 row)
+
+SET DateStyle = 'Postgres, MDY';
+-- Shorthand values
+-- Not directly usable for regression testing since these are not constants.
+-- So, just try to test parser and hope for the best - thomas 97/04/26
+SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '1 day')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1 day')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'today 10:30' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone '10:30 today' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interval '2 days')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'tomorrow 16:00:00' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone '16:00:00 tomorrow' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'yesterday 12:34:56' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone '12:34:56 yesterday' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone 'tomorrow' > 'now') as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp with time zone 'tomorrow' > 'now') as "True";
+ True 
+------
+ t
+(1 row)
+
+-- timestamp with time zone, interval arithmetic around DST change
+-- (just for fun, let's use an intentionally nonstandard POSIX zone spec)
+SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0';
+RESET TIME ZONE;
+SELECT CAST(interval '02:03' AS time) AS "02:03:00";
+ 02:03:00 
+----------
+ 02:03:00
+(1 row)
+
+SELECT time '01:30' + interval '02:01' AS "03:31:00";
+ 03:31:00 
+----------
+ 03:31:00
+(1 row)
+
+SELECT time '01:30' - interval '02:01' AS "23:29:00";
+ 23:29:00 
+----------
+ 23:29:00
+(1 row)
+
+SELECT time '02:30' + interval '36:01' AS "14:31:00";
+ 14:31:00 
+----------
+ 14:31:00
+(1 row)
+
+SELECT time '03:30' + interval '1 month 04:01' AS "07:31:00";
+ 07:31:00 
+----------
+ 07:31:00
+(1 row)
+
+SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08";
+ 23:29:00-08 
+-------------
+ 23:29:00-08
+(1 row)
+
+SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08";
+ 14:31:00-08 
+-------------
+ 14:31:00-08
+(1 row)
+
+-- These two tests cannot be used because they default to current timezone,
+-- which may be either -08 or -07 depending on the time of year.
+-- SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
+-- SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08";
+-- Try the following two tests instead, as a poor substitute
+SELECT CAST(CAST(date 'today' + time with time zone '05:30'
+            + interval '02:01' AS time with time zone) AS time) AS "07:31:00";
+ 07:31:00 
+----------
+ 07:31:00
+(1 row)
+
+SELECT CAST(cast(date 'today' + time with time zone '03:30'
+  + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00";
+ 07:31:00 
+----------
+ 07:31:00
+(1 row)
+
+-- SQL9x OVERLAPS operator
+-- test with time zone
+SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28')
+  OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27')
+  OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False";
+ False 
+-------
+ f
+(1 row)
+
+-- test without time zone
+SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28')
+  OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27')
+  OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False";
+ False 
+-------
+ f
+(1 row)
+
+-- test time and interval
+SELECT (time '00:00', time '01:00')
+  OVERLAPS (time '00:30', time '01:30') AS "True";
+ True 
+------
+ t
+(1 row)
+
+CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone);
+-- get some candidate input values
+INSERT INTO TEMP_TIMESTAMP (f1)
+  SELECT d1 FROM TIMESTAMP_TBL
+  WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
+   OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
+DROP TABLE TEMP_TIMESTAMP;
+--
+-- Comparisons between datetime types, especially overflow cases
+---
+SELECT '2202020-10-05'::date::timestamp;  -- fail
+ERROR:  date out of range for timestamp
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamp as t;
+ t 
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamp > '2202020-10-05'::date as f;
+ f 
+---
+ f
+(1 row)
+
+SELECT '2202020-10-05'::date::timestamptz;  -- fail
+ERROR:  date out of range for timestamp
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamptz as t;
+ t 
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz > '2202020-10-05'::date as f;
+ f 
+---
+ f
+(1 row)
+
+SET TimeZone = 'UTC-2';
+SELECT '4714-11-24 BC'::date < '2020-10-05'::timestamptz as t;
+ t 
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::date as t;
+ t 
+---
+ t
+(1 row)
+
+SELECT '4714-11-24 BC'::timestamp < '2020-10-05'::timestamptz as t;
+ t 
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t;
+ t 
+---
+ t
+(1 row)
+
+RESET TimeZone;
+--
+-- Formats
+--
+SET DateStyle TO 'US,Postgres';
+SET DateStyle TO 'US,ISO';
+SELECT d1 AS us_iso FROM TIMESTAMP_TBL;
+         us_iso         
+------------------------
+ -infinity
+ infinity
+ 1970-01-01 00:00:00
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:02
+ 1997-02-10 17:32:01.4
+ 1997-02-10 17:32:01.5
+ 1997-02-10 17:32:01.6
+ 1997-01-02 00:00:00
+ 1997-01-02 03:04:05
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-06-10 17:32:01
+ 2001-09-22 18:19:20
+ 2000-03-15 08:14:01
+ 2000-03-15 13:14:02
+ 2000-03-15 12:14:03
+ 2000-03-15 03:14:04
+ 2000-03-15 02:14:05
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:00
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-02-10 17:32:01
+ 1997-06-10 18:32:01
+ 1997-02-10 17:32:01
+ 1997-02-11 17:32:01
+ 1997-02-12 17:32:01
+ 1997-02-13 17:32:01
+ 1997-02-14 17:32:01
+ 1997-02-15 17:32:01
+ 1997-02-16 17:32:01
+ 0097-02-16 17:32:01 BC
+ 0097-02-16 17:32:01
+ 0597-02-16 17:32:01
+ 1097-02-16 17:32:01
+ 1697-02-16 17:32:01
+ 1797-02-16 17:32:01
+ 1897-02-16 17:32:01
+ 1997-02-16 17:32:01
+ 2097-02-16 17:32:01
+ 1996-02-28 17:32:01
+ 1996-02-29 17:32:01
+ 1996-03-01 17:32:01
+ 1996-12-30 17:32:01
+ 1996-12-31 17:32:01
+ 1997-01-01 17:32:01
+ 1997-02-28 17:32:01
+ 1997-03-01 17:32:01
+ 1997-12-30 17:32:01
+ 1997-12-31 17:32:01
+ 1999-12-31 17:32:01
+ 2000-01-01 17:32:01
+ 2000-12-31 17:32:01
+ 2001-01-01 17:32:01
+(65 rows)
+
+SET DateStyle TO 'US,SQL';
+SET DateStyle TO 'European,Postgres';
+SET DateStyle TO 'European,ISO';
+SET DateStyle TO 'European,SQL';
+RESET DateStyle;
+SELECT to_timestamp('97/Feb/16', 'YYMonDD');
+ERROR:  invalid value "/Feb/16" for "Mon"
+DETAIL:  The given value did not match any of the allowed values for this field.
+SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ');  -- NYI
+ERROR:  formatting field "TZ" is only supported in to_char
+SELECT to_timestamp('2000 + + JUN', 'YYYY  MON');
+ERROR:  invalid value "+" for "MON"
+DETAIL:  The given value did not match any of the allowed values for this field.
+SELECT to_date('2011 x12 x18', 'YYYYxMMxDD');
+ERROR:  invalid value "x1" for "MM"
+DETAIL:  Value must be an integer.
+--
+-- Check errors for some incorrect usages of to_timestamp() and to_date()
+--
+-- Mixture of date conventions (ISO week and Gregorian):
+SELECT to_timestamp('2005527', 'YYYYIWID');
+ERROR:  invalid combination of date conventions
+HINT:  Do not mix Gregorian and ISO week date conventions in a formatting template.
+-- Insufficient characters in the source string:
+SELECT to_timestamp('19971', 'YYYYMMDD');
+ERROR:  source string too short for "MM" formatting field
+DETAIL:  Field requires 2 characters, but only 1 remain.
+HINT:  If your source string is not fixed-width, try using the "FM" modifier.
+-- Insufficient digit characters for a single node:
+SELECT to_timestamp('19971)24', 'YYYYMMDD');
+ERROR:  invalid value "1)" for "MM"
+DETAIL:  Field requires 2 characters, but only 1 could be parsed.
+HINT:  If your source string is not fixed-width, try using the "FM" modifier.
+-- We don't accept full-length day or month names if short form is specified:
+SELECT to_timestamp('Friday 1-January-1999', 'DY DD MON YYYY');
+ERROR:  invalid value "da" for "DD"
+DETAIL:  Value must be an integer.
+SELECT to_timestamp('Fri 1-January-1999', 'DY DD MON YYYY');
+ERROR:  invalid value "uary" for "YYYY"
+DETAIL:  Value must be an integer.
+-- Value clobbering:
+SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
+ERROR:  conflicting values for "Mon" field in formatting string
+DETAIL:  This value contradicts a previous setting for the same field type.
+-- Non-numeric input:
+SELECT to_timestamp('199711xy', 'YYYYMMDD');
+ERROR:  invalid value "xy" for "DD"
+DETAIL:  Value must be an integer.
+-- Input that doesn't fit in an int:
+SELECT to_timestamp('10000000000', 'FMYYYY');
+ERROR:  value for "YYYY" in source string is out of range
+DETAIL:  Value must be in the range -2147483648 to 2147483647.
+-- Out-of-range and not-quite-out-of-range fields:
+SELECT to_timestamp('2016-06-13 25:00:00', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2016-06-13 25:00:00"
+SELECT to_timestamp('2016-06-13 15:60:00', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2016-06-13 15:60:00"
+SELECT to_timestamp('2016-06-13 15:50:60', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2016-06-13 15:50:60"
+SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH:MI:SS');
+ERROR:  hour "15" is invalid for the 12-hour clock
+HINT:  Use the 24-hour clock, or give an hour between 1 and 12.
+SELECT to_timestamp('2016-13-01 15:50:55', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2016-13-01 15:50:55"
+SELECT to_timestamp('2016-02-30 15:50:55', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2016-02-30 15:50:55"
+SELECT to_timestamp('2015-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS');
+ERROR:  date/time field value out of range: "2015-02-29 15:50:55"
+SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS');
+ERROR:  date/time field value out of range: "2015-02-11 86400"
+SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS');
+ERROR:  date/time field value out of range: "2015-02-11 86400"
+SELECT to_date('2016-13-10', 'YYYY-MM-DD');
+ERROR:  date/time field value out of range: "2016-13-10"
+SELECT to_date('2016-02-30', 'YYYY-MM-DD');
+ERROR:  date/time field value out of range: "2016-02-30"
+SELECT to_date('2015-02-29', 'YYYY-MM-DD');
+ERROR:  date/time field value out of range: "2015-02-29"
+SELECT to_date('2015 366', 'YYYY DDD');
+ERROR:  date/time field value out of range: "2015 366"
+SELECT to_date('2016 367', 'YYYY DDD');
+ERROR:  date/time field value out of range: "2016 367"
+--
+-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
+--
+SET TIME ZONE 'America/New_York';
+SET TIME ZONE '-1.5';
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
+     to_char      
+------------------
+ 2012-12-12 43200
+(1 row)
+
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');
+     to_char      
+------------------
+ 2012-12-12 43200
+(1 row)
+
+RESET TIME ZONE;