This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 191d943660f Add SQL parser test cases for MySQL (#37837)
191d943660f is described below
commit 191d943660fd0e60f36fffee551872cfbc862cff
Author: Liang Zhang <[email protected]>
AuthorDate: Sun Jan 25 18:33:12 2026 +0800
Add SQL parser test cases for MySQL (#37837)
---
.../main/resources/case/dml/select-expression.xml | 43 ++++++++++++++++++++++
.../src/main/resources/case/dml/select-with.xml | 31 ++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 21 +++++++++++
.../parser/src/main/resources/case/dml/values.xml | 29 +++++++++++++++
.../sql/supported/dml/select-expression.xml | 2 +
.../resources/sql/supported/dml/select-with.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 2 +
.../main/resources/sql/supported/dml/values.xml | 1 +
8 files changed, 130 insertions(+)
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 8c028a3921a..a3db758ceca 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
@@ -606,6 +606,27 @@
</where>
</select>
+ <select sql-case-id="select_concat_pipe_with_table">
+ <projections start-index="7" stop-index="16">
+ <expression-projection text="'a' || 'b'" start-index="7"
stop-index="16">
+ <expr>
+ <binary-operation-expression start-index="7"
stop-index="16">
+ <left>
+ <literal-expression value="a" start-index="7"
stop-index="9" />
+ </left>
+ <operator>||</operator>
+ <right>
+ <literal-expression value="b" start-index="14"
stop-index="16" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="23" stop-index="29" />
+ </from>
+ </select>
+
<select sql-case-id="select_where_with_expr_with_xor" parameters="1,2">
<from start-index="14" stop-index="20">
<simple-table name="t_order" start-index="14" stop-index="20" />
@@ -2732,6 +2753,28 @@
</where>
</select>
+ <select sql-case-id="select_returning_expression">
+ <projections start-index="7" stop-index="33">
+ <expression-projection text="'$.a'RETURNINGVARCHAR"
start-index="7" stop-index="33">
+ <expr>
+ <list-expression start-index="7" stop-index="33">
+ <items>
+ <literal-expression value="'$.a'" start-index="7"
stop-index="11" />
+ </items>
+ <items>
+ <literal-expression value="RETURNING"
start-index="13" stop-index="21" />
+ </items>
+ <items>
+ <data-type value="VARCHAR" start-index="23"
stop-index="33">
+ <data-type-length precision="10"
start-index="31" stop-index="32" />
+ </data-type>
+ </items>
+ </list-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_age_for_postgres">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
diff --git a/test/it/parser/src/main/resources/case/dml/select-with.xml
b/test/it/parser/src/main/resources/case/dml/select-with.xml
index 54872fa50b1..e2e099a66e6 100644
--- a/test/it/parser/src/main/resources/case/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-with.xml
@@ -816,6 +816,37 @@
</from>
</select>
+ <select sql-case-id="select_with_columns_cte">
+ <with start-index="0" stop-index="28">
+ <common-table-expression name="cte" start-index="5"
stop-index="28">
+ <column name="a" start-index="9" stop-index="9" />
+ <column name="b" start-index="11" stop-index="11" />
+ <subquery-expression start-index="5" stop-index="28">
+ <select>
+ <projections start-index="25" stop-index="27">
+ <expression-projection text="1" start-index="25"
stop-index="25">
+ <expr>
+ <literal-expression value="1"
start-index="25" stop-index="25" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="2" start-index="27"
stop-index="27">
+ <expr>
+ <literal-expression value="2"
start-index="27" stop-index="27" />
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="37" stop-index="37">
+ <shorthand-projection start-index="37" stop-index="37" />
+ </projections>
+ <from>
+ <simple-table name="cte" start-index="44" stop-index="46" />
+ </from>
+ </select>
+
<select sql-case-id="select_with_recursive_firebird">
<with start-index="0" stop-index="33">
<common-table-expression name="t" start-index="15" stop-index="33">
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 d776de59616..021f9748252 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -3669,6 +3669,27 @@
</where>
</select>
+ <select sql-case-id="select_order_by_position_mysql">
+ <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>
+ <order-by>
+ <index-item index="1" order-direction="DESC" start-index="31"
stop-index="31" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_table_statement">
+ <projections start-index="0" stop-index="0">
+ <shorthand-projection start-index="0" stop-index="0" />
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="0" stop-index="12" />
+ </from>
+ </select>
+
<select sql-case-id="select_with_left_function">
<from>
<simple-table name="t_order_item" start-index="44" stop-index="55"
/>
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 7de39a26cb7..0ad116d74da 100644
--- a/test/it/parser/src/main/resources/case/dml/values.xml
+++ b/test/it/parser/src/main/resources/case/dml/values.xml
@@ -93,6 +93,35 @@
</projections>
</select>
+ <select sql-case-id="values_row_two_rows_mysql">
+ <projections start-index="0" stop-index="24">
+ <expression-projection text="VALUES ROW(1,2), ROW(3,4)"
start-index="0" stop-index="24">
+ <expr>
+ <values-expression>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1"
start-index="11" stop-index="11" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="2"
start-index="13" stop-index="13" />
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression value="3"
start-index="21" stop-index="21" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="4"
start-index="23" stop-index="23" />
+ </assignment-value>
+ </value>
+ </values>
+ </values-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="values_with_order_limit">
<projections start-index="0" stop-index="41">
<expression-projection text="VALUES ROW(1,-2,3), ROW(5,7,9),
ROW(4,6,8)" start-index="0" stop-index="41">
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 1c3a4778fff..2746bbe4690 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
@@ -32,6 +32,7 @@
<sql-case id="select_with_case_expression" value="select t.*,o.item_id as
item_id,(case when t.status = 'init' then '已启用' when t.status = 'failed' then
'已停用' end) as stateName from t_order t left join t_order_item as o on
o.order_id =t.order_id where t.order_id=1000 limit 1" db-types="MySQL,H2" />
<sql-case id="select_where_with_expr_with_or" value="SELECT * FROM t_order
WHERE t_order.order_id = ? OR ? = t_order.order_id" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_or_sign" value="SELECT * FROM
t_order WHERE t_order.order_id = ? || ? = t_order.order_id" db-types="MySQL" />
+ <sql-case id="select_concat_pipe_with_table" value="SELECT 'a' || 'b' FROM
t_order" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_xor" value="SELECT * FROM
t_order WHERE t_order.order_id = ? XOR ? = t_order.order_id"
db-types="MySQL,Presto" />
<sql-case id="select_where_with_expr_with_and" value="SELECT * FROM
t_order WHERE t_order.order_id = ? AND ? = t_order.order_id" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_and_or" value="SELECT * FROM
t_order WHERE t_order.order_id = ? AND ? = t_order.order_id OR t_order.status =
'failed' AND ? = t_order.order_id" />
@@ -104,6 +105,7 @@
<sql-case id="select_with_regular_function_for_sql92" value="SELECT A(1)
FROM t_order WHERE A(1) = 1" db-types="MySQL,Oracle,SQLServer,H2,SQL92" />
<sql-case id="select_with_regular_function_utc_timestamp" value="SELECT
TIMEDIFF(NOW(), UTC_TIMESTAMP())" db-types="MySQL" />
<sql-case id="select_with_collate_with_marker" value="SELECT * FROM
t_order WHERE order_id COLLATE ?" db-types="MySQL,Presto,Doris,Hive" />
+ <sql-case id="select_returning_expression" value="SELECT '$.a' RETURNING
VARCHAR(10)" db-types="MySQL" />
<sql-case id="select_safe_equal" value="SELECT 1 <=> 1"
db-types="Doris" />
<sql-case id="select_where_is_true_doris" value="SELECT * FROM t_order
WHERE flag IS TRUE" db-types="Doris" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
index b10501de506..20d2a9177b0 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
@@ -29,5 +29,6 @@
<sql-case id="select_with_recursive_union_all1" value="WITH RECURSIVE
DirectoryCTE as (SELECT * FROM table1 WHERE id = 1 AND project_id = 2 UNION ALL
SELECT * FROM project_file_catalog t INNER JOIN DirectoryCTE cte ON
t.project_id = cte.project_id AND t.parent_id = cte.id) SELECT * FROM
DirectoryCTE ORDER BY level" db-types="MySQL" />
<sql-case id="select_with_oracle_recursive_union_all1" value="WITH
DirectoryCTE as (SELECT table1.col1, table1.col2 FROM table1 WHERE id = 1 AND
project_id = 2 UNION ALL SELECT t.col1, t.col2 FROM project_file_catalog t
INNER JOIN DirectoryCTE cte ON t.project_id = cte.project_id AND t.parent_id =
cte.id) SELECT DirectoryCTE.col1, DirectoryCTE.col2 FROM DirectoryCTE ORDER BY
level" db-types="Oracle" />
<sql-case id="select_with_recursive_union_all2" value="WITH cte AS (SELECT
1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4) SELECT col1, col2 FROM cte"
db-types="MySQL" />
+ <sql-case id="select_with_columns_cte" value="WITH cte(a,b) AS (SELECT
1,2) SELECT * FROM cte" db-types="MySQL" />
<sql-case id="select_with_recursive_firebird" value="WITH RECURSIVE t(id)
AS (SELECT 1) SELECT id FROM t" db-types="Firebird" />
</sql-cases>
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 6e3432ad139..2ebd8ba2d9b 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
@@ -87,6 +87,8 @@
<sql-case id="select_special_function_nested" value="SELECT
sum(if(status=0, 1, 0)) func_status FROM t_order WHERE user_id = ? AND order_id
= ?" db-types="MySQL" />
<sql-case id="select_with_interval_function" value="SELECT
INTERVAL(status,1,5) func_status FROM t_order WHERE user_id = ? AND order_id =
?" db-types="MySQL" />
<sql-case id="select_with_left_function" value="SELECT CONCAT(LEFT(status,
7), 'test') FROM t_order_item WHERE user_id = 10" db-types="MySQL" />
+ <sql-case id="select_table_statement" value="TABLE t_order"
db-types="MySQL" />
+ <sql-case id="select_order_by_position_mysql" value="SELECT * FROM t_order
ORDER BY 1 DESC" db-types="MySQL" />
<sql-case id="select_database" value="SELECT DATABASE()" db-types="MySQL"
/>
<sql-case id="select_quarter" value="SELECT QUARTER('2008-04-01')"
db-types="MySQL" />
<sql-case id="select_with_mod_function" value="SELECT * FROM t_order WHERE
MOD(order_id, 1) = 1" 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 939d8916520..646dc0801d6 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
@@ -19,6 +19,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,Presto,Doris,Hive" />
+ <sql-case id="values_row_two_rows_mysql" value="VALUES ROW(1,2), ROW(3,4)"
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" />
</sql-cases>
