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 f2f3ccc7522 Enhance select query statements (#27299)
f2f3ccc7522 is described below
commit f2f3ccc752227e672fdb47e424fdcbd9d77dcf8c
Author: niu niu <[email protected]>
AuthorDate: Fri Jul 21 20:11:11 2023 +0800
Enhance select query statements (#27299)
* Fix position function parse ClassCastException
* Support LATERAL query parse
* Add other sql test
* Format g4 file
---
.../src/main/antlr4/imports/mysql/DMLStatement.g4 | 2 +-
.../visitor/statement/MySQLStatementVisitor.java | 4 +-
.../main/resources/case/dml/select-expression.xml | 44 ++++++++++++++
.../main/resources/case/dml/select-sub-query.xml | 64 ++++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 69 ++++++++++++++++++++++
.../sql/supported/dml/select-expression.xml | 1 +
.../sql/supported/dml/select-sub-query.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 1 +
8 files changed, 183 insertions(+), 3 deletions(-)
diff --git
a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
index 37b063c8ea9..0b5b173348b 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
@@ -287,7 +287,7 @@ tableReference
;
tableFactor
- : tableName partitionNames? (AS? alias)? indexHintList? | subquery AS?
alias (LP_ columnNames RP_)? | LP_ tableReferences RP_
+ : tableName partitionNames? (AS? alias)? indexHintList? | subquery AS?
alias (LP_ columnNames RP_)? | LATERAL subquery AS? alias (LP_ columnNames
RP_)? | LP_ tableReferences RP_
;
partitionNames
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 29eb2969dfd..98bdfb88861 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
@@ -1054,8 +1054,8 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
public final ASTNode visitPositionFunction(final PositionFunctionContext
ctx) {
calculateParameterCount(ctx.expr());
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.POSITION().getText(), getOriginalText(ctx));
- result.getParameters().add((LiteralExpressionSegment)
visit(ctx.expr(0)));
- result.getParameters().add((LiteralExpressionSegment)
visit(ctx.expr(1)));
+ result.getParameters().add((ExpressionSegment) visit(ctx.expr(0)));
+ result.getParameters().add((ExpressionSegment) visit(ctx.expr(1)));
return result;
}
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 96149009c33..eb66306fc9c 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
@@ -17,6 +17,50 @@
-->
<sql-parser-test-cases>
+ <select sql-case-id="select_with_performance_schema_global_status">
+ <from>
+ <simple-table name="global_status" start-index="24"
stop-index="55">
+ <owner name="performance_schema" start-index="24"
stop-index="41" />
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="17">
+ <expression-projection text="1" alias="STATUS" start-index="7"
stop-index="17">
+ <expr>
+ <literal-expression start-index="7" stop-index="7"
value="1" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <where start-index="57" stop-index="165">
+ <expr>
+ <binary-operation-expression start-index="63" stop-index="165">
+ <left>
+ <binary-operation-expression start-index="63"
stop-index="108">
+ <left>
+ <column name="VARIABLE_NAME" start-index="63"
stop-index="75" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression start-index="78"
stop-index="108" value="MAX_EXECUTION_TIME_SET_FAILED" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <binary-operation-expression start-index="114"
stop-index="165">
+ <left>
+ <function function-name="CONVERT"
start-index="114" stop-index="146" text="CONVERT(VARIABLE_VALUE, UNSIGNED)" />
+ </left>
+ <operator>></operator>
+ <right>
+ <variable-segment start-index="150"
stop-index="165" variable="time_set_failed" />
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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/case/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
index 677839f25cb..27226bb04a4 100644
--- a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
@@ -17,6 +17,70 @@
-->
<sql-parser-test-cases>
+ <select sql-case-id="select_with_lateral">
+ <from>
+ <join-table start-index="14" stop-index="94" join-type="COMMA">
+ <left>
+ <join-table start-index="14" stop-index="54"
join-type="COMMA">
+ <left>
+ <simple-table start-index="14" stop-index="15"
name="t1" />
+ </left>
+ <right>
+ <subquery-table alias="dt1">
+ <subquery start-index="26" stop-index="47">
+ <select>
+ <projections distinct-row="true"
start-index="43" stop-index="46">
+ <column-projection
start-index="43" stop-index="46" name="x">
+ <owner start-index="43"
stop-index="44" name="t1" />
+ </column-projection>
+ </projections>
+ </select>
+ </subquery>
+ </subquery-table>
+ </right>
+ </join-table>
+ </left>
+ <right>
+ <subquery-table alias="dt2">
+ <subquery start-index="65" stop-index="87">
+ <select>
+ <projections distinct-row="true"
start-index="82" stop-index="86">
+ <column-projection start-index="82"
stop-index="86" name="x">
+ <owner start-index="82"
stop-index="84" name="dt1" />
+ </column-projection>
+ </projections>
+ </select>
+ </subquery>
+ </subquery-table>
+ </right>
+ </join-table>
+ </from>
+ <projections start-index="7" stop-index="7">
+ <expression-projection start-index="7" stop-index="7" text="1">
+ <expr>
+ <literal-expression start-index="7" stop-index="7"
value="1" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <where start-index="96" stop-index="114">
+ <expr>
+ <binary-operation-expression start-index="102"
stop-index="114">
+ <left>
+ <column start-index="102" stop-index="106" name="x">
+ <owner start-index="102" stop-index="104"
name="dt1" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column start-index="110" stop-index="114" name="x">
+ <owner start-index="110" stop-index="112"
name="dt2" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_sub_query_with_project">
<from>
<simple-table name="t_order" start-index="40" stop-index="46" />
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index b0d6b6473fa..2ffd03409b2 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -17,6 +17,75 @@
-->
<sql-parser-test-cases>
+ <select sql-case-id="select_position_function">
+ <projections start-index="7" stop-index="97">
+ <expression-projection text="position("0" in
"baaa" in (1))" start-index="7" stop-index="36">
+ <expr>
+ <function start-index="7" stop-index="36"
text="position("0" in "baaa" in (1))"
function-name="position">
+ <parameter>
+ <literal-expression start-index="16"
stop-index="18" value="0" />
+ </parameter>
+ <parameter>
+ <in-expression start-index="23" stop-index="35">
+ <left>
+ <literal-expression start-index="23"
stop-index="28" value="baaa" />
+ </left>
+ <right>
+ <list-expression start-index="33"
stop-index="35">
+ <items>
+ <literal-expression
start-index="34" stop-index="34" value="1" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="position("0" in
"1" in (1,2,3))" start-index="38" stop-index="68">
+ <expr>
+ <function start-index="38" stop-index="68"
text="position("0" in "1" in (1,2,3))"
function-name="position">
+ <parameter>
+ <literal-expression start-index="47"
stop-index="49" value="0" />
+ </parameter>
+ <parameter>
+ <in-expression start-index="54" stop-index="67">
+ <left>
+ <literal-expression start-index="54"
stop-index="56" value="1" />
+ </left>
+ <right>
+ <list-expression start-index="61"
stop-index="67">
+ <items>
+ <literal-expression
start-index="62" stop-index="62" value="1" />
+ </items>
+ <items>
+ <literal-expression
start-index="64" stop-index="64" value="2" />
+ </items>
+ <items>
+ <literal-expression
start-index="66" stop-index="66" value="3" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="position("sql" in
("mysql"))" start-index="70" stop-index="97">
+ <expr>
+ <function start-index="70" stop-index="97"
text="position("sql" in ("mysql"))"
function-name="position">
+ <parameter>
+ <literal-expression start-index="79"
stop-index="83" value="sql" />
+ </parameter>
+ <parameter>
+ <literal-expression start-index="89"
stop-index="95" value="mysql" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_with_latin1">
<projections start-index="7" stop-index="62">
<expression-projection text="_latin1'B' collate latin1_bin in
(_latin1'a',_latin1'b')" start-index="7" stop-index="62">
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 0fa6f800fa2..7b6ca996066 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
@@ -17,6 +17,7 @@
-->
<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_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" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
index 7a9e989dd43..d833661a4f4 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -17,6 +17,7 @@
-->
<sql-cases>
+ <sql-case id="select_with_lateral" value="SELECT 1 FROM t1, LATERAL
(SELECT DISTINCT t1.x) AS dt1, LATERAL (SELECT DISTINCT dt1.x) AS dt2 WHERE
dt1.x = dt2.x" db-types="MySQL" />
<sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL,openGauss,
SQLServer" />
<sql-case id="select_sub_query_with_table" value="SELECT t.* FROM (SELECT
* FROM t_order WHERE order_id IN (?, ?)) t" />
<sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL,openGauss" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 394896f2b1d..02c35b97568 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -17,6 +17,7 @@
-->
<sql-cases>
+ <sql-case id="select_position_function" value="select
position("0" in "baaa" in (1)),position("0" in
"1" in (1,2,3)),position("sql" in ("mysql"))"
db-types="MySQL" />
<sql-case id="select_with_latin1" value="select _latin1'B' collate
latin1_bin in (_latin1'a',_latin1'b')" db-types="MySQL" />
<sql-case id="select_with_default_str" value="select * from t1 where str
<> default(str)" db-types="MySQL" />
<sql-case id="select_subquery_excpet" value="select * from (select * from
t1 except all select * from t1 limit 2) a" db-types="MySQL" />
