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 96f804ff83f Add SQL parser test cases for Oracle (#37864)
96f804ff83f is described below
commit 96f804ff83f35621baa89bedbcc265fd71cb133a
Author: Liang Zhang <[email protected]>
AuthorDate: Tue Jan 27 20:33:55 2026 +0800
Add SQL parser test cases for Oracle (#37864)
* Refactor @ParameterizedTest
* Refactor @ParameterizedTest
* Add SQL parser test cases for Oracle
* Add SQL parser test cases for Oracle
* Add SQL parser test cases for Oracle
* 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/alter-table.xml | 12 ++
.../src/main/resources/case/ddl/create-table.xml | 10 ++
.../src/main/resources/case/ddl/create-type.xml | 2 +
.../src/main/resources/case/ddl/create-view.xml | 14 ++-
.../main/resources/case/dml/select-expression.xml | 37 ++++++
.../main/resources/case/dml/select-group-by.xml | 13 ++
.../src/main/resources/case/dml/select-into.xml | 9 ++
.../src/main/resources/case/dml/select-join.xml | 44 +++++++
.../parser/src/main/resources/case/dml/select.xml | 138 +++++++++++++++++++++
.../parser/src/main/resources/case/dml/update.xml | 14 +++
.../resources/sql/supported/ddl/alter-table.xml | 2 +
.../resources/sql/supported/ddl/create-table.xml | 1 +
.../resources/sql/supported/ddl/create-type.xml | 2 +
.../resources/sql/supported/ddl/create-view.xml | 1 +
.../sql/supported/dml/select-expression.xml | 3 +
.../resources/sql/supported/dml/select-into.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 11 ++
.../main/resources/sql/supported/dml/update.xml | 1 +
19 files changed, 315 insertions(+), 1 deletion(-)
diff --git a/test/it/parser/src/main/resources/case/ddl/alter-table.xml
b/test/it/parser/src/main/resources/case/ddl/alter-table.xml
index f50d4e144df..897ca46c7d9 100644
--- a/test/it/parser/src/main/resources/case/ddl/alter-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/alter-table.xml
@@ -1765,6 +1765,18 @@
<modify-constraint start-index="18" stop-index="53"
constraint-name="sales_time_fk" />
</alter-table>
+ <alter-table sql-case-id="alter_table_modify_primary_key_rely">
+ <table name="sales" start-index="12" stop-index="16" />
+ <modify-constraint start-index="18" stop-index="40" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_modify_collection_retrieval_locator">
+ <table name="nested_table_parent" start-index="12" stop-index="30" />
+ <modify-collection-retrieval start-index="32" stop-index="78">
+ <table name="child_tbl" start-index="52" stop-index="60" />
+ </modify-collection-retrieval>
+ </alter-table>
+
<alter-table sql-case-id="alter_table_enable_row_movement">
<table name="table" start-index="12" stop-index="16" />
</alter-table>
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 0cb71e3eb9c..c3644cbb9c7 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
@@ -3888,4 +3888,14 @@
<column name="ref_col" />
</column-definition>
</create-table>
+
+ <create-table sql-case-id="create_table_with_varchar2_char_length_oracle">
+ <table name="dt_len" start-index="13" stop-index="18" />
+ <column-definition type="VARCHAR2" start-index="21" stop-index="39">
+ <column name="c" />
+ <data-type value="VARCHAR2">
+ <data-type-length precision="10" type="CHAR" />
+ </data-type>
+ </column-definition>
+ </create-table>
</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 e6cb8e278c8..c370c008d0b 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
@@ -23,4 +23,6 @@
<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" />
+ <create-type sql-case-id="create_object_type_not_persistable_oracle" />
+ <create-type sql-case-id="create_varray_type_persistable_oracle" />
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-view.xml
b/test/it/parser/src/main/resources/case/ddl/create-view.xml
index aa8e02bfcdc..9f215bd013c 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-view.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-view.xml
@@ -273,7 +273,19 @@
</where>
</select>
</create-view>
-
+
+ <create-view sql-case-id="create_or_replace_view_simple_oracle"
view-definition="SELECT 1 FROM dual">
+ <view name="v_or_replace" start-index="23" stop-index="34" />
+ <select>
+ <projections start-index="46" stop-index="46">
+ <expression-projection text="1" start-index="46"
stop-index="46" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="53" stop-index="56" />
+ </from>
+ </select>
+ </create-view>
+
<create-view sql-case-id="create_view_as_simple_select1"
view-definition="SELECT * FROM laurel.emp">
<view name="employee" start-index="12" stop-index="26">
<owner name="laurel" start-index="12" stop-index="17" />
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 8f1895a8643..78b3b5142c8 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
@@ -3174,6 +3174,43 @@
</subquery-table>
</from>
</select>
+
+ <select sql-case-id="select_nchar_literal_oracle">
+ <projections start-index="7" stop-index="12">
+ <expression-projection text="abc" start-index="7" stop-index="12">
+ <expr>
+ <literal-expression value="abc" start-index="7"
stop-index="12" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="19" stop-index="22" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_datetime_escape_literal_oracle">
+ <projections start-index="7" stop-index="32">
+ <expression-projection text="{ts '2020-01-01 10:00:00'}"
start-index="7" stop-index="32">
+ <expr>
+ <common-expression text="{ts '2020-01-01 10:00:00'}"
start-index="7" stop-index="32" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="39" stop-index="42" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_nested_attribute_oracle">
+ <projections start-index="7" stop-index="20">
+ <column-projection name="c.address.city" start-index="7"
stop-index="20">
+ <owner name="c" start-index="7" stop-index="7" />
+ </column-projection>
+ </projections>
+ <from>
+ <simple-table name="contacts" alias="c" start-index="27"
stop-index="36" />
+ </from>
+ </select>
<select sql-case-id="select_inet_function">
<projections start-index="7" stop-index="55">
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 39cffe89622..bde33fc517a 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
@@ -998,4 +998,17 @@
<column-item name="dept" start-index="56" stop-index="59" />
</group-by>
</select>
+
+ <select sql-case-id="select_group_by_rollup_oracle_with_rollup">
+ <projections start-index="7" stop-index="29">
+ <column-projection name="department_id" start-index="7"
stop-index="19" />
+ <aggregation-projection type="COUNT" expression="COUNT(*)"
start-index="22" stop-index="29" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="36" stop-index="44" />
+ </from>
+ <group-by start-index="46" stop-index="75">
+ <column-item name="department_id" order-direction="ASC"
start-index="62" stop-index="74" />
+ </group-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-into.xml
b/test/it/parser/src/main/resources/case/dml/select-into.xml
index 5cce78d5eb2..cf64c101d1f 100644
--- a/test/it/parser/src/main/resources/case/dml/select-into.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-into.xml
@@ -455,4 +455,13 @@
</projections>
<into-table name="t_backup" start-index="27" stop-index="34" />
</select>
+
+ <select sql-case-id="select_bulk_collect_into_oracle">
+ <from start-index="38" stop-index="46">
+ <simple-table name="dept" start-index="43" stop-index="46" />
+ </from>
+ <projections start-index="7" stop-index="12">
+ <column-projection name="deptno" start-index="7" stop-index="12" />
+ </projections>
+ </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 7c363140863..34b585411d1 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
@@ -3687,4 +3687,48 @@
</join-table>
</from>
</select>
+
+ <select sql-case-id="select_outer_apply_with_where_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_order" alias="o" start-index="14"
stop-index="22" />
+ </left>
+ <right>
+ <subquery-table alias="i" start-index="36" stop-index="97">
+ <subquery>
+ <select>
+ <projections start-index="44" stop-index="44">
+ <shorthand-projection start-index="44"
stop-index="44" />
+ </projections>
+ <from>
+ <simple-table name="t_order_item"
alias="i" start-index="51" stop-index="64" />
+ </from>
+ <where start-index="66" stop-index="94">
+ <expr>
+ <binary-operation-expression
start-index="72" stop-index="94">
+ <left>
+ <column name="order_id"
start-index="72" stop-index="81">
+ <owner name="i"
start-index="72" stop-index="72" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="order_id"
start-index="85" stop-index="94">
+ <owner name="o"
start-index="85" stop-index="85" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index 2d9e4c478f3..a397d0ebf44 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -27,6 +27,24 @@
<comment start-index="0" stop-index="19" text="/*FORCE_IMCI_NODES*/" />
<comment start-index="28" stop-index="68" text="/*+
SET_VAR(cost_threshold_for_imci=0) */" />
</select>
+
+ <select sql-case-id="select_from_regular_function_table_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <from>
+ <function-table table-alias="f">
+ <table-function function-name="my_func" text="my_func(1, 2)">
+ <parameter>
+ <literal-expression value="1" start-index="22"
stop-index="22" />
+ </parameter>
+ <parameter>
+ <literal-expression value="2" start-index="25"
stop-index="25" />
+ </parameter>
+ </table-function>
+ </function-table>
+ </from>
+ </select>
<select sql-case-id="select_position_function">
<projections start-index="7" stop-index="97">
@@ -6191,6 +6209,15 @@
</from>
</select>
+ <select sql-case-id="select_xmlpi_evalname_function">
+ <projections start-index="7" stop-index="39">
+ <expression-projection text="XMLPI(EVALNAME 'target', 'value')"
start-index="11" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="46" stop-index="49" />
+ </from>
+ </select>
+
<select sql-case-id="select_xmlquery_function">
<projections start-index="7" stop-index="95">
<expression-projection text="XMLQUERY('//student[@age=20]' PASSING
BY VALUE xcol AS x RETURNING CONTENT NULL ON EMPTY)" start-index="7"
stop-index="95" />
@@ -6243,6 +6270,29 @@
</from>
</select>
+ <select sql-case-id="select_function_table_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <function-table start-index="14" stop-index="33" table-alias="pf">
+ <table-function function-name="pipelined_func"
text="pipelined_func(1)" />
+ </function-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_from_xmltable_with_namespaces_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 'uri0',
'uri1' AS ns1, 'uri2' AS ns2), '/root' PASSING xmlcol COLUMNS dummy
VARCHAR2(10) PATH 'dummy')" />
+ </function-table>
+ </from>
+ </select>
+
<select sql-case-id="select_with_null_keyword_in_projection">
<projections start-index="7" stop-index="31">
<expression-projection text="null" alias="order_id"
start-index="7" stop-index="22">
@@ -7036,6 +7086,61 @@
</projections>
</select>
+ <select sql-case-id="select_with_sample_clause_oracle">
+ <from>
+ <simple-table name="employees" start-index="14" stop-index="22" />
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_order_by_index_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>
+ <order-by start-index="22" stop-index="42">
+ <index-item index="1" order-direction="ASC"
null-order-direction="LAST" start-index="31" stop-index="31" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_is_nan_oracle">
+ <projections start-index="7" stop-index="28">
+ <expression-projection text="to_binary_float('NaN')"
start-index="7" stop-index="28">
+ <expr>
+ <function function-name="to_binary_float"
text="to_binary_float('NaN')" start-index="7" stop-index="28">
+ <parameter>
+ <literal-expression value="NaN" start-index="23"
stop-index="27" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="35" stop-index="38" />
+ </from>
+ <where start-index="40" stop-index="74">
+ <expr>
+ <binary-operation-expression start-index="46" stop-index="74">
+ <left>
+ <function function-name="to_binary_float"
text="to_binary_float('NaN')" start-index="46" stop-index="67">
+ <parameter>
+ <literal-expression value="NaN"
start-index="62" stop-index="66" />
+ </parameter>
+ </function>
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="NAN" start-index="72"
stop-index="74" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_with_trim_function_multi">
<projections start-index="7" stop-index="203" literal-start-index="7"
literal-stop-index="203">
<expression-projection text="TRIM(' derby ')" start-index="7"
stop-index="22" literal-start-index="7" literal-stop-index="22">
@@ -13377,6 +13482,30 @@
</from>
</select>
+ <select sql-case-id="select_with_xmlnamespaces_multiple_default_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 'uri0',
DEFAULT 'uri1'), '/root' PASSING xmlcol COLUMNS dummy VARCHAR2(10) PATH
'dummy')" />
+ </function-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_from_xmltable_with_default_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('x' PASSING xmlcol COLUMNS c1
VARCHAR2(10) PATH 'p' DEFAULT 0)" />
+ </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" />
@@ -13395,6 +13524,15 @@
</from>
</select>
+ <select sql-case-id="select_table_collection_expr_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="25" />
+ </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" />
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 3596d81e62d..00e56497db5 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -140,6 +140,20 @@
</set>
</update>
+ <update sql-case-id="update_with_dblink_oracle">
+ <table start-index="7" stop-index="22">
+ <simple-table name="employees" start-index="7" stop-index="22" />
+ </table>
+ <set start-index="24" stop-index="42">
+ <assignment start-index="24" stop-index="42">
+ <column name="salary" start-index="28" stop-index="33" />
+ <assignment-value>
+ <column name="salary" start-index="37" stop-index="42" />
+ </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/ddl/alter-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
index 5bcb69492c5..51c3fb7978e 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
@@ -244,6 +244,8 @@
<sql-case id="alter_table_modify_constraint_rely1" value="ALTER TABLE time
MODIFY CONSTRAINT pk_time RELY" db-types="Oracle" />
<sql-case id="alter_table_modify_constraint_rely2" value="ALTER TABLE
times MODIFY CONSTRAINT time_pk RELY" db-types="Oracle" />
<sql-case id="alter_table_modify_constraint_rely3" value="ALTER TABLE
sales MODIFY CONSTRAINT sales_time_fk RELY" db-types="Oracle" />
+ <sql-case id="alter_table_modify_primary_key_rely" value="ALTER TABLE
sales MODIFY PRIMARY KEY RELY" db-types="Oracle" />
+ <sql-case id="alter_table_modify_collection_retrieval_locator"
value="ALTER TABLE nested_table_parent MODIFY NESTED TABLE child_tbl RETURN AS
LOCATOR" db-types="Oracle" />
<sql-case id="alter_table_enable_row_movement" value="ALTER TABLE table
ENABLE ROW MOVEMENT" db-types="Oracle" />
<sql-case id="alter_table_set_interval" value="ALTER TABLE transactions
SET INTERVAL()" db-types="Oracle" />
<sql-case id="alter_table_add_primary_key_xmldata" value="ALTER TABLE
xwarehouses ADD (PRIMARY KEY(XMLDATA."WarehouseId"))"
db-types="Oracle" />
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 50837941017..e1cf8e32cf9 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
@@ -531,4 +531,5 @@
<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-case id="create_table_with_varchar2_char_length_oracle" value="CREATE
TABLE dt_len (c VARCHAR2(10 CHAR))" 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 c83ebc7e3ed..98fe507dad5 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
@@ -23,4 +23,6 @@
<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-case id="create_object_type_not_persistable_oracle" value="CREATE
TYPE person_persist AS OBJECT (name VARCHAR2(10)) NOT PERSISTABLE"
db-types="Oracle" />
+ <sql-case id="create_varray_type_persistable_oracle" value="CREATE TYPE
number_varray_persist AS VARRAY(3) OF NUMBER PERSISTABLE" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-view.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-view.xml
index 28780d3a873..bd54900366e 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-view.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-view.xml
@@ -32,6 +32,7 @@
<sql-case id="create_view_as_simple_select3" value="CREATE VIEW emp AS
SELECT empno, ename, mgr, deptno FROM company.emp" db-types="Oracle" />
<sql-case id="create_view_as_simple_select4" value="CREATE VIEW emp_v AS
SELECT empno, ename, deptno FROM emp" db-types="Oracle" />
<sql-case id="create_view_as_simple_select5" value="CREATE VIEW hr_verify
AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, MANAGER_ID FROM EMPLOYEES"
db-types="Oracle" />
+ <sql-case id="create_or_replace_view_simple_oracle" value="CREATE OR
REPLACE VIEW v_or_replace AS SELECT 1 FROM dual" db-types="Oracle" />
<sql-case id="create_view_as_select_with_condition1" value="CREATE VIEW
emp_emp AS SELECT e1.ename, e2.empno, e2.deptno FROM emp e1, emp e2 WHERE
e1.empno = e2.empno" db-types="Oracle" />
<sql-case id="create_view_as_select_with_condition2" value="CREATE VIEW
lowsal AS SELECT * FROM employees WHERE salary < 10000" db-types="Oracle" />
<sql-case id="create_view_as_select_with_condition3" value="CREATE VIEW
employees_departments AS SELECT employee_id, last_name, department_id FROM
employees, departments WHERE employees.department_id =
departments.department_id" db-types="Oracle" />
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 975cf8f0c5a..8cf26ad2dce 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
@@ -131,6 +131,9 @@
<sql-case id="select_prediction_bounds_function" value="SELECT cust_id
FROM mining_data_apply_v WHERE PREDICTION_BOUNDS(glmr_sh_regr_sample,0.98 USING
*).LOWER = 24;" db-types="Oracle" />
<sql-case id="select_prediction_function" value="SELECT cust_gender FROM
mining_data_apply_v WHERE PREDICTION (nb_sh_clas_sample COST MODEL AUTO USING
cust_marital_status, aeducation, household_size) = 1;" db-types="Oracle" />
<sql-case id="select_prediction_details_function" value="SELECT
PREDICTION_DETAILS(DT_SH_Clas_sample using *) FROM mining_data_apply_v WHERE
occupation = 'TechSup'" db-types="Oracle" />
+ <sql-case id="select_nchar_literal_oracle" value="SELECT N'abc' FROM dual"
db-types="Oracle" />
+ <sql-case id="select_datetime_escape_literal_oracle" value="SELECT {ts
'2020-01-01 10:00:00'} FROM dual" db-types="Oracle" />
+ <sql-case id="select_nested_attribute_oracle" value="SELECT c.address.city
FROM contacts c" db-types="Oracle" />
<sql-case id="select_predict_by_function" value="SELECT PREDICT BY
point_kmeans (FEATURES position) as pos FROM (select * from kmeans_2d limit
10);" db-types="openGauss" />
<sql-case id="select_inet_function" value="SELECT inet '192.168.1.5' =
inet '192.168.1.5' AS RESULT;" db-types="openGauss" />
<sql-case id="select_inet_function_with_inet_operator" value="SELECT inet
'192.168.1.5' <<= inet '192.168.1.5' AS RESULT;" db-types="openGauss" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
index d2b3f81c063..86d924cd4e0 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
@@ -29,6 +29,7 @@
<sql-case id="select_into_with_lock_before_into" value="SELECT status FROM
t_order WHERE order_id = ? FOR UPDATE INTO @var1" db-types="MySQL" />
<sql-case id="select_into_param_without_at" value="SELECT 1 INTO a"
db-types="MySQL" />
<sql-case id="select_into_with_variable" value="SELECT select_list INTO
record_variable_name FROM table_or_view_name" db-types="Oracle" />
+ <sql-case id="select_bulk_collect_into_oracle" value="SELECT deptno BULK
COLLECT INTO v_tab FROM dept" db-types="Oracle" />
<sql-case id="select_into_table_with_try_cast_function" value="SELECT
machine.temperature,
udf.ASAEdgeUDFDemo_Class1_SquareFunction(try_cast(machine.temperature as
bigint)) INTO Output FROM Input;" db-types="SQLServer"/>
<sql-case id="select_into_table_before_from" value="SELECT * INTO
dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice
< $100;" db-types="SQLServer"/>
<sql-case id="select_into_simple_table" value="SELECT film_id, title,
rental_rate INTO TABLE film_r FROM film WHERE rating = 'R' AND rental_duration
= 5 ORDER BY title" db-types="PostgreSQL, openGauss"/>
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 3eea391c134..7ce5f7c6411 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
@@ -69,4 +69,5 @@
<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-case id="select_outer_apply_with_where_oracle" value="SELECT * FROM
t_order o OUTER APPLY (SELECT * FROM t_order_item i WHERE i.order_id =
o.order_id) i" 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 55e77ce8cfb..7861a7ddd56 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
@@ -147,6 +147,7 @@
<sql-case id="select_with_partitioned_table" value="SELECT * FROM sales
PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id,
time_id, channel_id" db-types="Oracle" />
<sql-case id="select_with_binding_tables_without_join" value="SELECT
last_name, job_id, departments.department_id, department_name FROM employees,
departments WHERE employees.department_id = departments.department_id ORDER BY
last_name, job_id" db-types="Oracle" />
<sql-case id="select_with_lateral_clause" value="SELECT * FROM employees
e, LATERAL(SELECT * FROM departments d WHERE e.department_id =
d.department_id)" db-types="Oracle" />
+ <sql-case id="select_function_table_oracle" value="SELECT * FROM
pipelined_func(1) pf" db-types="Oracle" />
<sql-case id="select_with_containers" value="SELECT * FROM
CONTAINERS(employees)" db-types="Oracle" />
<sql-case id="select_with_hierarchical_connect_by" value="SELECT
last_name, employee_id, manager_id FROM employees CONNECT BY employee_id =
manager_id ORDER BY last_name" db-types="Oracle" />
<sql-case
id="select_current_date_function_with_shorthand_regular_function" value="SELECT
* FROM t_order WHERE date = CURRENT_DATE" db-types="MySQL" />
@@ -200,10 +201,14 @@
<sql-case id="select_xmlforest_function" value="SELECT
XMLFOREST(e.employee_id AS EVALNAME 'ID', e.last_name AS name, e.salary) FROM
employees e WHERE employee_id = 204;" db-types="Oracle" />
<sql-case id="select_xmlparse_function" value="SELECT XMLPARSE(DOCUMENT
'DEPTXML' WELLFORMED) AS dept FROM DUAL;" db-types="Oracle" />
<sql-case id="select_xmlpi_function" value="SELECT XMLPI(NAME "Order
analysisComp", 'imported, reconfigured, disassembled') AS 'XMLPI' FROM
DUAL;" db-types="Oracle" />
+ <sql-case id="select_xmlpi_evalname_function" value="SELECT XMLPI(EVALNAME
'target', 'value') FROM dual;" db-types="Oracle" />
<sql-case id="select_xmlquery_function" value="SELECT
XMLQUERY('//student[@age=20]' PASSING BY VALUE xcol AS x RETURNING CONTENT NULL
ON EMPTY) FROM x_table;" db-types="Oracle" />
<sql-case id="select_xmlroot_function" value="SELECT
XMLROOT(XMLType('143598'), VERSION '1.0', STANDALONE YES) AS 'XMLROOT' FROM
DUAL;" db-types="Oracle" />
<sql-case id="select_xmlserialize_function" value="SELECT
XMLSERIALIZE(DOCUMENT c2 AS BLOB ENCODING 'UTF-8' VERSION 'a' INDENT SIZE = 0
SHOW DEFAULTS) FROM b;" db-types="Oracle" />
<sql-case id="select_from_xmltable_function" value="SELECT warehouse_name
warehouse, warehouse2.Water, warehouse2.Rail FROM warehouses,
XMLTABLE('/Warehouse' PASSING warehouses.warehouse_spec COLUMNS
"Water" varchar2(6) PATH 'WaterAccess',"Rail" varchar2(6)
PATH 'RailAccess') warehouse2;" db-types="Oracle" />
+ <sql-case id="select_from_xmltable_with_namespaces_oracle" value="SELECT *
FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'uri0', 'uri1' AS ns1, 'uri2' AS ns2),
'/root' PASSING xmlcol COLUMNS dummy VARCHAR2(10) PATH 'dummy') xt"
db-types="Oracle" />
+ <sql-case id="select_with_xmlnamespaces_multiple_default_oracle"
value="SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'uri0', DEFAULT 'uri1'),
'/root' PASSING xmlcol COLUMNS dummy VARCHAR2(10) PATH 'dummy') xt"
db-types="Oracle" />
+ <sql-case id="select_from_regular_function_table_oracle" value="SELECT *
FROM my_func(1, 2) f" db-types="Oracle" />
<sql-case id="select_with_null_keyword_in_projection" value="select null
as order_id, item_id from t_order" db-types="MySQL" />
<sql-case id="select_literal_type_cast_money" value="SELECT '$99'::money"
db-types="PostgreSQL,openGauss" />
<sql-case id="select_positional_parameter_type_cast_money" value="SELECT
$1::money" db-types="PostgreSQL,openGauss" case-types="Placeholder" />
@@ -261,6 +266,10 @@
<sql-case id="select_with_keep_clause" value="SELECT salary,MIN(salary)
KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY
department_id) 'Worst', MAX(salary) KEEP (DENSE_RANK LAST ORDER BY
commission_pct) OVER (PARTITION BY department_id) 'Best' FROM employees ORDER
BY department_id" db-types="Oracle" />
<sql-case id="select_with_corr_function" value="SELECT employee_id,
CORR(SYSDATE - hire_date, salary) FROM employees WHERE department_id in (50,
80) ORDER BY employee_id" db-types="Oracle" />
<sql-case id="select_with_db_link" value="SELECT * FROM
hr.employees@remote" db-types="Oracle" />
+ <sql-case id="select_with_sample_clause_oracle" value="SELECT * FROM
employees SAMPLE(10)" db-types="Oracle" />
+ <sql-case id="select_with_order_by_index_oracle" value="SELECT * FROM
t_order ORDER BY 1 NULLS LAST" db-types="Oracle" />
+ <sql-case id="select_is_nan_oracle" value="SELECT to_binary_float('NaN')
FROM dual WHERE to_binary_float('NaN') IS NAN" db-types="Oracle" />
+ <sql-case id="select_group_by_rollup_oracle_with_rollup" value="SELECT
department_id, COUNT(*) FROM employees GROUP BY ROLLUP(department_id)"
db-types="Oracle" />
<sql-case id="select_with_trim_function_multi" value="select TRIM(' derby
'), TRIM(BOTH ' ' FROM ' derby '), TRIM(TRAILING ' ' FROM ' derby '),
TRIM(cast (null as char(1)) FROM ' derby '), TRIM(' ' FROM cast(null as
varchar(30))), TRIM('y' FROM ' derby') FROM employees" db-types="Oracle" />
<sql-case id="select_with_trim_function_simple" value="SELECT TRIM( '.,! '
FROM ' # test .') AS Result" db-types="SQLServer" />
<sql-case id="select_with_trim_function_leading" value="SELECT
TRIM(LEADING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer,Presto" />
@@ -476,7 +485,9 @@
<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_table_collection_expr_oracle" value="SELECT * FROM
TABLE(1 + 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_from_xmltable_with_default_oracle" value="SELECT *
FROM XMLTABLE('x' PASSING xmlcol COLUMNS c1 VARCHAR2(10) PATH 'p' DEFAULT 0)
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" />
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 d791ce4c3ce..64d9c9b86e9 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
@@ -50,6 +50,7 @@
<sql-case id="update_with_dot_column_name" value="UPDATE employees SET
salary =.salary + 10 WHERE employee_id BETWEEN 1 and 10; " db-types="Oracle" />
<sql-case id="update_with_set_value_clause" value="UPDATE ot1 SET
VALUE(ot1.x) = t1(20) WHERE VALUE(ot1.x) = t1(10);" db-types="Oracle" />
<sql-case id="update_with_subquery_target_oracle" value="UPDATE (SELECT *
FROM employees) SET salary = 1" db-types="Oracle" />
+ <sql-case id="update_with_dblink_oracle" value="UPDATE employees@remote
SET salary = salary" db-types="Oracle" />
<sql-case id="update_with_open_row_set_function" value="UPDATE T SET
XmlCol = ( SELECT * FROM OPENROWSET(BULK 'C:\SampleFolder\SampleData3.txt',
SINGLE_BLOB) AS x) WHERE IntCol = 1" db-types="SQLServer"/>
<sql-case id="update_with_point_type" value="UPDATE dbo.Cities SET
Location.SetXY(23.5, 23.5) WHERE Name = 'Anchorage'" db-types="SQLServer"/>
<sql-case id="update_with_table_hint" value="UPDATE Production.Product
WITH (TABLOCK) SET ListPrice = ListPrice * 1.10 WHERE ProductNumber LIKE
'BK-%'" db-types="SQLServer"/>
