You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2023/07/22 23:39:56 UTC
[shardingsphere] branch master updated: Change CASE WHEN parse for mysql (#27288)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 d94a16c9781 Change CASE WHEN parse for mysql (#27288)
d94a16c9781 is described below
commit d94a16c97819a98cf047a30689b1e7ed1413c6f4
Author: niu niu <zi...@aliyun.com>
AuthorDate: Sun Jul 23 07:39:48 2023 +0800
Change CASE WHEN parse for mysql (#27288)
* Change CASE WHEN parse for mysql
* Add test sql
---
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 2 +-
.../visitor/statement/MySQLStatementVisitor.java | 2 +-
.../parser/src/main/resources/case/dml/insert.xml | 12 ++
.../main/resources/case/dml/select-expression.xml | 132 +++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../sql/supported/dml/select-expression.xml | 5 +
6 files changed, 152 insertions(+), 2 deletions(-)
diff --git a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4 b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
index 26ce28cf41f..a75bac4296f 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
@@ -1153,7 +1153,7 @@ matchSearchModifier
;
caseExpression
- : CASE simpleExpr? caseWhen+ caseElse? END
+ : CASE expr? caseWhen+ caseElse? END
;
datetimeExpr
diff --git a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
index 23f41da1f69..b5cc8c4d91a 100644
--- a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
+++ b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -1191,7 +1191,7 @@ public abstract class MySQLStatementVisitor extends MySQLStatementBaseVisitor<AS
whenExprs.add((ExpressionSegment) visit(each.expr(0)));
thenExprs.add((ExpressionSegment) visit(each.expr(1)));
}
- ExpressionSegment caseExpr = null == ctx.simpleExpr() ? null : (ExpressionSegment) visit(ctx.simpleExpr());
+ ExpressionSegment caseExpr = null == ctx.expr() ? null : (ExpressionSegment) visit(ctx.expr());
ExpressionSegment elseExpr = null == ctx.caseElse() ? null : (ExpressionSegment) visit(ctx.caseElse().expr());
return new CaseWhenExpression(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), caseExpr, whenExprs, thenExprs, elseExpr);
}
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml b/test/it/parser/src/main/resources/case/dml/insert.xml
index f8d38ea214c..550c9c8dd94 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -17,6 +17,18 @@
-->
<sql-parser-test-cases>
+ <insert sql-case-id="insert_into_values">
+ <table name="test_nested" start-index="12" stop-index="22" />
+ <columns start-index="23" stop-index="23" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="31" stop-index="31" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
<insert sql-case-id="insert_with_all_placeholders" parameters="1, 1, 'init'">
<table name="t_order" start-index="12" stop-index="18" />
<columns start-index="20" stop-index="46">
diff --git a/test/it/parser/src/main/resources/case/dml/select-expression.xml b/test/it/parser/src/main/resources/case/dml/select-expression.xml
index eb66306fc9c..c3caac0caa0 100644
--- a/test/it/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-expression.xml
@@ -61,6 +61,138 @@
</where>
</select>
+ <select sql-case-id="select_udf_function_mysqltest1_f1">
+ <projections start-index="7" stop-index="21">
+ <expression-projection text="mysqltest1.f1()" start-index="7" stop-index="21">
+ <function function-name="mysqltest1.f1()" alias="creation_date" start-index="7" stop-index="21" text="mysqltest1.f1()" />
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_case_when_with_mul_condition">
+ <projections start-index="7" stop-index="52">
+ <expression-projection text="case 1*0 when "a" then "true" else "false" END" start-index="7" stop-index="52">
+ <expr>
+ <case-when-expression>
+ <case-expr start-index="12" stop-index="14">
+ <binary-operation-expression start-index="12" stop-index="14">
+ <left>
+ <literal-expression value="1" start-index="12" stop-index="12" />
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="0" start-index="14" stop-index="14" />
+ </right>
+ </binary-operation-expression>
+ </case-expr>
+ <when-exprs>
+ <literal-expression value="a" start-index="21" stop-index="23" />
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="true" start-index="30" stop-index="35" />
+ </then-exprs>
+ <else-expr>
+ <literal-expression value="false" start-index="42" stop-index="48" />
+ </else-expr>
+ </case-when-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_case_when_with_sub_condition">
+ <projections start-index="7" stop-index="52">
+ <expression-projection text="case 1-0 when "a" then "true" else "false" END" start-index="7" stop-index="52">
+ <expr>
+ <case-when-expression>
+ <case-expr start-index="12" stop-index="14">
+ <binary-operation-expression start-index="12" stop-index="14">
+ <left>
+ <literal-expression value="1" start-index="12" stop-index="12" />
+ </left>
+ <operator>-</operator>
+ <right>
+ <literal-expression value="0" start-index="14" stop-index="14" />
+ </right>
+ </binary-operation-expression>
+ </case-expr>
+ <when-exprs>
+ <literal-expression value="a" start-index="21" stop-index="23" />
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="true" start-index="30" stop-index="35" />
+ </then-exprs>
+ <else-expr>
+ <literal-expression value="false" start-index="42" stop-index="48" />
+ </else-expr>
+ </case-when-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_case_when_with_add_condition">
+ <projections start-index="7" stop-index="52">
+ <expression-projection text="case 1+0 when "a" then "true" else "false" END" start-index="7" stop-index="52">
+ <expr>
+ <case-when-expression>
+ <case-expr start-index="12" stop-index="14">
+ <binary-operation-expression start-index="12" stop-index="14">
+ <left>
+ <literal-expression value="1" start-index="12" stop-index="12" />
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="0" start-index="14" stop-index="14" />
+ </right>
+ </binary-operation-expression>
+ </case-expr>
+ <when-exprs>
+ <literal-expression value="a" start-index="21" stop-index="23" />
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="true" start-index="30" stop-index="35" />
+ </then-exprs>
+ <else-expr>
+ <literal-expression value="false" start-index="42" stop-index="48" />
+ </else-expr>
+ </case-when-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_case_when_with_div_condition">
+ <projections start-index="7" stop-index="52">
+ <expression-projection text="case 1/0 when "a" then "true" else "false" END" start-index="7" stop-index="52">
+ <expr>
+ <case-when-expression>
+ <case-expr start-index="12" stop-index="14">
+ <binary-operation-expression start-index="12" stop-index="14">
+ <left>
+ <literal-expression value="1" start-index="12" stop-index="12" />
+ </left>
+ <operator>/</operator>
+ <right>
+ <literal-expression value="0" start-index="14" stop-index="14" />
+ </right>
+ </binary-operation-expression>
+ </case-expr>
+ <when-exprs>
+ <literal-expression value="a" start-index="21" stop-index="23" />
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="true" start-index="30" stop-index="35" />
+ </then-exprs>
+ <else-expr>
+ <literal-expression value="false" start-index="42" stop-index="48" />
+ </else-expr>
+ </case-when-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_with_expression">
<from>
<simple-table name="t_order" alias="o" start-index="38" stop-index="49" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index 36eebb71a37..c5cad9f5667 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -17,6 +17,7 @@
-->
<sql-cases>
+ <sql-case id="insert_into_values" value="insert into test_nested values(1)" db-types="MySQL" />
<sql-case id="insert_with_all_placeholders" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?)" />
<sql-case id="insert_with_historical_type_cast_syntax" value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?::int4, ?::text)" db-types="PostgreSQL,openGauss" />
<sql-case id="insert_with_now_function" value="INSERT INTO t_order_item (item_id, order_id, user_id, status, creation_date) VALUES (?, ?, ?, 'insert', now())" db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 7b6ca996066..304ba8edeca 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -18,6 +18,11 @@
<sql-cases>
<sql-case id="select_with_performance_schema_global_status" value="SELECT 1 AS STATUS FROM performance_schema.global_status WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED' AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_set_failed" db-types="MySQL" />
+ <sql-case id="select_udf_function_mysqltest1_f1" value="select mysqltest1.f1()" db-types="MySQL" />
+ <sql-case id="select_case_when_with_mul_condition" value="select case 1*0 when "a" then "true" else "false" END" db-types="MySQL" />
+ <sql-case id="select_case_when_with_sub_condition" value="select case 1-0 when "a" then "true" else "false" END" db-types="MySQL" />
+ <sql-case id="select_case_when_with_add_condition" value="select case 1+0 when "a" then "true" else "false" END" db-types="MySQL" />
+ <sql-case id="select_case_when_with_div_condition" value="select case 1/0 when "a" then "true" else "false" END" db-types="MySQL" />
<sql-case id="select_with_expression" value="SELECT o.order_id + 1 * 2 as exp FROM t_order AS o ORDER BY o.order_id" db-types="MySQL, H2, SQL92, SQLServer" />
<sql-case id="select_with_expression_for_postgresql" value="SELECT o.order_id + 1 * 2 as exp FROM t_order AS o ORDER BY o.order_id" db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_date_function" value="SELECT DATE(i.creation_date) AS creation_date FROM `t_order_item` AS i ORDER BY DATE(i.creation_date) DESC" db-types="MySQL" />