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 3a9f3073a56 Add SQL parser test cases for SQL92 (#37762)
3a9f3073a56 is described below
commit 3a9f3073a569214e4aa08d7ef8fe113bf513fbb4
Author: Liang Zhang <[email protected]>
AuthorDate: Sat Jan 17 20:11:14 2026 +0800
Add SQL parser test cases for SQL92 (#37762)
---
.../src/main/resources/case/ddl/drop-table.xml | 5 +++
.../main/resources/case/dml/select-aggregate.xml | 9 ++++++
.../src/main/resources/case/dml/select-join.xml | 16 ++++++++++
.../main/resources/case/dml/select-sub-query.xml | 32 +++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 36 ++++++++++++++++++++++
.../resources/sql/supported/ddl/drop-table.xml | 1 +
.../sql/supported/dml/select-aggregate.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 3 ++
9 files changed, 104 insertions(+)
diff --git a/test/it/parser/src/main/resources/case/ddl/drop-table.xml
b/test/it/parser/src/main/resources/case/ddl/drop-table.xml
index d0c6951a3b4..d34b2f46ab0 100644
--- a/test/it/parser/src/main/resources/case/ddl/drop-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/drop-table.xml
@@ -127,4 +127,9 @@
<drop-table sql-case-id="drop_table_sql92">
<table name="t_drop" start-index="11" stop-index="16" />
</drop-table>
+
+ <drop-table sql-case-id="drop_multiple_tables_sql92">
+ <table name="t_drop_a" start-index="11" stop-index="18" />
+ <table name="t_drop_b" start-index="21" stop-index="28" />
+ </drop-table>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
b/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
index 0a02b5b8722..bd95ab9fdc5 100644
--- a/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
@@ -50,6 +50,15 @@
</projections>
</select>
+ <select sql-case-id="select_count_star_alias_sql92">
+ <from>
+ <simple-table name="t_order" start-index="28" stop-index="34" />
+ </from>
+ <projections start-index="7" stop-index="21">
+ <aggregation-projection type="COUNT" alias="cnt"
expression="COUNT(*)" start-index="7" stop-index="14" />
+ </projections>
+ </select>
+
<select sql-case-id="select_count_with_sub">
<from>
<simple-table name="t_order" start-index="37" stop-index="43" />
diff --git a/test/it/parser/src/main/resources/case/dml/select-join.xml
b/test/it/parser/src/main/resources/case/dml/select-join.xml
index 9f6973a59e2..5e905a862ee 100644
--- a/test/it/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-join.xml
@@ -3629,6 +3629,22 @@
</from>
</select>
+ <select sql-case-id="select_parenthesized_comma_join_sql92">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="15"
stop-index="23" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i"
start-index="26" stop-index="39" />
+ </right>
+ </join-table>
+ </from>
+ </select>
+
<select sql-case-id="select_cross_join_sql92">
<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-sub-query.xml
b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
index cc833386ff1..c80468a6426 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
@@ -1026,6 +1026,38 @@
</where>
</select>
+ <select sql-case-id="select_in_subquery_sql92">
+ <projections start-index="7" stop-index="14">
+ <column-projection name="order_id" start-index="7" stop-index="14"
/>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="21" stop-index="27" />
+ </from>
+ <where start-index="29" stop-index="56">
+ <expr>
+ <in-expression start-index="35" stop-index="56">
+ <not>false</not>
+ <left>
+ <column name="order_id" start-index="35"
stop-index="42" />
+ </left>
+ <right>
+ <subquery start-index="47" stop-index="56">
+ <select>
+ <projections start-index="55" stop-index="55">
+ <expression-projection text="1"
start-index="55" stop-index="55">
+ <expr>
+ <literal-expression value="1"
start-index="55" stop-index="55" />
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_subquery_table_sql92">
<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.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index 030019b0d26..cda2c96cad3 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -12908,6 +12908,19 @@
</from>
</select>
+ <select sql-case-id="select_case_expression_sql92">
+ <projections start-index="7" stop-index="30">
+ <expression-projection text="CASE1WHEN1THEN1END" start-index="7"
stop-index="30">
+ <expr>
+ <common-expression text="CASE1WHEN1THEN1END"
start-index="7" stop-index="30" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="37" stop-index="43" />
+ </from>
+ </select>
+
<select sql-case-id="select_where_is_not_null_sql92">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
@@ -12930,6 +12943,28 @@
</where>
</select>
+ <select sql-case-id="select_where_is_null_sql92">
+ <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="41">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="41">
+ <left>
+ <column name="status" start-index="28" stop-index="33"
/>
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="NULL" start-index="38"
stop-index="41" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_compare_subquery_sql92">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
@@ -12980,6 +13015,7 @@
</projections>
</select>
+
<select sql-case-id="select_without_from_sql92">
<projections start-index="7" stop-index="7">
<expression-projection text="1" start-index="7" stop-index="7">
diff --git a/test/it/parser/src/main/resources/sql/supported/ddl/drop-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/drop-table.xml
index b84d17dcbd0..1a80d047d56 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/drop-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/drop-table.xml
@@ -43,4 +43,5 @@
<sql-case id="drop_table_if_exists_purge_hive_with_db" value="DROP TABLE
IF EXISTS test_db.t_log PURGE" db-types="Hive" />
<sql-case id="drop_table_firebird" value="DROP TABLE t_firebird_drop"
db-types="Firebird"/>
<sql-case id="drop_table_sql92" value="DROP TABLE t_drop" db-types="SQL92"
/>
+ <sql-case id="drop_multiple_tables_sql92" value="DROP TABLE t_drop_a,
t_drop_b" db-types="SQL92" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
index cf350826296..2351576c523 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
@@ -20,6 +20,7 @@
<sql-case id="select_sum" value="SELECT SUM(user_id) AS user_id_sum FROM
t_order" />
<sql-case id="select_sum_column" value="SELECT SUM(t_order.user_id) AS
user_id_sum FROM t_order" />
<sql-case id="select_count" value="SELECT COUNT(*) AS orders_count FROM
t_order" />
+ <sql-case id="select_count_star_alias_sql92" value="SELECT COUNT(*) AS cnt
FROM t_order" db-types="SQL92" />
<sql-case id="select_count_with_sub" value="SELECT COUNT(*) AS
orders_count FROM t_order WHERE order_id > 1-1" />
<sql-case id="select_count_with_sub_with_whitespace" value="SELECT
COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1" />
<sql-case id="select_max" value="SELECT MAX(user_id) AS max_user_id FROM
t_order" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
index a50afd79fb4..2a8adb132ce 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
@@ -25,6 +25,7 @@
<sql-case id="select_left_outer_join_related_with_alias_sql92"
value="SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN
employees e ON d.department_id = e.department_id ORDER BY d.department_id,
e.last_name" db-types="SQL92" />
<sql-case id="select_right_outer_join_related_with_alias" value="SELECT
d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON
d.department_id = e.department_id ORDER BY d.department_id, e.last_name"
db-types="MySQL, Oracle" />
<sql-case id="select_right_outer_join_related_with_alias_sql92"
value="SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN
employees e ON d.department_id = e.department_id ORDER BY d.department_id,
e.last_name" db-types="SQL92" />
+ <sql-case id="select_parenthesized_comma_join_sql92" value="SELECT * FROM
(t_order o, t_order_item i)" db-types="SQL92" />
<sql-case id="select_full_outer_join_related_with_alias" value="SELECT
d.department_id AS d_dept_id, e.department_id AS e_dept_id, e.last_name FROM
departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_full_outer_join_using_related_with_alias"
value="SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL
OUTER JOIN employees e USING (department_id) ORDER BY department_id,
e.last_name" db-types="Oracle" />
<sql-case id="select_cross_apply_join_related_with_alias" value="SELECT
d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY
(SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE
d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY
d.department_name, v.employee_id" db-types="Oracle" />
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 c839de5b39e..f2db0cb0075 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
@@ -434,14 +434,17 @@
<sql-case id="select_cast_int_sql92" value="SELECT CAST(user_id AS INT)
FROM t_order" db-types="SQL92" />
<sql-case id="select_all_param_alias_schema_sql92" value="SELECT ALL ? AS
"aid" FROM schema1.t_order WHERE (order_id = 1) OR active IS NULL"
db-types="SQL92" />
<sql-case id="select_like_between_and_sql92" value="SELECT status FROM
t_order WHERE status LIKE '1%' AND order_id BETWEEN 1 AND 10" db-types="SQL92"
/>
+ <sql-case id="select_where_is_null_sql92" value="SELECT * FROM t_order
WHERE status IS NULL" db-types="SQL92" />
<sql-case id="select_safe_eq_sql92" value="SELECT order_id FROM t_order
WHERE order_id <=> 1" db-types="SQL92" />
<sql-case id="select_in_list_sql92" value="SELECT order_id FROM t_order
WHERE order_id IN (1, 2)" db-types="SQL92" />
<sql-case id="select_not_in_subquery_sql92" value="SELECT order_id FROM
t_order WHERE order_id NOT IN (SELECT 1)" db-types="SQL92" />
+ <sql-case id="select_in_subquery_sql92" value="SELECT order_id FROM
t_order WHERE order_id IN (SELECT 1)" db-types="SQL92" />
<sql-case id="select_not_between_sql92" value="SELECT order_id FROM
t_order WHERE order_id NOT BETWEEN 1 AND 2" db-types="SQL92" />
<sql-case id="select_addition_expr_sql92" value="SELECT order_id + 1 FROM
t_order" db-types="SQL92" />
<sql-case id="select_interval_expression_sql92" value="SELECT INTERVAL 1
DAY" db-types="SQL92" />
<sql-case id="select_position_function_sql92" value="SELECT POSITION('a'
IN 'aaa')" db-types="SQL92" />
<sql-case id="select_null_literal_sql92" value="SELECT NULL FROM t_order"
db-types="SQL92" />
+ <sql-case id="select_case_expression_sql92" value="SELECT CASE 1 WHEN 1
THEN 1 END FROM t_order" db-types="SQL92" />
<sql-case id="select_without_from_sql92" value="SELECT 1" db-types="SQL92"
/>
<sql-case id="select_inner_join_sql92" value="SELECT * FROM t_order INNER
JOIN t_order_item ON t_order.order_id = t_order_item.order_id" db-types="SQL92"
/>
<sql-case id="select_subquery_projection_sql92" value="SELECT (SELECT 1)
sub FROM t_order" db-types="SQL92" />
