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 cc4aa73b166 Fix COLLATE ClassCastException and Add select test sql
(#27196)
cc4aa73b166 is described below
commit cc4aa73b166e0f38bd38c40e646938ade0819250
Author: niu niu <[email protected]>
AuthorDate: Sat Jul 15 10:13:36 2023 +0800
Fix COLLATE ClassCastException and Add select test sql (#27196)
* Fix COLLATE ClassCastException for mysql
* Add select sql test
---
.../visitor/statement/MySQLStatementVisitor.java | 5 +
.../parser/src/main/resources/case/dml/select.xml | 323 +++++++++++++++++++++
.../parser/src/main/resources/case/dml/values.xml | 53 ++++
.../main/resources/sql/supported/dml/select.xml | 9 +
.../main/resources/sql/supported/dml/values.xml | 1 +
5 files changed, 391 insertions(+)
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 7a1f7d7fcad..1297843e20c 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
@@ -1728,6 +1728,11 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
result.setAlias(alias);
return result;
}
+ if (projection instanceof CollateExpression) {
+ ExpressionProjectionSegment result = new
ExpressionProjectionSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
getOriginalText(ctx.expr()), (CollateExpression) projection);
+ result.setAlias(alias);
+ return result;
+ }
LiteralExpressionSegment column = (LiteralExpressionSegment)
projection;
ExpressionProjectionSegment result = null == alias
? new ExpressionProjectionSegment(column.getStartIndex(),
column.getStopIndex(), String.valueOf(column.getLiterals()), column)
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 bf729eb7fe9..41108177060 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,329 @@
-->
<sql-parser-test-cases>
+ <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">
+ <expr>
+ <in-expression start-index="7" stop-index="62">
+ <left>
+ <collate-expression start-index="7"
stop-index="35">
+ <collate-name>
+ <literal-expression start-index="18"
stop-index="35" value="latin1_bin" />
+ </collate-name>
+ </collate-expression>
+ </left>
+ <right>
+ <list-expression start-index="40" stop-index="62">
+ <items>
+ <literal-expression start-index="41"
stop-index="50" value="latin1'a" />
+ </items>
+ <items>
+ <literal-expression start-index="52"
stop-index="61" value="latin1'b" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_default_str">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="14" stop-index="15" />
+ </from>
+ <where start-index="17" stop-index="41">
+ <expr>
+ <binary-operation-expression text="str <> default(str)"
start-index="23" stop-index="41">
+ <left>
+ <column name="str" start-index="23" stop-index="25" />
+ </left>
+ <right>
+ <function start-index="30" stop-index="41"
text="default(str)" function-name="default">
+ <parameter>
+ <column name="str" start-index="38"
stop-index="40" />
+ </parameter>
+ </function>
+ </right>
+ <operator><></operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_subquery_excpet">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <subquery-table alias="a">
+ <subquery start-index="14" stop-index="67">
+ <select>
+ <projections start-index="22" stop-index="22">
+ <shorthand-projection start-index="22"
stop-index="22" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="29"
stop-index="30" />
+ </from>
+ <combine combine-type="EXCEPT" start-index="32"
stop-index="58">
+ <left>
+ <projections start-index="22" stop-index="22">
+ <shorthand-projection start-index="22"
stop-index="22" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="29"
stop-index="30" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="50" stop-index="50">
+ <shorthand-projection start-index="50"
stop-index="50" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="57"
stop-index="58" />
+ </from>
+ </right>
+ </combine>
+ <limit start-index="60" stop-index="66">
+ <row-count value="2" start-index="66"
stop-index="66" />
+ </limit>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_point_function_with_in">
+ <projections start-index="7" stop-index="38">
+ <expression-projection alias="res" text="point(1,1) IN
('1',1,'1')" start-index="7" stop-index="38">
+ <expr>
+ <in-expression start-index="7" stop-index="31">
+ <left>
+ <function start-index="7" stop-index="16"
text="point(1,1)" function-name="point">
+ <parameter>
+ <literal-expression start-index="13"
stop-index="13" value="1" />
+ </parameter>
+ <parameter>
+ <literal-expression start-index="15"
stop-index="15" value="1" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <list-expression start-index="21" stop-index="31">
+ <items>
+ <literal-expression start-index="22"
stop-index="24" value="1" />
+ </items>
+ <items>
+ <literal-expression start-index="26"
stop-index="26" value="1" />
+ </items>
+ <items>
+ <literal-expression start-index="28"
stop-index="30" value="1" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_in">
+ <projections start-index="7" stop-index="48">
+ <column-projection name="b" start-index="7" stop-index="7" />
+ <expression-projection text="b IN ('20161213')" start-index="10"
stop-index="26">
+ <expr>
+ <in-expression start-index="10" stop-index="26">
+ <left>
+ <column name="b" start-index="10" stop-index="10"
/>
+ </left>
+ <right>
+ <list-expression start-index="15" stop-index="26">
+ <items>
+ <literal-expression start-index="16"
stop-index="25" value="20161213" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection text="b in ('20161213', 0)"
start-index="29" stop-index="48">
+ <expr>
+ <in-expression start-index="29" stop-index="48">
+ <left>
+ <column name="b" start-index="29" stop-index="29"
/>
+ </left>
+ <right>
+ <list-expression start-index="34" stop-index="48">
+ <items>
+ <literal-expression start-index="35"
stop-index="44" value="20161213" />
+ </items>
+ <items>
+ <literal-expression start-index="47"
stop-index="47" value="0" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t2" start-index="55" stop-index="56" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_st_geom_from_text">
+ <projections start-index="7" stop-index="67">
+ <expression-projection alias="result"
text="ST_GeomFromText('POINT(0 0)') IN (SELECT b FROM t1)" start-index="7"
stop-index="67">
+ <expr>
+ <in-expression start-index="7" stop-index="57">
+ <left>
+ <function start-index="7" stop-index="35"
text="ST_GeomFromText('POINT(0 0)')" function-name="ST_GeomFromText">
+ <parameter>
+ <literal-expression start-index="23"
stop-index="34" value="POINT(0 0)" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <subquery start-index="40" stop-index="57">
+ <select>
+ <projections start-index="48"
stop-index="48">
+ <column-projection name="b"
start-index="48" stop-index="48" />
+ </projections>
+ <from>
+ <simple-table name="t1"
start-index="55" stop-index="56" />
+ </from>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_st_aswkb_st_geom_form_text">
+ <projections start-index="7" stop-index="77">
+ <expression-projection alias="result"
text="ST_AsWKB(ST_GeomFromText('POINT(0 0)')) IN (SELECT b FROM t1)"
start-index="7" stop-index="77">
+ <expr>
+ <in-expression start-index="7" stop-index="67">
+ <left>
+ <function start-index="7" stop-index="45"
text="ST_AsWKB(ST_GeomFromText('POINT(0 0)'))" function-name="ST_AsWKB">
+ <parameter>
+ <function start-index="16" stop-index="44"
text="ST_GeomFromText('POINT(0 0)')" function-name="ST_GeomFromText">
+ <parameter>
+ <literal-expression
start-index="32" stop-index="43" value="POINT(0 0)" />
+ </parameter>
+ </function>
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <subquery start-index="50" stop-index="67">
+ <select>
+ <projections start-index="58"
stop-index="58">
+ <column-projection name="b"
start-index="58" stop-index="58" />
+ </projections>
+ <from>
+ <simple-table name="t1"
start-index="65" stop-index="66" />
+ </from>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_null_in_subquery">
+ <projections start-index="7" stop-index="94">
+ <expression-projection text="NULL IN(SELECT (f1 between 0 and 1)
FROM (SELECT f1 FROM t WHERE (@b:=NULL) - f2) as dt)" start-index="7"
stop-index="94">
+ <expr>
+ <in-expression start-index="7" stop-index="94">
+ <left>
+ <literal-expression start-index="7"
stop-index="10" value="null" />
+ </left>
+ <right>
+ <subquery start-index="14" stop-index="94">
+ <select>
+ <projections start-index="22"
stop-index="41">
+ <expression-projection text="f1
between 0 and 1" start-index="22" stop-index="41">
+ <expr>
+ <between-expression
start-index="23" stop-index="40">
+ <left>
+ <column name="f1"
start-index="23" stop-index="24" />
+ </left>
+ <between-expr>
+ <literal-expression
value="0" start-index="34" stop-index="34" />
+ </between-expr>
+ <and-expr>
+ <literal-expression
value="1" start-index="40" stop-index="40" />
+ </and-expr>
+ </between-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <subquery-table alias="dt">
+ <subquery>
+ <select>
+ <projections
start-index="56" stop-index="57">
+ <column-projection
name="f1" start-index="56" stop-index="57" />
+ </projections>
+ <from>
+ <simple-table name="t"
start-index="64" stop-index="64" />
+ </from>
+ <where start-index="66"
stop-index="86">
+ <expr>
+
<binary-operation-expression text="(@b:=NULL) - f2" start-index="72"
stop-index="86">
+ <left>
+
<binary-operation-expression text="@b:=NULL" start-index="73" stop-index="80">
+ <left>
+
<variable-segment start-index="73" stop-index="74" variable="b" />
+ </left>
+
<operator>:=</operator>
+ <right>
+
<literal-expression value="null" start-index="77" stop-index="80" />
+
</right>
+
</binary-operation-expression>
+ </left>
+ <right>
+ <column
name="f2" start-index="85" stop-index="86" />
+ </right>
+
<operator>-</operator>
+
</binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_collate">
+ <projections start-index="7" stop-index="58">
+ <expression-projection text="NAME_CONST('var', 'value') COLLATE
latin1_general_cs" start-index="7" stop-index="58">
+ <expr>
+ <collate-expression start-index="7" stop-index="58">
+ <collate-name>
+ <literal-expression start-index="34"
stop-index="58" value="latin1_general_cs" />
+ </collate-name>
+ </collate-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_with_hex_function">
<projections start-index="7" stop-index="50">
<expression-projection text="HEX(_binary 0x0003 << (_binary
0x38 | 0x38))" start-index="7" stop-index="50">
diff --git a/test/it/parser/src/main/resources/case/dml/values.xml
b/test/it/parser/src/main/resources/case/dml/values.xml
index 0d7b2e5d1cc..67df700095b 100644
--- a/test/it/parser/src/main/resources/case/dml/values.xml
+++ b/test/it/parser/src/main/resources/case/dml/values.xml
@@ -18,6 +18,59 @@
<sql-parser-test-cases>
+ <select sql-case-id="values_with_regexp_replace">
+ <projections start-index="7" stop-index="41">
+ <expression-projection text="REGEXP_REPLACE(e, 'pattern', 'xyz')"
start-index="7" stop-index="41">
+ <expr>
+ <function start-index="7" stop-index="41"
text="REGEXP_REPLACE(e, 'pattern', 'xyz')" function-name="REGEXP_REPLACE">
+ <parameter>
+ <column name="e" start-index="22" stop-index="22"
/>
+ </parameter>
+ <parameter>
+ <literal-expression start-index="25"
stop-index="33" value="pattern" />
+ </parameter>
+ <parameter>
+ <literal-expression start-index="36"
stop-index="40" value="xyz" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <subquery-table alias="v">
+ <subquery>
+ <select>
+ <projections start-index="48" stop-index="105">
+ <expression-projection text="VALUES ROW('Find
pattern'), ROW(NULL), ROW('Find pattern')" start-index="48" stop-index="105">
+ <expr>
+ <values-expression>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression
value="Find pattern" start-index="59" stop-index="72" />
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression
value="null" start-index="80" stop-index="83" />
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression
value="Find pattern" start-index="91" stop-index="104" />
+ </assignment-value>
+ </value>
+ </values>
+ </values-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ </select>
+
<select sql-case-id="values_with_row">
<projections start-index="0" stop-index="14">
<expression-projection text="values ROW(1,2)" start-index="0"
stop-index="14">
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 6b23c80752c..c4cc9d9520e 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,15 @@
-->
<sql-cases>
+ <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" />
+ <sql-case id="select_point_function_with_in" value="SELECT point(1,1) IN
('1',1,'1') AS res" db-types="MySQL" />
+ <sql-case id="select_with_in" value="SELECT b, b IN ('20161213'), b in
('20161213', 0) FROM t2" db-types="MySQL" />
+ <sql-case id="select_with_st_geom_from_text" value="SELECT
ST_GeomFromText('POINT(0 0)') IN (SELECT b FROM t1) AS result" db-types="MySQL"
/>
+ <sql-case id="select_with_st_aswkb_st_geom_form_text" value="SELECT
ST_AsWKB(ST_GeomFromText('POINT(0 0)')) IN (SELECT b FROM t1) AS result"
db-types="MySQL" />
+ <sql-case id="select_null_in_subquery" value="SELECT NULL IN(SELECT (f1
between 0 and 1) FROM (SELECT f1 FROM t WHERE (@b:=NULL) - f2) as dt)"
db-types="MySQL" />
+ <sql-case id="select_with_collate" value="SELECT NAME_CONST('var',
'value') COLLATE latin1_general_cs" db-types="MySQL" />
<sql-case id="select_with_hex_function" value="SELECT HEX(_binary 0x0003
<< (_binary 0x38 | 0x38))" db-types="MySQL" />
<sql-case id="select_distinct_with_grouping_function" value="SELECT
DISTINCT f1 FROM t1 GROUP BY f1 WITH ROLLUP ORDER BY f1,
ANY_VALUE(GROUPING(f1))" db-types="MySQL" />
<sql-case id="select_convert_function1" value="SELECT CONVERT(TIMESTAMP
"2004-01-22 21:45:33" USING latin1)" db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/values.xml
b/test/it/parser/src/main/resources/sql/supported/dml/values.xml
index bb31b244693..eb6d76e1164 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/values.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/values.xml
@@ -17,6 +17,7 @@
-->
<sql-cases>
+ <sql-case id="values_with_regexp_replace" value="SELECT REGEXP_REPLACE(e,
'pattern', 'xyz')FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find
pattern')) AS v(e)" db-types="MySQL" />
<sql-case id="values_with_row" value="values ROW(1,2)" db-types="MySQL" />
<sql-case id="values_with_order_limit" value="VALUES ROW(1,-2,3),
ROW(5,7,9), ROW(4,6,8) ORDER BY column_1 desc , column_0 desc limit 10"
db-types="MySQL" />
<sql-case id="values_with_select" value="values row((select 1), 2)"
db-types="MySQL" />
