This is an automated email from the ASF dual-hosted git repository.
jianglongtao 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 ad529a8 fix wrong route result caused by oracle parser ambiguity
(#13450)
ad529a8 is described below
commit ad529a8ee7e06fc76225608888729a23ec4473f4
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Thu Nov 4 13:37:08 2021 +0800
fix wrong route result caused by oracle parser ambiguity (#13450)
* fix wrong route result caused by oracle parser ambiguity
* move unsupported case to supported
---
.../src/main/antlr4/imports/oracle/Keyword.g4 | 4 --
.../src/main/resources/case/dml/select.xml | 63 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 2 +
.../main/resources/sql/unsupported/unsupported.xml | 1 -
.../resources/scenario/sharding/case/select.xml | 5 ++
5 files changed, 70 insertions(+), 5 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/Keyword.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/Keyword.g4
index fc5bf0a..d87e9d4 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/Keyword.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/Keyword.g4
@@ -562,7 +562,3 @@ TRIGGERS
GLOBAL_NAME
: G L O B A L UL_ N A M E
;
-
-A
- : 'A'
- ;
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
index 541bb83..a1a228e 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
@@ -388,6 +388,46 @@
</where>
</select>
+ <select sql-case-id="select_equal_with_single_table_and_lowercase_keyword"
parameters="1, 1">
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="22" stop-index="55">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="55">
+ <left>
+ <binary-operation-expression start-index="28"
stop-index="38">
+ <left>
+ <column name="user_id" start-index="28"
stop-index="34" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="38"
stop-index="38" />
+ <parameter-marker-expression value="0"
start-index="38" stop-index="38" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>and</operator>
+ <right>
+ <binary-operation-expression start-index="44"
stop-index="55">
+ <left>
+ <column name="order_id" start-index="44"
stop-index="51" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="55"
stop-index="55" />
+ <parameter-marker-expression value="1"
start-index="55" stop-index="55" />
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_equal_with_same_sharding_column"
parameters="1, 2">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
@@ -3651,6 +3691,7 @@
<column-item name="product" start-index="309" stop-index="315" />
</order-by>
</select>
+
<select sql-case-id="select_with_comments">
<from>
<simple-table name="t_order" start-index="51" stop-index="57" />
@@ -3662,4 +3703,26 @@
<comment start-index="32" stop-index="49" text="# middle
comments
"/>
<comment start-index="60" stop-index="74" text="-- end comments"/>
</select>
+
+ <select sql-case-id="select_with_model_in">
+ <from>
+ <subquery-table>
+ <subquery>
+ <select>
+ <projections start-index="55" stop-index="107">
+ <expression-projection text="1001"
alias="order_id_value" start-index="55" stop-index="76" />
+ <expression-projection text="100001"
alias="order_item_id_value" start-index="79" stop-index="107" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="114"
stop-index="117" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ <projections start-index="7" stop-index="40">
+ <column-projection name="order_id_value" start-index="7"
stop-index="20"/>
+ <column-projection name="order_item_id_value" start-index="22"
stop-index="40"/>
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
index 234d447..bcd7c5e 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
@@ -31,6 +31,7 @@
<sql-case id="select_not_in_with_single_table" value="SELECT * FROM
t_order_item WHERE item_id IS NOT NULL AND item_id NOT IN (?, ?) ORDER BY
item_id" />
<sql-case id="select_not_between_with_single_table" value="SELECT * FROM
t_order_item WHERE item_id IS NOT NULL AND item_id NOT BETWEEN ? AND ? ORDER BY
item_id" />
<sql-case id="select_equal_with_single_table" value="SELECT * FROM t_order
WHERE user_id = ? AND order_id = ?" />
+ <sql-case id="select_equal_with_single_table_and_lowercase_keyword"
value="select * from t_order where user_id = ? and order_id = ?" />
<sql-case id="select_in_with_single_table" value="SELECT * FROM t_order
WHERE user_id IN (?, ?, ?) AND order_id IN (?, ?) ORDER BY user_id, order_id" />
<sql-case id="select_between_with_single_table" value="SELECT * FROM
t_order WHERE user_id BETWEEN ? AND ? AND order_id BETWEEN ? AND ? ORDER BY
user_id, order_id" />
<sql-case id="select_comparison_symbol_with_single_table" value="SELECT *
FROM t_order WHERE user_id >= ? AND user_id <= ? AND order_id >= ? AND
order_id <= ? ORDER BY user_id, order_id" />
@@ -105,4 +106,5 @@
<sql-case id="select_with_model_with_order_by" value="SELECT year, sales
FROM sales_view WHERE country='Italy' AND prod='Bounce' MODEL DIMENSION BY
(year) MEASURES (sale sales) RULES SEQUENTIAL ORDER (sales[ANY] ORDER BY year
DESC = sales[cv(year)-1]) ORDER BY year" db-types="Oracle" />
<sql-case id="select_with_model_with_multi_column_for_loop" value="SELECT
country, product, year, s FROM sales_view MODEL DIMENSION BY (country, product,
year) MEASURES (sales s) IGNORE NAV RULES UPSERT (s[FOR (country, product,
year) IN (SELECT DISTINCT 'new_country', product, year FROM sales_view WHERE
country = 'Poland')] = s['Poland',CV(),CV()]) ORDER BY country, year, product"
db-types="Oracle" />
<sql-case id="select_with_comments" value="-- begin comments
SELECT
* FROM # middle comments
 t_order; -- end comments" db-types="MySQL"/>
+ <sql-case id="select_with_model_in" value="SELECT
order_id_value,order_item_id_value FROM (select 1001 as order_id_value, 100001
as order_item_id_value from dual) MODEL RETURN UPDATED ROWS DIMENSION
BY(order_item_id_value) MEASURES(order_id_value) RULES(order_id_value[1] =
10001)" db-types="Oracle" />
</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 afd0137..4e7548e 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
@@ -22,7 +22,6 @@
<sql-case id="assert_select_with_json_extract_sign_with_parameter_marker"
value="SELECT * FROM t_order WHERE order_id -> ?" db-types="MySQL"/>
<sql-case
id="assert_select_with_json_unquote_extract_sign_with_parameter_marker"
value="SELECT * FROM t_order WHERE order_id ->> ?" db-types="MySQL"/>
<sql-case id="assert_insert_with_first" value="INSERT FIRST INTO TABLE_XXX
(field1) VALUES (field1) SELECT field1 FROM TABLE_XXX2" db-types="Oracle" />
- <sql-case id="assert_select_with_model_in" value="SELECT
order_id_value,order_item_id_value FROM (select 1001 as order_id_value, 100001
as order_item_id_value from dual) MODEL RETURN UPDATED ROWS DIMENSION
BY(order_item_id_value) MEASURES(order_id_value) RULES(order_id_value[1] =
10001)" db-types="Oracle" />
<sql-case id="assert_dist_SQL_show_rule_parse_conflict" value="SHOW
REPLICA_QUERY RULE FROM schema_name"/>
<sql-case id="create_bit_xor_table" value="create table BIT_XOR (a int);"
db-types="MySQL"/>
<sql-case id="select_with_comment1" value="select 2 as expected,
/*!01000/**/*/ 2 as result;" db-types="MySQL"/>
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
index bbe5b18..112038f 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
@@ -516,4 +516,9 @@
<input sql="SELECT * FROM T_ROLE JOIN T_ROLE_ADMIN ON T_ROLE.ROLE_ID =
T_ROLE_ADMIN.ROLE_ID WHERE T_ROLE_ADMIN.ROLE_ID = ?" parameters="1000" />
<output sql="SELECT * FROM T_ROLE JOIN T_ROLE_ADMIN ON T_ROLE.ROLE_ID
= T_ROLE_ADMIN.ROLE_ID WHERE T_ROLE_ADMIN.ROLE_ID = ?" parameters="1000" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_with_and_condition_and_lowercase_keyword">
+ <input sql="select * from t_account where amount=? and account_id=?"
parameters="1,2"/>
+ <output sql="select * from t_account_0 where amount=? and
account_id=?" parameters="1,2"/>
+ </rewrite-assertion>
</rewrite-assertions>
