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 0748ec8423c Add SQL parser test cases for Oracle (#37853)
0748ec8423c is described below
commit 0748ec8423c2b24cc89c1a20cdcf2320819be0aa
Author: Liang Zhang <[email protected]>
AuthorDate: Mon Jan 26 19:57:01 2026 +0800
Add SQL parser test cases for Oracle (#37853)
---
.../parser/src/main/resources/case/dal/spool.xml | 1 +
.../src/main/resources/case/ddl/create-type.xml | 1 +
.../parser/src/main/resources/case/dml/delete.xml | 28 ++++++++++++
.../parser/src/main/resources/case/dml/insert.xml | 21 +++++++++
.../parser/src/main/resources/case/dml/select.xml | 9 ++++
.../parser/src/main/resources/case/dml/update.xml | 52 ++++++++++++++++++++++
.../src/main/resources/sql/supported/dal/spool.xml | 1 +
.../resources/sql/supported/ddl/create-type.xml | 1 +
.../main/resources/sql/supported/dml/delete.xml | 1 +
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 1 +
.../main/resources/sql/supported/dml/update.xml | 2 +
12 files changed, 119 insertions(+)
diff --git a/test/it/parser/src/main/resources/case/dal/spool.xml
b/test/it/parser/src/main/resources/case/dal/spool.xml
index 7b4488ca367..c1c815ee687 100644
--- a/test/it/parser/src/main/resources/case/dal/spool.xml
+++ b/test/it/parser/src/main/resources/case/dal/spool.xml
@@ -18,4 +18,5 @@
<sql-parser-test-cases>
<spool sql-case-id="spool_online_all.sql" filename="online_all.sql" />
+ <spool sql-case-id="spool_without_filename" filename="OFF" />
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-type.xml
b/test/it/parser/src/main/resources/case/ddl/create-type.xml
index ccd68182b57..e6cb8e278c8 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-type.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-type.xml
@@ -22,4 +22,5 @@
<create-type sql-case-id="create_type_as_varray" />
<create-type sql-case-id="create_type_as_object" />
<create-type sql-case-id="create_sub_type" />
+ <create-type
sql-case-id="create_type_noneditionable_not_instantiable_oracle" />
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/delete.xml
b/test/it/parser/src/main/resources/case/dml/delete.xml
index d98dbcb2304..e63de72aa38 100644
--- a/test/it/parser/src/main/resources/case/dml/delete.xml
+++ b/test/it/parser/src/main/resources/case/dml/delete.xml
@@ -714,6 +714,34 @@
<table name="product_price_history" start-index="7" stop-index="27" />
</delete>
+ <delete sql-case-id="delete_table_collection_oracle">
+ <subquery-table start-index="12" stop-index="49">
+ <subquery>
+ <select>
+ <projections start-index="25" stop-index="34">
+ <column-projection name="nested_col" start-index="25"
stop-index="34" />
+ </projections>
+ <from start-index="36" stop-index="48">
+ <simple-table name="t_nested" start-index="41"
stop-index="48" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ <where start-index="51" stop-index="70">
+ <expr>
+ <binary-operation-expression start-index="57" stop-index="70">
+ <left>
+ <column name="nested_col" start-index="57"
stop-index="66" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="70"
stop-index="70" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </delete>
+
<delete sql-case-id="delete_with_schema">
<table name="t_order" start-index="12" stop-index="22">
<owner name="db1" start-index="12" stop-index="14" />
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 56ba45a3816..82394816bcd 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -237,6 +237,27 @@
</values>
</insert>
+ <insert sql-case-id="insert_table_collection_oracle">
+ <columns start-index="54" stop-index="54" />
+ <select>
+ <from start-index="38" stop-index="52">
+ <simple-table name="t_nested" alias="t" start-index="43"
stop-index="52" />
+ </from>
+ <projections start-index="25" stop-index="36">
+ <column-projection name="nested_col" start-index="25"
stop-index="36">
+ <owner name="t" start-index="25" stop-index="25" />
+ </column-projection>
+ </projections>
+ </select>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="63"
stop-index="63" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
<insert sql-case-id="insert_with_now_function" parameters="1, 1, 'init'">
<table name="t_order_item" start-index="12" stop-index="23" />
<columns start-index="25" stop-index="75">
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 dd9c0c0166f..aec75c1ac51 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -13365,4 +13365,13 @@
</function-table>
</from>
</select>
+
+ <select sql-case-id="select_table_collection_column_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <collection-table start-index="14" stop-index="30" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/update.xml
b/test/it/parser/src/main/resources/case/dml/update.xml
index c2cf2997585..62ce5c243fc 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -63,6 +63,58 @@
</where>
</update>
+ <update sql-case-id="update_set_default_oracle">
+ <table start-index="7" stop-index="15">
+ <simple-table name="employees" start-index="7" stop-index="15" />
+ </table>
+ <set start-index="17" stop-index="36">
+ <assignment start-index="21" stop-index="36">
+ <column name="status" start-index="21" stop-index="26" />
+ <assignment-value>
+ <column name="DEFAULT" start-index="30" stop-index="36" />
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="38" stop-index="58">
+ <expr>
+ <binary-operation-expression start-index="44" stop-index="58">
+ <left>
+ <column name="employee_id" start-index="44"
stop-index="54" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="58"
stop-index="58" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_table_collection_oracle">
+ <table start-index="7" stop-index="44">
+ <subquery-table start-index="7" stop-index="44">
+ <subquery>
+ <select>
+ <projections start-index="20" stop-index="29">
+ <column-projection name="nested_col"
start-index="20" stop-index="29" />
+ </projections>
+ <from start-index="31" stop-index="43">
+ <simple-table name="t_nested" start-index="36"
stop-index="43" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </table>
+ <set start-index="46" stop-index="69">
+ <assignment start-index="50" stop-index="69">
+ <column name="nested_col" start-index="50" stop-index="59" />
+ <assignment-value>
+ <column name="DEFAULT" start-index="63" stop-index="69" />
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+
<update sql-case-id="update_with_alias" parameters="'update', 1, 1">
<table start-index="7" stop-index="18" >
<simple-table name="t_order" alias="o" start-index="7"
stop-index="18" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dal/spool.xml
b/test/it/parser/src/main/resources/sql/supported/dal/spool.xml
index c34c0e4325f..01fe0970085 100644
--- a/test/it/parser/src/main/resources/sql/supported/dal/spool.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dal/spool.xml
@@ -18,4 +18,5 @@
<sql-cases>
<sql-case id="spool_online_all.sql" value="SPOOL online_all.sql"
db-types="Oracle"/>
+ <sql-case id="spool_without_filename" value="SPOOL OFF" db-types="Oracle"/>
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-type.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-type.xml
index 4f8b9286a60..c83ebc7e3ed 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-type.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-type.xml
@@ -22,4 +22,5 @@
<sql-case id="create_type_as_varray" value="CREATE TYPE address_array_t AS
VARRAY(3) OF cust_address_typ;" db-types="Oracle" />
<sql-case id="create_type_as_object" value="CREATE TYPE person_t AS OBJECT
(name VARCHAR2(100), ssn NUMBER) NOT FINAL;" db-types="Oracle" />
<sql-case id="create_sub_type" value="CREATE TYPE employee_t UNDER
person_t (department_id NUMBER, salary NUMBER) NOT FINAL;" db-types="Oracle" />
+ <sql-case id="create_type_noneditionable_not_instantiable_oracle"
value="CREATE OR REPLACE NONEDITIONABLE TYPE person_t AS OBJECT (name
VARCHAR2(100)) NOT INSTANTIABLE" db-types="Oracle" />
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
index 7aa585eac20..9170cdc65f2 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
@@ -42,6 +42,7 @@
<sql-case id="delete_with_returning_into" value="DELETE FROM employees
WHERE job_id = 'SA_REP' AND hire_date + TO_YMINTERVAL('01-00') < SYSDATE
RETURNING salary INTO bnd1" db-types="Oracle" />
<sql-case id="delete_with_partition" value="DELETE FROM sales PARTITION
(sales_q1_1998) WHERE amount_sold > 1000" db-types="Oracle" />
<sql-case id="delete_with_table" value="DELETE product_price_history"
db-types="Oracle" />
+ <sql-case id="delete_table_collection_oracle" value="DELETE FROM
TABLE(SELECT nested_col FROM t_nested) WHERE nested_col = 1" db-types="Oracle"
/>
<sql-case id="delete_with_schema" value="DELETE FROM db1.t_order" />
<sql-case id="delete_with_simple_condition" value="DELETE FROM
Q1_2000_sales WHERE amount_sold < 0" db-types="Oracle" />
<sql-case id="delete_with_output_clause_with_compress_function"
value="DELETE FROM player OUTPUT deleted.id,deleted.name,
deleted.surname,deleted.datemodifier,COMPRESS(deleted.info) INTO
dbo.inactivePlayers WHERE datemodified < @startOfYear" db-types="SQLServer"
/>
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 2d3aa5188a2..0367520dac8 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
@@ -204,4 +204,5 @@
<sql-case id="insert_with_columns_sql92" value="INSERT INTO
t_order(order_id, status) VALUES (1, 'ok')" db-types="SQL92" />
<sql-case id="insert_without_columns_sql92" value="INSERT INTO t_order
VALUES (1, 'ok')" db-types="SQL92" />
<sql-case id="insert_with_default_sql92" value="INSERT INTO
t_order(status) VALUES (DEFAULT)" db-types="SQL92" />
+ <sql-case id="insert_table_collection_oracle" value="INSERT INTO
TABLE(SELECT t.nested_col FROM t_nested t) VALUES (1)" db-types="Oracle" />
</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 8dde5c77677..b629758d760 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
@@ -474,4 +474,5 @@
<sql-case id="select_in_without_parentheses_oracle" value="SELECT * FROM
t_order WHERE status IN 'shipped'" db-types="Oracle" />
<sql-case id="select_from_shards_oracle" value="SELECT * FROM
SHARDS(employees)" db-types="Oracle" />
<sql-case id="select_with_xmlnamespaces_clause_oracle" value="SELECT *
FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'uri', 'urn:ns1' AS ns1), '/root' PASSING
xmlcol COLUMNS dummy VARCHAR2(10) PATH 'dummy') xt" db-types="Oracle" />
+ <sql-case id="select_table_collection_column_oracle" value="SELECT * FROM
TABLE(nested_col)" db-types="Oracle" />
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
index 43a1ef409f8..831c525d87f 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
@@ -76,4 +76,6 @@
<sql-case id="update_basic" value="UPDATE products SET stock = 0;"
db-types="Hive" />
<sql-case id="update_with_null" value="UPDATE user_profiles SET last_login
= NULL;" db-types="Hive" />
<sql-case id="update_with_where_sql92" value="UPDATE t_order SET status =
'x' WHERE order_id = 1" db-types="SQL92" />
+ <sql-case id="update_set_default_oracle" value="UPDATE employees SET
status = DEFAULT WHERE employee_id = 1" db-types="Oracle" />
+ <sql-case id="update_table_collection_oracle" value="UPDATE TABLE(SELECT
nested_col FROM t_nested) SET nested_col = DEFAULT" db-types="Oracle" />
</sql-cases>
