This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 58da87a0282 Support Oracle bare CROSSEDITION trigger and XMLForest
alias without AS from issue #27073 (#39570)
58da87a0282 is described below
commit 58da87a028240d69e8d1fa510b4a4889d8abb1d4
Author: Liang Zhang <[email protected]>
AuthorDate: Sun Aug 23 11:14:59 2026 +0800
Support Oracle bare CROSSEDITION trigger and XMLForest alias without AS
from issue #27073 (#39570)
* Support Oracle bare CROSSEDITION trigger and XMLForest alias without AS
from issue #27073
- Make FORWARD/REVERSE optional in triggerEditionClause, since Oracle
documents FORWARD as the default crossedition form
- Accept alias without AS in exprWithAlias and xmlAsAliasOrEvalnameExpr for
XMLATTRIBUTES, XMLELEMENT, XMLFOREST and XMLCOLATTVAL parameters
- Add parser IT cases for the CREATE OR REPLACE trigger statements and
XMLFOREST alias from the issue
Fixes #27073
* Support Oracle bare CROSSEDITION trigger and XMLForest alias without AS
from issue #27073
- Make FORWARD/REVERSE optional in triggerEditionClause, since Oracle
documents FORWARD as the default crossedition form
- Accept alias without AS in exprWithAlias and xmlAsAliasOrEvalnameExpr for
XMLATTRIBUTES, XMLELEMENT, XMLFOREST and XMLCOLATTVAL parameters
- Add parser IT cases for the CREATE OR REPLACE trigger statements and
XMLFOREST alias from the issue
Fixes #27073
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 4 +--
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 2 +-
.../src/main/resources/case/ddl/create-trigger.xml | 7 +++++
.../parser/src/main/resources/case/dml/select.xml | 22 +++++++++++++++
.../resources/sql/supported/ddl/create-trigger.xml | 31 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
.../main/resources/sql/unsupported/unsupported.xml | 2 --
7 files changed, 64 insertions(+), 5 deletions(-)
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 5996bcc8573..38937d657e5 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -1783,7 +1783,7 @@ xmlElementFunction
;
exprWithAlias
- : expr (AS alias)?
+ : expr (AS? alias)?
;
xmlAttributes
@@ -1803,7 +1803,7 @@ xmlColattvalFunction
;
xmlAsAliasOrEvalnameExpr
- :AS (alias | EVALNAME expr)
+ : AS? (alias | EVALNAME expr)
;
xmlExistsFunction
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index b66d01f0652..7bfb5c3f80f 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -751,7 +751,7 @@ referencingItem
;
triggerEditionClause
- : (FORWARD | REVERSE) CROSSEDITION
+ : (FORWARD | REVERSE)? CROSSEDITION
;
triggerOrderingClause
diff --git a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
index 74c2338b621..0e43bf26e83 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
@@ -87,6 +87,13 @@
<owner name="NEW" start-index="186" stop-index="188" />
</pseudo-column>
</create-trigger>
+ <create-trigger sql-case-id="create_trigger_with_instead_of_create" />
+ <create-trigger sql-case-id="create_trigger_with_cross_edition">
+ <comment start-index="247" stop-index="365" text="/* Trigger is
running because of serendipitous change.
 Insert new row into
table2 unless it is already there. */" />
+ <comment start-index="378" stop-index="423" text="/*+
IGNORE_ROW_ON_DUPKEY_INDEX(table2(key)) */" />
+ <comment start-index="523" stop-index="691" text="/* Trigger is
running because you are applying transform.
 If tranform has not
yet inserted new row in table2, insert new row;
 otherwise, update
new row. */" />
+ <comment start-index="716" stop-index="760" text="/*+
CHANGE_DUPKEY_ERROR_INDEX(table2(key)) */" />
+ </create-trigger>
<create-trigger sql-case-id="create_trigger_pg_after_insert">
<trigger-name name="t_merge_trigger" />
<table name="t_merge_target" start-index="47" stop-index="60" />
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 f8b2394e5e8..761d090cae2 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -6522,6 +6522,28 @@
</where>
</select>
+ <select sql-case-id="select_xmlforest_function_without_as">
+ <projections start-index="7" stop-index="58">
+ <expression-projection text="XMLFOREST(e.employee_id
"ID", e.last_name, e.salary)" start-index="7" stop-index="58" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="65" stop-index="75"
alias="e" />
+ </from>
+ <where start-index="77" stop-index="99">
+ <expr>
+ <binary-operation-expression start-index="83" stop-index="99">
+ <left>
+ <column name="employee_id" start-index="83"
stop-index="93" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="204" start-index="97"
stop-index="99" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_xmlparse_function">
<projections start-index="7" stop-index="53">
<expression-projection text="XMLPARSE(DOCUMENT 'DEPTXML'
WELLFORMED)" alias="dept" start-index="7" stop-index="53" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
index 9ff168e82c0..7776a79c802 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
@@ -103,6 +103,37 @@
<sql-case id="create_trigger_with_explicit_state_visitor_oracle"
value="CREATE OR REPLACE TRIGGER trg_proc_trigger_data AFTER INSERT ON
t_proc_trigger_source FOR EACH ROW DISABLE BEGIN NULL; END;" db-types="Oracle"
/>
<sql-case id="create_trigger_with_instead_of_nested_table" value="CREATE
OR REPLACE TRIGGER Library_trigger INSTEAD OF INSERT ON Library_view FOR EACH
ROW DECLARE Bookvar Book_t; i INTEGER; BEGIN INSERT INTO Library_table VALUES
(:NEW.Section); FOR i IN 1..:NEW.Booklist.COUNT LOOP Bookvar :=
:NEW.Booklist(i); INSERT INTO Book_table (Booknum, Section, Title, Author,
Available) VALUES (Bookvar.booknum, :NEW.Section, Bookvar.Title,
Bookvar.Author, bookvar.Available); END LOOP; END;" db- [...]
<sql-case id="create_trigger_with_pseudorecord_collection_index"
value="CREATE OR REPLACE TRIGGER trg_pseudorecord_data AFTER INSERT ON
t_pseudorecord_source FOR EACH ROW DECLARE v INTEGER; BEGIN v := :NEW.coll(i);
INSERT INTO t_pseudorecord_target VALUES (:NEW.coll(i)); END;"
db-types="Oracle" />
+ <sql-case id="create_trigger_with_instead_of_create" value="CREATE OR
REPLACE TRIGGER t
+ INSTEAD OF CREATE ON SCHEMA
+ BEGIN
+ EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';
+ END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cross_edition" value="CREATE OR REPLACE
TRIGGER trigger1
+ BEFORE INSERT OR UPDATE ON table1
+ FOR EACH ROW
+ CROSSEDITION
+DECLARE
+ row_already_present EXCEPTION;
+ PRAGMA EXCEPTION_INIT(row_already_present, -38911);
+BEGIN
+ IF APPLYING_CROSSEDITION_TRIGGER THEN
+ /* Trigger is running because of serendipitous change.

Insert new row into table2 unless it is already there. */
+ INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table2(key)) */
+ INTO table2
+ VALUES(:new.key, :new.value, to_date('1900-01-01', 'YYYY-MM-DD'));
+ ELSE
+ /* Trigger is running because you are applying transform.
 If
tranform has not yet inserted new row in table2, insert new row;

otherwise, update new row. */
+ BEGIN
+ INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(table2(key)) */
+ INTO table2
+ VALUES(:new.key, :new.value, SYSTIMESTAMP);
+ EXCEPTION WHEN row_already_present THEN
+ UPDATE table2
+ SET value = :new.value, last_updated = SYSTIMESTAMP
+ WHERE key = :new.key;
+ END;
+ END IF;
+END;" db-types="Oracle" />
<sql-case id="create_trigger_pg_after_insert" value="CREATE TRIGGER
t_merge_trigger AFTER INSERT ON t_merge_target FOR EACH ROW EXECUTE FUNCTION
merge_notify()" db-types="PostgreSQL" />
<sql-case id="create_trigger_pg_multi_events_when" value="CREATE TRIGGER
check_update BEFORE INSERT OR DELETE ON t_merge_target FOR EACH ROW WHEN
(order_id = 1) EXECUTE PROCEDURE process_order_audit()" db-types="PostgreSQL" />
<sql-case id="create_trigger_pg_constraint" value="CREATE CONSTRAINT
TRIGGER FROM t_merge_target DEFERRABLE FOR EACH ROW WHEN (order_id = 1) EXECUTE
FUNCTION merge_notify()" db-types="PostgreSQL" />
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 a786be9938d..c99d41fdf44 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
@@ -206,6 +206,7 @@
<sql-case id="select_xmlcolattval_function" value="SELECT
XMLCOLATTVAL(e.employee_id AS EVALNAME 'ID', e.last_name AS name, e.salary)
'Emp Element' FROM employees e WHERE employee_id = 204;" db-types="Oracle" />
<sql-case id="select_xmlexists_function" value="SELECT id,
XMLEXISTS('//student[@age=20]' PASSING BY VALUE xcol AS x) FROM x_table;"
db-types="Oracle" />
<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_xmlforest_function_without_as" value="SELECT
XMLFOREST(e.employee_id "ID", e.last_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" />
diff --git a/test/it/parser/src/main/resources/sql/unsupported/unsupported.xml
b/test/it/parser/src/main/resources/sql/unsupported/unsupported.xml
index 8725fc3f086..625500147d4 100644
--- a/test/it/parser/src/main/resources/sql/unsupported/unsupported.xml
+++ b/test/it/parser/src/main/resources/sql/unsupported/unsupported.xml
@@ -25,8 +25,6 @@
<sql-case
id="unsupported_refresh_table_metadata_with_single_quoted_table_name"
value="REFRESH TABLE METADATA 'T_ORDER'" db-types="ShardingSphere" />
<sql-case id="with_select" value="WITH cte AS (SELECT 0 /*! ) */ SELECT *
FROM cte a, cte b;" db-types="MySQL" />
<sql-case id="with_select_comment" value="WITH cte AS /*! ( */ SELECT 0)
SELECT * FROM cte a, cte b;" db-types="MySQL" />
- <sql-case id="create_table_as_select" value="create table agg_data_2k as
select g from generate_series(0, 1999) g;" db-types="PostgreSQL" />
- <sql-case id="create_temp_table" value="create temp table old_oids as
select relname, oid as oldoid, relfilenode as oldfilenode from pg_class where
relname like 'at_partitioned%'" db-types="PostgreSQL" />
<sql-case id="select_case_when" value="select relname,c.oid = oldoid as
orig_oid,case relfilenode when 0 then 'none' when c.oid then 'own' when
oldfilenode then 'orig' else 'OTHER' end as storage, obj_description(c.oid,
'pg_class') as desc from pg_class c left join old_oids using (relname) where
relname like 'at_partitioned%' order by relname" db-types="PostgreSQL" />
<sql-case id="unsupported_select_json_value_on_error_for_doris"
value="SELECT JSON_VALUE('{"price": "49.95"}', '$.price'
RETURNING DECIMAL(4,1) null on empty default 0 on error)" db-types="Doris" />
<sql-case
id="unsupported_alter_database_set_replica_quota_with_unit_for_doris"
value="ALTER DATABASE example_db SET REPLICA QUOTA 1G" db-types="Doris" />
