This is an automated email from the ASF dual-hosted git repository.
totalo pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 86a358740fa Fix PostgreSQL and openGauss time extract function parse
error (#16879)
86a358740fa is described below
commit 86a358740fa8b933f4871b074ddc924962d0e400
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Sat Apr 16 20:37:40 2022 +0800
Fix PostgreSQL and openGauss time extract function parse error (#16879)
* Fix PostgreSQL and openGauss time extract function parse error
* fix unit test
---
.../src/main/antlr4/imports/opengauss/BaseRule.g4 | 3 ++
.../src/main/antlr4/imports/postgresql/BaseRule.g4 | 3 ++
.../resources/case/dml/select-special-function.xml | 14 ++++++
.../sql/supported/dml/select-special-function.xml | 1 +
.../main/resources/sql/unsupported/unsupported.xml | 50 ----------------------
5 files changed, 21 insertions(+), 50 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
index 516a33284d6..63c6091ce43 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
@@ -244,6 +244,7 @@ unreservedWord
| INSERT
| INSTEAD
| INVOKER
+ | INTERVAL
| ISOLATION
| KEY
| LABEL
@@ -395,6 +396,8 @@ unreservedWord
| TRUSTED
| TYPE
| TYPES
+ | TIME
+ | TIMESTAMP
| UESCAPE
| UNBOUNDED
| UNCOMMITTED
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
index 213bbe5e94a..43e8bbb550f 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
@@ -245,6 +245,7 @@ unreservedWord
| INSERT
| INSTEAD
| INVOKER
+ | INTERVAL
| ISOLATION
| KEY
| LABEL
@@ -396,6 +397,8 @@ unreservedWord
| TRUSTED
| TYPE
| TYPES
+ | TIME
+ | TIMESTAMP
| UESCAPE
| UNBOUNDED
| UNCOMMITTED
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-special-function.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-special-function.xml
index 5455de00ce4..1c2b133b10a 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-special-function.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-special-function.xml
@@ -153,4 +153,18 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_with_extract_function">
+ <projections start-index="7" stop-index="56">
+ <expression-projection text="EXTRACT(YEAR FROM TIMESTAMP
'2001-02-16 20:38:40')" start-index="7" stop-index="56">
+ <expr>
+ <function function-name="EXTRACT" start-index="7"
stop-index="56" text="EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40')">
+ <parameter>
+ <literal-expression value="2001-02-16 20:38:40"
start-index="25" stop-index="55"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-special-function.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-special-function.xml
index 5d29273a519..a31d12c3323 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-special-function.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -29,4 +29,5 @@
<sql-case id="select_weight_string" value="SELECT WEIGHT_STRING('bar')"
db-types="MySQL" />
<sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order"
db-types="MySQL" />
<sql-case id="select_current_user_brackets" value="SELECT CURRENT_USER()"
db-types="MySQL" />
+ <sql-case id="select_with_extract_function" value="SELECT EXTRACT(YEAR
FROM TIMESTAMP '2001-02-16 20:38:40')" db-types="PostgreSQL,openGauss" />
</sql-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/unsupported/unsupported.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/unsupported/unsupported.xml
index 983da3f7416..39e492f589f 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/unsupported/unsupported.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/unsupported/unsupported.xml
@@ -5531,7 +5531,6 @@
<sql-case id="insert_by_postgresql_source_test_case37" value="INSERT INTO
VARBIT_TABLE VALUES (B'010101');" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case38" value="INSERT INTO
VARBIT_TABLE VALUES (B'01010101010');" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case39" value="INSERT INTO
VARBIT_TABLE VALUES (B'101011111010');" db-types="PostgreSQL"/>
- <sql-case id="insert_by_postgresql_source_test_case40" value="INSERT INTO
ab1 SELECT generate_series(1,10), generate_series(1,10),
generate_series('2020-10-01'::timestamp,
'2020-10-10'::timestamp, interval '1 day'),
generate_series('2020-10-01'::timestamptz,
'2020-10-10'::timestamptz, interval '1 day');"
db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case44" value="INSERT INTO
abbrev_abort_uuids DEFAULT VALUES;" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case45" value="INSERT INTO
abbrev_abort_uuids DEFAULT VALUES;" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case49" value="INSERT INTO
arrtest (a, b[1:2], c, d[1:2]) VALUES ('{}', '{3,4}',
'{foo,bar}', '{bar,foo}');" db-types="PostgreSQL"/>
@@ -5865,10 +5864,6 @@
<sql-case id="select_by_postgresql_source_test_case3" value="SELECT
p::text || ',' || i::text || ':' || CASE
WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row,
logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt,
logging_agg_nonstrict_initcond(v) filter(where f) over wnd as
nstrict_init_filt, logging_agg_strict(v::text) filter(where f) over wnd
as strict_filt, logging_agg_strict_initcond(v) filter(where f) over wnd
[...]
<sql-case id="select_by_postgresql_source_test_case4" value="SELECT
p::text || ',' || i::text || ':' || COALESCE(v::text,
'NULL') AS row, logging_agg_nonstrict(v) over wnd as nstrict,
logging_agg_nonstrict_initcond(v) over wnd as nstrict_init,
logging_agg_strict(v::text) over wnd as strict,
logging_agg_strict_initcond(v) over wnd as strict_init FROM (VALUES (1, 1,
NULL), (1, 2, 'a'), (1, 3, 'b'), (1, 4, NULL), (1, 5,
NULL), [...]
<sql-case id="select_by_postgresql_source_test_case5" value="SELECT
(SELECT n FROM (VALUES (1)) AS x, (SELECT n FROM
generate_series(1,10) AS n ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS
z FROM generate_series(1,10) AS s;" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case6" value="SELECT str,
interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp
'0055-06-17 BC') AS equal FROM ( VALUES ('week',
'7 d'), ('day', '1 d'), ('hour',
'1 h'), ('minute', '1 m'),
('second', '1 s'), ('millisecond', '1
ms'), ('microsecond', '1 u [...]
- <sql-case id="select_by_postgresql_source_test_case7" value="SELECT str,
interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp
'2000-01-01 BC') AS equal FROM ( VALUES ('week',
'7 d'), ('day', '1 d'), ('hour',
'1 h'), ('minute', '1 m'),
('second', '1 s'), ('millisecond', '1
ms'), ('microsecond', '1 u [...]
- <sql-case id="select_by_postgresql_source_test_case8" value="SELECT str,
interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp
'2001-01-01') AS equal FROM ( VALUES ('week', '7
d'), ('day', '1 d'), ('hour', '1
h'), ('minute', '1 m'), ('second',
'1 s'), ('millisecond', '1 ms'),
('microsecond', '1 us&a [...]
- <sql-case id="select_by_postgresql_source_test_case9" value="SELECT str,
interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp
'2020-03-02') AS equal FROM ( VALUES ('week', '7
d'), ('day', '1 d'), ('hour', '1
h'), ('minute', '1 m'), ('second',
'1 s'), ('millisecond', '1 ms'),
('microsecond', '1 us&a [...]
<sql-case id="select_by_postgresql_source_test_case10" value="SELECT
str, interval, date_trunc(str, ts, 'Australia/Sydney') =
date_bin(interval::interval, ts, timestamp with time zone
'2001-01-01+11') AS equal FROM ( VALUES ('day', '1
d'), ('hour', '1 h'), ('minute', '1
m'), ('second', '1 s'), ('millisecond',
'1 ms'), ('microsecond', &a [...]
<sql-case id="select_by_postgresql_source_test_case11" value="SELECT
thousand FROM onek WHERE thousand < 5 ORDER BY
thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case12" value="SELECT
thousand FROM onek WHERE thousand < 5 ORDER BY
thousand FETCH FIRST 1 ROW WITH TIES;" db-types="PostgreSQL"/>
@@ -6085,12 +6080,6 @@
<sql-case id="select_by_postgresql_source_test_case224" value="SELECT *
FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13),
rngfunc_mat(10+r,13) );" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case225" value="SELECT *
FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13),
rngfunc_mat(11,11) );" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case226" value="SELECT *
FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(11,11),
rngfunc_mat(10+r,13) );" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case227" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 < interval '@ 1 day';"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case228" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 <= interval '@ 5
hours';" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case229" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 <> interval '@ 10
days';" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case230" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 = interval '@ 34 years';"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case231" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds
ago';" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case232" value="SELECT *
FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 >= interval '@ 1
month';" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case233" value="SELECT *
FROM ROWS FROM(generate_series(10,11), get_users()) WITH ORDINALITY;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case234" value="SELECT *
FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case235" value="SELECT *
FROM box_temp WHERE f1 &< '(10,4.333334),(5,100)';"
db-types="PostgreSQL"/>
@@ -6183,12 +6172,10 @@
<sql-case id="select_by_postgresql_source_test_case322" value="SELECT
DATE_TRUNC('CENTURY', DATE '0055-08-10 BC');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case323" value="SELECT
DATE_TRUNC('CENTURY', DATE '1970-03-20');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case324" value="SELECT
DATE_TRUNC('CENTURY', DATE '2004-08-10');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case325" value="SELECT
DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20
04:30:00.00000');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case326" value="SELECT
DATE_TRUNC('DECADE', DATE '0002-12-31 BC');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case327" value="SELECT
DATE_TRUNC('DECADE', DATE '0004-12-25');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case328" value="SELECT
DATE_TRUNC('DECADE', DATE '1993-12-25');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case329" value="SELECT
DATE_TRUNC('MILLENNIUM', DATE '1970-03-20');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case330" value="SELECT
DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20
04:30:00.00000');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case331" value="SELECT
DISTINCT p1.proargtypes[0]::regtype, p2.proargtypes[0]::regtype FROM pg_proc AS
p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND
p1.prolang = 12 AND p2.prolang = 12 AND p1.prokind != 'a' AND
p2.prokind != 'a' AND p1.prosrc NOT LIKE
E'range\\_constructor_' AND p2.prosrc NOT LIKE
E'range\\_constructor_' AND p1.prosrc NOT LIKE E&apos [...]
<sql-case id="select_by_postgresql_source_test_case332" value="SELECT
DISTINCT p1.proargtypes[1]::regtype, p2.proargtypes[1]::regtype FROM pg_proc AS
p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND
p1.prolang = 12 AND p2.prolang = 12 AND p1.prokind != 'a' AND
p2.prokind != 'a' AND p1.prosrc NOT LIKE
E'range\\_constructor_' AND p2.prosrc NOT LIKE
E'range\\_constructor_' AND p1.prosrc NOT LIKE E&apos [...]
<sql-case id="select_by_postgresql_source_test_case333" value="SELECT
DISTINCT p1.proargtypes[2]::regtype, p2.proargtypes[2]::regtype FROM pg_proc AS
p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND
p1.prolang = 12 AND p2.prolang = 12 AND p1.prokind != 'a' AND
p2.prokind != 'a' AND (p1.proargtypes[2] < p2.proargtypes[2])
ORDER BY 1, 2;" db-types="PostgreSQL"/>
@@ -6231,13 +6218,8 @@
<sql-case id="select_by_postgresql_source_test_case370" value="SELECT
EXTRACT(CENTURY FROM DATE '1901-01-01');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case371" value="SELECT
EXTRACT(CENTURY FROM DATE '2000-12-31');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case372" value="SELECT
EXTRACT(CENTURY FROM DATE '2001-01-01');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case373" value="SELECT
EXTRACT(CENTURY FROM INTERVAL '-100 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case374" value="SELECT
EXTRACT(CENTURY FROM INTERVAL '-99 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case375" value="SELECT
EXTRACT(CENTURY FROM INTERVAL '100 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case376" value="SELECT
EXTRACT(CENTURY FROM INTERVAL '99 y');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case377" value="SELECT
EXTRACT(DAY FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case378" value="SELECT
EXTRACT(DAY FROM DATE 'infinity');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case379" value="SELECT
EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case380" value="SELECT
EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case381" value="SELECT
EXTRACT(DAY FROM DATE '-infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case382" value="SELECT
EXTRACT(DAY FROM DATE 'infinity');" db-types="PostgreSQL"/>
@@ -6250,27 +6232,19 @@
<sql-case id="select_by_postgresql_source_test_case389" value="SELECT
EXTRACT(DECADE FROM DATE '0011-01-01 BC');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case390" value="SELECT
EXTRACT(DECADE FROM DATE '0012-12-31 BC');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case391" value="SELECT
EXTRACT(DECADE FROM DATE '1994-12-25');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case392" value="SELECT
EXTRACT(DECADE FROM INTERVAL '-100 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case393" value="SELECT
EXTRACT(DECADE FROM INTERVAL '-99 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case394" value="SELECT
EXTRACT(DECADE FROM INTERVAL '100 y');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case395" value="SELECT
EXTRACT(DECADE FROM INTERVAL '99 y');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case396" value="SELECT
EXTRACT(DOW FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case397" value="SELECT
EXTRACT(DOW FROM DATE '2020-08-16');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case398" value="SELECT
EXTRACT(DOW FROM DATE 'infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case399" value="SELECT
EXTRACT(DOY FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case400" value="SELECT
EXTRACT(DOY FROM DATE 'infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case401" value="SELECT
EXTRACT(EPOCH FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case402" value="SELECT
EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case403" value="SELECT
EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case404" value="SELECT
EXTRACT(EPOCH FROM DATE 'infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case405" value="SELECT
EXTRACT(EPOCH FROM DATE '1970-01-01');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case406" value="SELECT
EXTRACT(EPOCH FROM DATE '-infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case407" value="SELECT
EXTRACT(EPOCH FROM DATE 'infinity');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case408" value="SELECT
EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case409" value="SELECT
EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case410" value="SELECT
EXTRACT(FORTNIGHT FROM INTERVAL '2 days');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case411" value="SELECT
EXTRACT(HOUR FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case412" value="SELECT
EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case413" value="SELECT
EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case414" value="SELECT
EXTRACT(ISODOW FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case415" value="SELECT
EXTRACT(ISODOW FROM DATE '2020-08-16');"
db-types="PostgreSQL"/>
@@ -6292,23 +6266,18 @@
<sql-case id="select_by_postgresql_source_test_case431" value="SELECT
EXTRACT(MILLENNIUM FROM DATE '2000-12-31');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case432" value="SELECT
EXTRACT(MILLENNIUM FROM DATE '2001-01-01');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case433" value="SELECT
EXTRACT(MILLENNIUM FROM DATE 'infinity');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case434" value="SELECT
EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case435" value="SELECT
EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case436" value="SELECT
EXTRACT(MILLISECONDS FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case437" value="SELECT
EXTRACT(MINUTE FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case438" value="SELECT
EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case439" value="SELECT
EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case440" value="SELECT
EXTRACT(MONTH FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case441" value="SELECT
EXTRACT(MONTH FROM DATE 'infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case442" value="SELECT
EXTRACT(QUARTER FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case443" value="SELECT
EXTRACT(QUARTER FROM DATE 'infinity');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case444" value="SELECT
EXTRACT(SECOND FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case445" value="SELECT
EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case446" value="SELECT
EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case447" value="SELECT
EXTRACT(TIMEZONE FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case448" value="SELECT
EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case449" value="SELECT
EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04:30');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case450" value="SELECT
EXTRACT(TIMEZONE FROM INTERVAL '2 days');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case451" value="SELECT
EXTRACT(TIMEZONE_H FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case452" value="SELECT
EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04:30');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case453" value="SELECT
EXTRACT(TIMEZONE_M FROM DATE '2020-08-11');"
db-types="PostgreSQL"/>
@@ -6633,24 +6602,14 @@
<sql-case id="select_by_postgresql_source_test_case778" value="SELECT d1
as "timestamp", date_part( 'quarter', d1) AS quarter,
date_part( 'msec', d1) AS msec, date_part( 'usec', d1)
AS usec FROM TIMESTAMP_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case779" value="SELECT d1
as timestamptz, date_part( 'isoyear', d1) AS isoyear, date_part(
'week', d1) AS week, date_part( 'isodow', d1) AS isodow,
date_part( 'dow', d1) AS dow, date_part( 'doy', d1) AS
doy FROM TIMESTAMPTZ_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case780" value="SELECT d1
as timestamptz, date_part( 'quarter', d1) AS quarter, date_part(
'msec', d1) AS msec, date_part( 'usec', d1) AS usec
FROM TIMESTAMPTZ_TBL;" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case781" value="SELECT
date_bin('-2 days'::interval, timestamp '1970-01-01
01:00:00' , timestamp '1970-01-01 00:00:00');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case782" value="SELECT
date_bin('-2 days'::interval, timestamp with time zone
'1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01
00:00:00+00');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case783" value="SELECT
date_bin('0 days'::interval, timestamp '1970-01-01
01:00:00' , timestamp '1970-01-01 00:00:00');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case784" value="SELECT
date_bin('0 days'::interval, timestamp with time zone
'1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01
00:00:00+00');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case785" value="SELECT
date_bin('5 min'::interval, timestamp '2020-02-01
01:01:01', timestamp '2020-02-01 00:02:30');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case786" value="SELECT
date_bin('5 months'::interval, timestamp '2020-02-01
01:01:01', timestamp '2001-01-01');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case787" value="SELECT
date_bin('5 months'::interval, timestamp with time zone
'2020-02-01 01:01:01+00', timestamp with time zone
'2001-01-01+00');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case788" value="SELECT
date_bin('5 years'::interval, timestamp '2020-02-01
01:01:01', timestamp '2001-01-01');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case789" value="SELECT
date_bin('5 years'::interval, timestamp with time zone
'2020-02-01 01:01:01+00', timestamp with time zone
'2001-01-01+00');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case790" value="SELECT
date_part('epoch', TIME '2020-05-26
13:30:25.575401');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case791" value="SELECT
date_part('epoch', TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case792" value="SELECT
date_part('microsecond', TIME '2020-05-26
13:30:25.575401');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case793" value="SELECT
date_part('microsecond', TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case794" value="SELECT
date_part('millisecond', TIME '2020-05-26
13:30:25.575401');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case795" value="SELECT
date_part('millisecond', TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case796" value="SELECT
date_part('second', TIME '2020-05-26
13:30:25.575401');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case797" value="SELECT
date_part('second', TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case798" value="SELECT
date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' )
AS week_trunc;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case799" value="SELECT
date_trunc( 'week', timestamp with time zone '2004-02-29
15:44:17.71393' ) AS week_trunc;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case800" value="SELECT
date_trunc('day', timestamp with time zone '2001-02-16
20:38:40+00', 'Australia/Sydney') as sydney_trunc;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case801" value="SELECT
date_trunc('day', timestamp with time zone '2001-02-16
20:38:40+00', 'GMT') as gmt_trunc;" db-types="PostgreSQL"/>
@@ -6662,19 +6621,16 @@
<sql-case id="select_by_postgresql_source_test_case807" value="SELECT
encode(overlay(E'Th\\000omas'::bytea placing
E'Th\\001omas'::bytea from 2),'escape');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case808" value="SELECT
encode(overlay(E'Th\\000omas'::bytea placing
E'\\002\\003'::bytea from 5 for 3),'escape');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case809" value="SELECT
encode(overlay(E'Th\\000omas'::bytea placing
E'\\002\\003'::bytea from 8),'escape');"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case810" value="SELECT
extract(epoch from interval '1000000000 days');"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case811" value="SELECT
f.f1, @f.f1 AS abs_f1 FROM FLOAT8_TBL f;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case812" value="SELECT
f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case813" value="SELECT
f.f1, |/f.f1 AS sqrt_f1 FROM FLOAT8_TBL f WHERE f.f1 >
'0.0';" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case814" value="SELECT
f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case815" value="SELECT f1 +
time '00:01' AS "Illegal" FROM TIME_TBL;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case816" value="SELECT f1 +
time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case817" value="SELECT f1
AS open_path, polygon( pclose(f1)) AS polygon FROM PATH_TBL WHERE
isopen(f1);" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case818" value="SELECT f1
as "date", date_part('year', f1) AS year,
date_part('month', f1) AS month, date_part('day', f1)
AS day, date_part('quarter', f1) AS quarter,
date_part('decade', f1) AS decade, date_part('century',
f1) AS century, date_part('millennium', f1) AS millennium,
date_part('isoyear', f1) AS isoyear, [...]
<sql-case id="select_by_postgresql_source_test_case819" value="SELECT f1,
EXTRACT(MICROSECOND FROM f1) AS MICROSECOND, EXTRACT(MILLISECOND FROM
f1) AS MILLISECOND, EXTRACT(SECOND FROM f1) AS SECOND, EXTRACT(MINUTE
FROM f1) AS MINUTE, EXTRACT(HOUR FROM f1) AS HOUR, EXTRACT(DAY FROM f1)
AS DAY, EXTRACT(MONTH FROM f1) AS MONTH, EXTRACT(QUARTER FROM f1) AS
QUARTER, EXTRACT(YEAR FROM f1) AS YEAR, EXTRACT(DECADE FROM f1) AS
DECADE, EXTRACT(CENTUR [...]
<sql-case id="select_by_postgresql_source_test_case820" value="SELECT f1,
date_part('microsecond', f1) AS microsecond,
date_part('millisecond', f1) AS millisecond,
date_part('second', f1) AS second, date_part('epoch',
f1) AS epoch FROM INTERVAL_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case821" value="SELECT f1,
@-@ f1 FROM PATH_TBL;" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case822" value="SELECT f1,
f1::INTERVAL DAY TO MINUTE AS "minutes", (f1 + INTERVAL '1
month')::INTERVAL MONTH::INTERVAL YEAR AS "years" FROM
interval_tbl;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case823" value="SELECT f1,
f1::box FROM POLYGON_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case824" value="SELECT f1,
f1::path FROM POLYGON_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case825" value="SELECT f1,
f1::polygon FROM CIRCLE_TBL WHERE f1 >= '<(0,0),1>';"
db-types="PostgreSQL"/>
@@ -6699,7 +6655,6 @@
<sql-case id="select_by_postgresql_source_test_case846" value="SELECT i AS
inet, host(i), text(i), family(i) FROM INET_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case847" value="SELECT i
FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;"
db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case848" value="SELECT i
FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case849" value="SELECT i,
to_char(i * interval '1mon', 'rm'), to_char(i *
interval '1mon', 'RM') FROM generate_series(-13, 13)
i;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case850" value="SELECT i,
c, i < c AS lt, i <= c AS le, i = c AS eq, i >= c AS ge, i > c
AS gt, i <> c AS ne, i << c AS sb, i <<= c AS sbe, i
>> c AS sup, i >>= c AS spe, i && c AS ovr FROM
INET_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case851" value="SELECT i,
~i AS "~i" FROM inet_tbl;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case852" value="SELECT
i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING) FROM
(VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);"
db-types="PostgreSQL"/>
@@ -6768,9 +6723,6 @@
<sql-case id="select_by_postgresql_source_test_case915" value="SELECT
jsonb_path_query_first('[{"a": 1}, {"a": 2},
{"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min
&& @ < $max)', vars => '{"min": 1,
"max": 4}');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case916" value="SELECT
jsonb_path_query_first('[{"a": 1}, {"a": 2},
{"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min
&& @ < $max)', vars => '{"min": 3,
"max": 4}');" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case917" value="SELECT
jsonb_path_query_first('[{"a": 1}, {"a": 2},
{}]', 'strict $[*].a', silent => true);"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case918" value="SELECT
justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds')
as "7 mons 6 days 5 hours 4 mins 3 seconds";" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case919" value="SELECT
justify_hours(interval '6 months 3 days 52 hours 3 minutes 2
seconds') as "6 mons 5 days 4 hours 3 mins 2 seconds";"
db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case920" value="SELECT
justify_interval(interval '1 month -1 hour') as "1 month -1
hour";" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case921" value="SELECT l.s,
b.f1 FROM LINE_TBL l, BOX_TBL b WHERE l.s ?# b.f1;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case922" value="SELECT l.s,
b.f1 FROM LSEG_TBL l, BOX_TBL b WHERE l.s ?# b.f1;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case923" value="SELECT l.s,
b.f1, l.s ## b.f1 FROM LINE_TBL l, BOX_TBL b;" db-types="PostgreSQL"/>
@@ -8482,12 +8434,10 @@
<sql-case id="low_select_by_postgresql_source_test_case322" value="select
length(md5((f1[1]).c2)) from dest;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case323" value="select
length(md5((f1[1]).c2)) from dest;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case324" value="select
md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS
"TRUE";" db-types="PostgreSQL"/>
- <sql-case id="low_select_by_postgresql_source_test_case325" value="select
md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea)
= '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";"
db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case326" value="select
md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea)
= 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";"
db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case327" value="select
md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS
"TRUE";" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case328" value="select
md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS
"TRUE";" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case329" value="select
md5('abcdefghijklmnopqrstuvwxyz'::bytea) =
'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE";"
db-types="PostgreSQL"/>
- <sql-case id="low_select_by_postgresql_source_test_case330" value="select
md5('message digest'::bytea) =
'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE";"
db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case331" value="select
min_scale(numeric 'NaN') is NULL;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case332" value="select
min_scale(numeric 'inf') is NULL;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case333" value="select
mr_polymorphic(int4range(1, 4));" db-types="PostgreSQL"/>
