You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by tu...@apache.org on 2022/04/18 09:21:29 UTC
[shardingsphere] branch master updated: Fix PostgreSQL and openGauss select mod function parse error (#16891)
This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 cd479c262fa Fix PostgreSQL and openGauss select mod function parse error (#16891)
cd479c262fa is described below
commit cd479c262fa30ca42b0362b7fbc8ec20cc9102e8
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Mon Apr 18 17:21:22 2022 +0800
Fix PostgreSQL and openGauss select mod function parse error (#16891)
* Fix PostgreSQL and openGauss select mod function parse error
* remove some test case in unsupported.xml
---
.../src/main/antlr4/imports/opengauss/BaseRule.g4 | 1 +
.../src/main/antlr4/imports/postgresql/BaseRule.g4 | 1 +
.../resources/case/dml/select-special-function.xml | 24 ++++++++++++++++++++--
.../sql/supported/dml/select-special-function.xml | 3 ++-
.../main/resources/sql/unsupported/unsupported.xml | 7 -------
5 files changed, 26 insertions(+), 10 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 63c6091ce43..99709f34436 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
@@ -271,6 +271,7 @@ unreservedWord
| MODE
| MONTH
| MOVE
+ | MOD
| NAME
| NAMES
| NEW
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 43e8bbb550f..d0e84153c58 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
@@ -272,6 +272,7 @@ unreservedWord
| MODE
| MONTH
| MOVE
+ | MOD
| NAME
| NAMES
| NEW
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 1c2b133b10a..bdae6eb1bde 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
@@ -154,17 +154,37 @@
</projections>
</select>
- <select sql-case-id="select_with_extract_function">
+ <select sql-case-id="select_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"/>
+ <literal-expression value="2001-02-16 20:38:40" start-index="25" stop-index="55" />
</parameter>
</function>
</expr>
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_mod_function">
+ <projections start-index="7" stop-index="22">
+ <expression-projection text="MOD(order_id, 1)" start-index="7" stop-index="22">
+ <expr>
+ <function function-name="MOD" start-index="7" stop-index="22" text="MOD(order_id, 1)">
+ <parameter>
+ <column name="order_id" start-index="11" stop-index="18" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="21" stop-index="21" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="29" stop-index="35" />
+ </from>
+ </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 a31d12c3323..f7541cd8624 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,5 +29,6 @@
<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-case id="select_extract_function" value="SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40')" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_mod_function" value="SELECT MOD(order_id, 1) from t_order" 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 39e492f589f..3139e1a2695 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
@@ -5590,10 +5590,6 @@
<sql-case id="insert_by_postgresql_source_test_case186" value="INSERT INTO large_tuple_test (select 3, NULL);" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case187" value="INSERT INTO large_tuple_test (select 4, repeat('a', 8126));" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case188" value="INSERT INTO main_table DEFAULT VALUES;" db-types="PostgreSQL"/>
- <sql-case id="insert_by_postgresql_source_test_case190" value="INSERT INTO mcv_lists (a, b, c, d) SELECT (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END), (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END), (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END) FROM generate_series(1,5000) s(i);" db-types="PostgreSQL"/>
- <sql-case id="insert_by_postgresql_source_test_case191" value="INSERT INTO mcv_lists (a, b, c, filler1) SELECT (CASE WHEN mod(i,100) = 1 THEN NULL ELSE mod(i,100) END), (CASE WHEN mod(i,50) = 1 THEN NULL ELSE mod(i,50) END), (CASE WHEN mod(i,25) = 1 THEN NULL ELSE mod(i,25) END), i FROM generate_series(1,5000) s(i);" db-types="PostgreSQL"/>
- <sql-case id="insert_by_postgresql_source_test_case197" value="INSERT INTO mcv_lists_bool (a, b, c) SELECT (mod(i,2) = 0), (mod(i,4) = 0), (mod(i,8) = 0) FROM generate_series(1,10000) s(i);" db-types="PostgreSQL"/>
- <sql-case id="insert_by_postgresql_source_test_case202" value="INSERT INTO mcv_lists_uuid (a, b, c) SELECT md5(mod(i,100)::text)::uuid, md5(mod(i,50)::text)::uuid, md5(mod(i,25)::text)::uuid FROM generate_series(1,5000) s(i);" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case205" value="INSERT INTO ndistinct (a, b, c, filler1) SELECT i/100, i/100, i/100, cash_words((i/100)::money) FROM generate_series(1,1000) s(i);" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case207" value="INSERT INTO nulltest DEFAULT VALUES;" db-types="PostgreSQL"/>
<sql-case id="insert_by_postgresql_source_test_case211" value="INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val)) FROM num_data WHERE val != '0.0';" db-types="PostgreSQL"/>
@@ -6965,7 +6961,6 @@
<sql-case id="select_by_postgresql_source_test_case1160" value="SELECT q1, float8(q1) FROM INT8_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case1161" value="SELECT q1, q1 << 2 AS "shl", q1 >> 3 AS "shr" FROM INT8_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case1162" value="SELECT q1, q2, q1 & q2 AS "and", q1 | q2 AS "or", q1 # q2 AS "xor", ~q1 AS "not" FROM INT8_TBL;" db-types="PostgreSQL"/>
- <sql-case id="select_by_postgresql_source_test_case1163" value="SELECT q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case1164" value="SELECT q2, float8(q2) FROM INT8_TBL;" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case1165" value="SELECT range_merge(nummultirange(numrange(1,2)));" db-types="PostgreSQL"/>
<sql-case id="select_by_postgresql_source_test_case1166" value="SELECT range_merge(nummultirange(numrange(1,2), numrange(7,8)));" db-types="PostgreSQL"/>
@@ -7146,7 +7141,6 @@
<sql-case id="with_by_postgresql_source_test_case19" value="WITH v(val) AS (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) SELECT val, to_char(val, '9.999EEEE') as numeric, to_char(val::float8, '9.999EEEE') as float8, to_char(val::float4, '9.999EEEE') as float4 FROM v;" db-types="PostgreSQL"/>
<sql-case id="with_by_postgresql_source_test_case20" value="WITH v(val) AS (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) SELECT val, to_char(val, 'MI99.99') as numeric, to_char(val::float8, 'MI99.99') as float8, to_char(val::float4, 'MI99.99') as float4 FROM v;" db-types="PostgreSQL"/>
<sql-case id="with_by_postgresql_source_test_case21" value="WITH v(val) AS (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) SELECT val, to_char(val, 'MI9999999999.99') as numeric, to_char(val::float8, 'MI9999999999.99') as float8, to_char(val::float4, 'MI9999999999.99') as float4 FROM v;" db-types="PostgreSQL"/>
- <sql-case id="with_by_postgresql_source_test_case22" value="WITH v(x) AS (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan')) SELECT x1, x2, x1 / x2 AS quot, x1 % x2 AS mod, div(x1, x2) AS div FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0;" db-types="PostgreSQL"/>
<sql-case id="low_alter_by_postgresql_source_test_case41" value="alter event trigger regress_event_trigger disable;" db-types="PostgreSQL"/>
<sql-case id="low_alter_by_postgresql_source_test_case42" value="alter event trigger regress_event_trigger disable;" db-types="PostgreSQL"/>
<sql-case id="low_alter_by_postgresql_source_test_case43" value="alter event trigger regress_event_trigger enable always;" db-types="PostgreSQL"/>
@@ -8375,7 +8369,6 @@
<sql-case id="low_select_by_postgresql_source_test_case258" value="select dfunc(b => 'b'::text, a => 'a');" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case259" value="select dfunc(b => 'b'::text, a => 'a', flag => true);" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case260" value="select distinct from pg_database;" db-types="PostgreSQL"/>
- <sql-case id="low_select_by_postgresql_source_test_case261" value="select div(-9999999999999999999999::numeric,1000000000000000000000)*1000000000000000000000 + mod(-9999999999999999999999::numeric,1000000000000000000000);" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case262" value="select f1(2, 4) as int, f1(2, 4.5) as num;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case263" value="select f1(42) as int, f1(4.5) as num;" db-types="PostgreSQL"/>
<sql-case id="low_select_by_postgresql_source_test_case264" value="select f1(42) as int, f1(4.5) as num;" db-types="PostgreSQL"/>