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 34bd7397946 Add SQL parser test cases for Oracle (#37848)
34bd7397946 is described below
commit 34bd73979464551726b2884cbbea6d60134d90fb
Author: Liang Zhang <[email protected]>
AuthorDate: Mon Jan 26 12:39:34 2026 +0800
Add SQL parser test cases for Oracle (#37848)
---
.../main/resources/case/dml/select-expression.xml | 90 ++++++++++++++++++++++
.../main/resources/case/dml/select-group-by.xml | 13 ++++
.../src/main/resources/case/dml/select-join.xml | 27 +++++++
.../resources/case/dml/select-special-function.xml | 57 ++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 64 +++++++++++++++
.../sql/supported/dml/select-expression.xml | 5 ++
.../sql/supported/dml/select-group-by.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../sql/supported/dml/select-special-function.xml | 3 +
.../main/resources/sql/supported/dml/select.xml | 4 +
10 files changed, 265 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 17bc9221f2d..1dcb33d4d18 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
@@ -3473,4 +3473,94 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_hex_literal_oracle">
+ <projections start-index="7" stop-index="9">
+ <expression-projection text="0x1" start-index="7" stop-index="9">
+ <expr>
+ <common-expression text="0x1" start-index="7"
stop-index="9" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="16" stop-index="19" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_bit_literal_oracle">
+ <projections start-index="7" stop-index="11">
+ <expression-projection text="0b101" start-index="7"
stop-index="11">
+ <expr>
+ <common-expression text="0b101" start-index="7"
stop-index="11" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="18" stop-index="21" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_binary_prefix_oracle">
+ <projections start-index="7" stop-index="16">
+ <column-projection name="col" start-index="14" stop-index="16" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="23" stop-index="26" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_interval_day_to_second_precision_oracle">
+ <projections start-index="7" stop-index="53">
+ <expression-projection text="(SYSTIMESTAMP - order_date) DAY(2) TO
SECOND(3)" start-index="7" stop-index="53">
+ <function>
+ <interval-expression start-index="8" stop-index="53">
+ <left>
+ <column name="SYSTIMESTAMP" start-index="8"
stop-index="19" />
+ </left>
+ <operator>-</operator>
+ <right>
+ <column name="order_date" start-index="23"
stop-index="32" />
+ </right>
+ <interval-day-to-second-expr start-index="35"
stop-index="53">
+ <day>DAY</day>
+ <to>TO</to>
+ <second>SECOND</second>
+
<leading-field-precision>2</leading-field-precision>
+
<fractional-second-precision>3</fractional-second-precision>
+ </interval-day-to-second-expr>
+ </interval-expression>
+ </function>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="orders" start-index="60" stop-index="65" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_interval_year_to_month_precision_oracle">
+ <projections start-index="7" stop-index="47">
+ <expression-projection text="(hire_date - start_date) YEAR(3) TO
MONTH" start-index="7" stop-index="47">
+ <function>
+ <interval-expression start-index="8" stop-index="47">
+ <left>
+ <column name="hire_date" start-index="8"
stop-index="16" />
+ </left>
+ <operator>-</operator>
+ <right>
+ <column name="start_date" start-index="20"
stop-index="29" />
+ </right>
+ <interval-year-to-month-expr start-index="32"
stop-index="47">
+ <year>YEAR</year>
+ <to>TO</to>
+ <month>MONTH</month>
+
<leading-field-precision>3</leading-field-precision>
+ </interval-year-to-month-expr>
+ </interval-expression>
+ </function>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="54" stop-index="62" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
index d900b75d55b..39cffe89622 100644
--- a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
@@ -985,4 +985,17 @@
</expression-item>
</group-by>
</select>
+
+ <select sql-case-id="select_group_by_rollup_oracle">
+ <from>
+ <simple-table name="employees" start-index="30" stop-index="38" />
+ </from>
+ <projections start-index="7" stop-index="23">
+ <column-projection name="dept" start-index="7" stop-index="10" />
+ <aggregation-projection type="SUM" expression="SUM(salary)"
start-index="13" stop-index="23" />
+ </projections>
+ <group-by start-index="40" stop-index="60">
+ <column-item name="dept" start-index="56" stop-index="59" />
+ </group-by>
+ </select>
</sql-parser-test-cases>
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 5e905a862ee..7c363140863 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
@@ -3660,4 +3660,31 @@
</join-table>
</from>
</select>
+
+ <select sql-case-id="select_outer_apply_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="LEFT">
+ <left>
+ <simple-table name="t_outer" start-index="14"
stop-index="20" />
+ </left>
+ <right>
+ <subquery-table alias="i" start-index="34" stop-index="59">
+ <subquery>
+ <select>
+ <projections start-index="42" stop-index="43">
+ <column-projection name="id"
start-index="42" stop-index="43" />
+ </projections>
+ <from>
+ <simple-table name="t_inner"
start-index="50" stop-index="56" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </right>
+ </join-table>
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index a7bfedf99b9..6968db63601 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -5604,4 +5604,61 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_translate_nchar_cs_oracle">
+ <from>
+ <simple-table name="translate_tab" start-index="42"
stop-index="54" />
+ </from>
+ <projections start-index="7" stop-index="35">
+ <expression-projection text="TRANSLATE(col USING NCHAR_CS)"
start-index="7" stop-index="35">
+ <expr>
+ <function function-name="TRANSLATE" start-index="7"
stop-index="35" text="TRANSLATE(col USING NCHAR_CS)">
+ <parameter>
+ <column name="col" start-index="17"
stop-index="19" />
+ </parameter>
+ <parameter>
+ <literal-expression value="NCHAR_CS"
start-index="27" stop-index="34" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_approx_rank_oracle">
+ <from>
+ <simple-table name="dual" start-index="29" stop-index="32" />
+ </from>
+ <projections start-index="7" stop-index="22">
+ <expression-projection text="APPROX_RANK(0.5)" start-index="7"
stop-index="22">
+ <expr>
+ <function function-name="APPROX_RANK" start-index="7"
stop-index="22" text="APPROX_RANK(0.5)">
+ <parameter>
+ <literal-expression value="0.5" start-index="19"
stop-index="21" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_isschemavalid_function_oracle">
+ <from>
+ <simple-table name="dual" start-index="49" stop-index="52" />
+ </from>
+ <projections start-index="7" stop-index="42">
+ <expression-projection text="ISSCHEMAVALID(xml_doc, 'schema.xsd')"
start-index="7" stop-index="42">
+ <expr>
+ <function function-name="ISSCHEMAVALID" start-index="7"
stop-index="42" text="ISSCHEMAVALID(xml_doc, 'schema.xsd')">
+ <parameter>
+ <column name="xml_doc" start-index="21"
stop-index="27" />
+ </parameter>
+ <parameter>
+ <literal-expression value="schema.xsd"
start-index="30" stop-index="41" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </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 6d82cfe3d4b..dd9c0c0166f 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -13301,4 +13301,68 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_where_is_true_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="14" stop-index="22" />
+ </from>
+ <where start-index="24" stop-index="43">
+ <expr>
+ <binary-operation-expression start-index="30" stop-index="43">
+ <left>
+ <column name="active" start-index="30" stop-index="35"
/>
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="TRUE" start-index="40"
stop-index="43" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_in_without_parentheses_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <where start-index="22" stop-index="46">
+ <expr>
+ <in-expression start-index="28" stop-index="46">
+ <left>
+ <column name="status" start-index="28" stop-index="33"
/>
+ </left>
+ <right>
+ <literal-expression value="shipped" start-index="38"
stop-index="46" />
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_from_shards_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="21" stop-index="29" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_xmlnamespaces_clause_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <function-table table-alias="xt">
+ <table-function function-name="XMLTABLE"
+ text="XMLTABLE(XMLNAMESPACES(DEFAULT 'uri',
'urn:ns1' AS ns1), '/root' PASSING xmlcol COLUMNS dummy VARCHAR2(10) PATH
'dummy')" />
+ </function-table>
+ </from>
+ </select>
</sql-parser-test-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 4b8c8194e2d..d03d42df229 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
@@ -149,4 +149,9 @@
<sql-case id="select_where_not_equal_all_subquery_mysql" value="SELECT *
FROM t_order WHERE amount <> ALL (SELECT price FROM t_price)"
db-types="MySQL" />
<sql-case id="select_where_with_safe_equal_parameter_mysql" value="SELECT
* FROM t_order WHERE status <=> ?" db-types="MySQL"
case-types="PLACEHOLDER" />
<sql-case id="select_where_equal_all_subquery_mysql" value="SELECT * FROM
t_order WHERE amount = ALL (SELECT price FROM t_price)" db-types="MySQL" />
+ <sql-case id="select_hex_literal_oracle" value="SELECT 0x1 FROM dual"
db-types="Oracle" />
+ <sql-case id="select_bit_literal_oracle" value="SELECT 0b101 FROM dual"
db-types="Oracle" />
+ <sql-case id="select_binary_prefix_oracle" value="SELECT BINARY col FROM
dual" db-types="Oracle" />
+ <sql-case id="select_interval_day_to_second_precision_oracle"
value="SELECT (SYSTIMESTAMP - order_date) DAY(2) TO SECOND(3) FROM orders"
db-types="Oracle" />
+ <sql-case id="select_interval_year_to_month_precision_oracle"
value="SELECT (hire_date - start_date) YEAR(3) TO MONTH FROM employees"
db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 2211485033a..5fa5558e955 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -46,4 +46,5 @@
<sql-case id="select_from_input_table" value="SELECT count(*) FROM input
GROUP BY PartitionId, clusterid, tumblingwindow;" db-types="SQLServer"/>
<sql-case id="select_group_by_top_column_value" value="SELECT TOP 10
hash_unique_bigint_id FROM dbo.TelemetryDS WHERE Timestamp BETWEEN @StartTime
AND @EndTime GROUP BY hash_unique_bigint_id ORDER BY
MAX(max_elapsed_time_microsec) DESC" db-types="SQLServer" />
<sql-case id="select_group_by_count_with_tumblingwindow_function"
value="SELECT count(*) FROM input GROUP BY clusterid,tumblingwindow(minutes,
5)" db-types="SQLServer" />
+ <sql-case id="select_group_by_rollup_oracle" value="SELECT dept,
SUM(salary) FROM employees GROUP BY ROLLUP(dept)" db-types="Oracle" />
</sql-cases>
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 2a8adb132ce..3eea391c134 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
@@ -68,4 +68,5 @@
<sql-case id="select_join_with_brackets" value="SELECT t.ORDER_ID FROM
(t_order t INNER JOIN t_order_item i ON t.ORDER_ID = i.ORDER_ID) LEFT JOIN
T_ORDER c ON t.ORDER_ID = c.ORDER_ID" db-types="Oracle"/>
<sql-case id="select_right_join_firebird" value="SELECT * FROM t_order o
RIGHT JOIN t_order_item i ON o.order_id = i.order_id" db-types="Firebird"/>
<sql-case id="select_cross_join_firebird" value="SELECT * FROM t_order
CROSS JOIN t_order_item" db-types="Firebird"/>
+ <sql-case id="select_outer_apply_oracle" value="SELECT * FROM t_outer
OUTER APPLY (SELECT id FROM t_inner) i" 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 e56816e832d..dee57da7b34 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
@@ -320,4 +320,7 @@
<sql-case id="select_regexp_function_with_null" value="SELECT REGEXP(NULL,
'^billie')" db-types="Doris" />
<sql-case id="select_current_time_precision_mysql" value="SELECT
CURRENT_TIME(3)" db-types="MySQL" />
<sql-case id="select_odbc_fn_now_mysql" value="SELECT {fn NOW()}"
db-types="MySQL" />
+ <sql-case id="select_translate_nchar_cs_oracle" value="SELECT
TRANSLATE(col USING NCHAR_CS) FROM translate_tab" db-types="Oracle" />
+ <sql-case id="select_approx_rank_oracle" value="SELECT APPROX_RANK(0.5)
FROM dual" db-types="Oracle" />
+ <sql-case id="select_isschemavalid_function_oracle" value="SELECT
ISSCHEMAVALID(xml_doc, 'schema.xsd') FROM dual" 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 3fc52da30cc..8dde5c77677 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
@@ -470,4 +470,8 @@
<sql-case id="select_null_safe_equal_mysql" value="SELECT * FROM t_order
WHERE order_id <=> ?" db-types="MySQL" case-types="PLACEHOLDER" />
<sql-case id="select_three_part_column_mysql" value="SELECT
db1.t_order.status FROM db1.t_order" db-types="MySQL" />
<sql-case id="select_string_literal_concat_mysql" value="SELECT 'ab' 'cd'"
db-types="MySQL" />
+ <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_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-cases>
