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 ecf76ffc4f0 Add SQL parser test cases for Oracle (#37858)
ecf76ffc4f0 is described below
commit ecf76ffc4f090c3ca89a6f581de65c5e9320f338
Author: Liang Zhang <[email protected]>
AuthorDate: Tue Jan 27 14:04:09 2026 +0800
Add SQL parser test cases for Oracle (#37858)
* Add SQL parser test cases for Oracle
* Add SQL parser test cases for Oracle
* Add SQL parser test cases for Oracle
---
.../src/main/resources/case/ddl/create-table.xml | 7 ++
.../main/resources/case/dml/select-expression.xml | 22 ++++++
.../main/resources/case/dml/select-order-by.xml | 13 ++++
.../parser/src/main/resources/case/dml/select.xml | 85 ++++++++++++++++++++++
.../main/resources/case/plsql/create-procedure.xml | 8 ++
.../resources/sql/supported/ddl/create-table.xml | 1 +
.../sql/supported/dml/select-expression.xml | 5 +-
.../sql/supported/dml/select-order-by.xml | 1 +
.../sql/supported/dml/select-special-function.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 7 ++
.../sql/supported/plsql/create-procedure.xml | 1 +
11 files changed, 149 insertions(+), 2 deletions(-)
diff --git a/test/it/parser/src/main/resources/case/ddl/create-table.xml
b/test/it/parser/src/main/resources/case/ddl/create-table.xml
index ab17b1eea0f..0cb71e3eb9c 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-table.xml
@@ -3881,4 +3881,11 @@
<referenced-table name="t_firebird_len" start-index="122"
stop-index="135" />
</constraint-definition>
</create-table>
+
+ <create-table sql-case-id="create_table_with_ref_column_oracle">
+ <table name="ref_table" start-index="13" stop-index="21" />
+ <column-definition type="person_t" start-index="24" stop-index="43">
+ <column name="ref_col" />
+ </column-definition>
+ </create-table>
</sql-parser-test-cases>
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 1dcb33d4d18..8f1895a8643 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
@@ -120,6 +120,28 @@
</where>
</select>
+ <select sql-case-id="select_where_is_nan_oracle">
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="22" stop-index="38">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="38">
+ <left>
+ <column name="flag" start-index="28" stop-index="31" />
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="NAN" start-index="36"
stop-index="38" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_case_when_with_mul_condition">
<projections start-index="7" stop-index="52">
<expression-projection text="case 1*0 when "a" then
"true" else "false" END" start-index="7" stop-index="52">
diff --git a/test/it/parser/src/main/resources/case/dml/select-order-by.xml
b/test/it/parser/src/main/resources/case/dml/select-order-by.xml
index b30d8e21aff..a6987b0a7b3 100644
--- a/test/it/parser/src/main/resources/case/dml/select-order-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-order-by.xml
@@ -413,4 +413,17 @@
</expression-item>
</order-by>
</select>
+
+ <select sql-case-id="select_order_by_position_oracle">
+ <from>
+ <simple-table name="t_order" start-index="30" stop-index="36" />
+ </from>
+ <projections start-index="7" stop-index="23">
+ <column-projection name="order_id" start-index="7" stop-index="14"
/>
+ <column-projection name="user_id" start-index="17" stop-index="23"
/>
+ </projections>
+ <order-by>
+ <index-item index="1" order-direction="ASC" start-index="47"
stop-index="47" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
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 91a2037fb87..2d9e4c478f3 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -13385,4 +13385,89 @@
<collection-table start-index="14" stop-index="30" />
</from>
</select>
+
+ <select sql-case-id="select_table_collection_function_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <collection-table alias="t" start-index="14" stop-index="34" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_xmlcdata_function_oracle">
+ <projections start-index="7" stop-index="22">
+ <expression-projection text="XMLCDATA('text')" start-index="7"
stop-index="22" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="29" stop-index="32" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_format_function_oracle">
+ <projections start-index="7" stop-index="26">
+ <expression-projection text="FORMAT('abc', '999')" start-index="7"
stop-index="26" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="33" stop-index="36" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_first_value_oracle">
+ <projections start-index="7" stop-index="48">
+ <expression-projection text="FIRST_VALUE(salary) OVER (ORDER BY
salary)" start-index="7" stop-index="48" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="55" stop-index="63" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_datetime_lbe_literal_oracle">
+ <projections start-index="7" stop-index="32">
+ <expression-projection text="{ts '2020-02-02 10:00:00'}"
start-index="7" stop-index="32" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="39" stop-index="42" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_feature_compare_oracle">
+ <projections start-index="7" stop-index="36">
+ <expression-projection text="FEATURE_COMPARE(model USING *)"
start-index="7" stop-index="36" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="43" stop-index="46" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_table_dblink_oracle">
+ <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>
+ </select>
+
+ <select sql-case-id="select_into_variable_oracle">
+ <projections start-index="7" stop-index="12">
+ <column-projection name="status" start-index="7" stop-index="12" />
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="35" stop-index="41" />
+ </from>
+ <where start-index="43" stop-index="60">
+ <expr>
+ <binary-operation-expression start-index="49" stop-index="60">
+ <left>
+ <column name="order_id" start-index="49"
stop-index="56" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="60"
stop-index="60" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
b/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
index 9b93dafc65c..22c26866062 100644
--- a/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
+++ b/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
@@ -51,4 +51,12 @@
<procedure-body-end-name name="cp_SHOPBudget_Gy" />
</procedure-body-end-names>
</create-procedure>
+
+ <create-procedure sql-case-id="pl_sql_create_procedure_with_loop_dml">
+ <procedure-name name="proc_multi" />
+ <sql-statements>
+ <sql-statement start-index="48" stop-index="65"
statement-class-simple-name="UpdateStatement" />
+ </sql-statements>
+ </create-procedure>
+
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
index 739a4a0e9bd..50837941017 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
@@ -530,4 +530,5 @@
<sql-case id="create_table_with_blob_segment_size" value="CREATE TABLE
t_blob_segment (payload BLOB SUB_TYPE 0 SEGMENT SIZE 80)" db-types="Firebird" />
<sql-case id="create_table_firebird_len" value="CREATE TABLE
t_firebird_len (id INT PRIMARY KEY, amount DECIMAL(10,2), code CHAR(5))"
db-types="Firebird" />
<sql-case id="create_table_firebird_constraint" value="CREATE TABLE
t_firebird_constraint (id INT, CONSTRAINT pk_t PRIMARY KEY (id), CONSTRAINT
fk_t FOREIGN KEY (id) REFERENCES t_firebird_len(id))" db-types="Firebird" />
+ <sql-case id="create_table_with_ref_column_oracle" value="CREATE TABLE
ref_table (ref_col REF person_t)" db-types="Oracle" />
</sql-cases>
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 d03d42df229..975cf8f0c5a 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
@@ -108,8 +108,9 @@
<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" />
- <sql-case id="select_where_is_false_doris" value="SELECT * FROM t_order
WHERE flag IS FALSE" db-types="Doris" />
+ <sql-case id="select_where_is_true_doris" value="SELECT * FROM t_order
WHERE flag IS TRUE" db-types="Doris,Oracle" />
+ <sql-case id="select_where_is_false_doris" value="SELECT * FROM t_order
WHERE flag IS FALSE" db-types="Doris,Oracle" />
+ <sql-case id="select_where_is_nan_oracle" value="SELECT * FROM t_order
WHERE flag IS NAN" db-types="Oracle" />
<sql-case id="select_string_concat_with_double_bar" value="SELECT 'a' ||
'b'" db-types="Doris,Hive,Firebird" />
<sql-case id="select_age_for_postgres" value="SELECT * FROM
cypher('sharding_test_1', $$ CREATE (n) $$) as (a agtype)"
db-types="PostgreSQL" />
<sql-case id="select_datetime_expression" value="SELECT SYSTIMESTAMP AT
TIME ZONE 'UTC' FROM DUAL;" db-types="Oracle" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
index 48b94fc4d9e..3a93841fb72 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
@@ -36,4 +36,5 @@
<sql-case id="select_order_by_with_star_no_table_alias" value="SELECT *
FROM t_order ORDER BY order_id" db-types="H2,MySQL" />
<sql-case id="select_order_by_with_table_star_without_table_name"
value="SELECT i.*, o.* FROM t_order o JOIN t_order_item i ON o.user_id =
i.user_id AND o.order_id = i.order_id ORDER BY item_id" db-types="H2,MySQL" />
<sql-case id="select_order_by_expression_binary_operation" value="select *
from t_order order by 1+1" db-types="MySQL,Presto,Doris,Firebird" />
+ <sql-case id="select_order_by_position_oracle" value="SELECT order_id,
user_id FROM t_order ORDER BY 1" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index dee57da7b34..f9a86961827 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -52,6 +52,7 @@
<sql-case id="select_set_function" value="SELECT customer_id,
SET(cust_address_ntab) address FROM customers_demo ORDER BY customer_id;"
db-types="Oracle" />
<sql-case id="select_pivot" value="SELECT pivotAlias.* FROM (SELECT * FROM
sales) PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb')) pivotAlias"
db-types="Oracle"/>
<sql-case id="select_unpivot" value="SELECT * FROM (SELECT * FROM sales)
UNPIVOT ((cert_type, cert_number) FOR cert_source IN ((primary_cert_type,
primary_cert_no), (alt_cert_type1, alt_cert_no1), (alt_cert_type2,
alt_cert_no2)))" db-types="Oracle"/>
+ <sql-case id="select_feature_compare_oracle" value="SELECT
FEATURE_COMPARE(model USING *) FROM dual" db-types="Oracle" />
<sql-case id="select_string_split_function" value="SELECT value as tag,
COUNT(*) AS [number_of_articles] FROM Product CROSS APPLY STRING_SPLIT(Tags,
',') GROUP BY value HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC"
db-types="SQLServer"/>
<sql-case id="select_from_open_json_function" value="SELECT * FROM
OPENJSON(@array) WITH ( month VARCHAR(3), temp int, month_id tinyint
'$.sql:identity()') as months" db-types="SQLServer"/>
<sql-case id="select_from_open_json_function_with_path" value="SELECT
[key], value FROM OPENJSON(@json,'$.path.to."sub-object"')"
db-types="SQLServer"/>
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 d43cee267ce..55e77ce8cfb 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,6 +474,13 @@
<sql-case id="select_where_is_true_oracle" value="SELECT * FROM employees
WHERE active IS TRUE" db-types="Oracle" />
<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_table_dblink_oracle" value="SELECT * FROM
t_order@db_link" db-types="Oracle" />
+ <sql-case id="select_into_variable_oracle" value="SELECT status INTO
status_var FROM t_order WHERE order_id = 1" 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-case id="select_table_collection_function_oracle" value="SELECT *
FROM TABLE(get_nested()) t" db-types="Oracle" />
+ <sql-case id="select_xmlcdata_function_oracle" value="SELECT
XMLCDATA('text') FROM dual" db-types="Oracle" />
+ <sql-case id="select_format_function_oracle" value="SELECT FORMAT('abc',
'999') FROM dual" db-types="Oracle" />
+ <sql-case id="select_first_value_oracle" value="SELECT FIRST_VALUE(salary)
OVER (ORDER BY salary) FROM employees" db-types="Oracle" />
+ <sql-case id="select_datetime_lbe_literal_oracle" value="SELECT {ts
'2020-02-02 10:00:00'} FROM dual" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
b/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
index f8e79a94ecb..9068b96f327 100644
--- a/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
+++ b/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
@@ -19,4 +19,5 @@
<sql-cases>
<sql-case id="pl_sql_create_procedure" db-types="Oracle" value="create or
replace procedure test_procedure(P_RETVAL out varchar2) is E_EXCEPTION
EXCEPTION; v_bendate date; v_enddate date; v_day varchar2(30); begin
SAVEPOINT cp_SHOPBudget_Gy; P_RETVAL := 0; select upper(trim(to_char(sysdate,
'day'))) into v_day from dual; if v_day = 'MONDAY' then return; else
case when v_day = 'MONDAY' then v_bendate := TRUNC(SYSDATE) - 7;
v_enddate := TRUNC( [...]
<sql-case id="pl_sql_create_procedure_not_end_with_semi" db-types="Oracle"
value="create or replace procedure test_procedure(P_RETVAL out varchar2) is
E_EXCEPTION EXCEPTION; v_bendate date; v_enddate date; v_day
varchar2(30); begin SAVEPOINT cp_SHOPBudget_Gy; P_RETVAL := 0; select
upper(trim(to_char(sysdate, 'day'))) into v_day from dual; if v_day = 'MONDAY'
then return; else case when v_day = 'MONDAY' then
v_bendate := TRUNC(SYSDATE) - 7; v [...]
+ <sql-case id="pl_sql_create_procedure_with_loop_dml" db-types="Oracle"
value="create or replace procedure proc_multi as begin update t set a = 1;
end;"/>
</sql-cases>
