This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 4983e949b8c Fix oracle create view attribute parse rule (#29057)
4983e949b8c is described below
commit 4983e949b8c60497fbbbeda50a40e79d1d76be57
Author: niu niu <[email protected]>
AuthorDate: Fri Nov 17 08:22:20 2023 +0800
Fix oracle create view attribute parse rule (#29057)
* Fix attribute rule
* Add sql parse test
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 4 +
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 13 +-
.../src/main/resources/case/ddl/create-view.xml | 176 ++++++++++++++++++++-
.../parser/src/main/resources/case/dml/insert.xml | 146 ++++++++++++++++-
.../resources/sql/supported/ddl/create-view.xml | 7 +-
.../main/resources/sql/supported/dml/insert.xml | 8 +-
6 files changed, 340 insertions(+), 14 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index d18cb9e3b86..a52a18f899b 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -912,6 +912,10 @@ orderByItem
: (columnName | numberLiterals | expr) (ASC | DESC)? (NULLS FIRST | NULLS
LAST)?
;
+attribute
+ : (owner DOT_)? identifier
+ ;
+
attributeName
: oracleId
;
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index 1d1ca80e835..c9cddf286cd 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -2752,7 +2752,8 @@ dropFlashbackArchive
;
createDiskgroup
- : CREATE DISKGROUP diskgroupName ((HIGH | NORMAL | FLEX | EXTENDED (SITE
siteName)? | EXTERNAL) REDUNDANCY)? diskClause+ attribute?
+ : CREATE DISKGROUP diskgroupName ((HIGH | NORMAL | FLEX | EXTENDED (SITE
siteName)? | EXTERNAL) REDUNDANCY)? diskClause+
+ ( ATTRIBUTE attributeName EQ_ attributeValue (COMMA_ attributeName EQ_
attributeValue)*)?
;
diskClause
@@ -2763,14 +2764,6 @@ qualifieDiskClause
: searchString (NAME diskName)? (SIZE sizeClause)? (FORCE | NOFORCE)?
;
-attribute
- : ATTRIBUTE attributeNameAndValue (COMMA_ attributeNameAndValue)*
- ;
-
-attributeNameAndValue
- : attributeName EQ_ attributeValue
- ;
-
dropDiskgroup
: DROP DISKGROUP diskgroupName contentsClause?
;
@@ -3260,7 +3253,7 @@ modifyVolumeClause
;
diskgroupAttributes
- : SET ATTRIBUTE attributeNameAndValue
+ : SET ATTRIBUTE attributeName EQ_ attributeValue
;
modifyDiskgroupFile
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 225a9a30e3c..b02db9f8945 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
@@ -243,7 +243,7 @@
</select>
</create-view>
- <create-view sql-case-id="create_view_as_simple_select"
view-definition="SELECT * FROM laurel.emp">
+ <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" />
</view>
@@ -259,6 +259,20 @@
</select>
</create-view>
+ <create-view sql-case-id="create_view_as_simple_select2"
view-definition="SELECT * FROM scott.dept">
+ <view name="dept" start-index="12" stop-index="15" />
+ <select>
+ <projections start-index="27" stop-index="27">
+ <shorthand-projection start-index="27" stop-index="27" />
+ </projections>
+ <from>
+ <simple-table name="dept" start-index="34" stop-index="43">
+ <owner name="scott" start-index="34" stop-index="38" />
+ </simple-table>
+ </from>
+ </select>
+ </create-view>
+
<create-view sql-case-id="create_view_as_where_where"
view-definition="SELECT employee_id, last_name, department_id FROM employees,
departments WHERE employees.department_id = departments.department_id">
<view name="employees_departments" start-index="12" stop-index="32" />
<select>
@@ -410,4 +424,164 @@
</combine>
</select>
</create-view>
+
+ <create-view sql-case-id="create_view_with_object_identifier1"
view-definition="SELECT e.empnum, e.ename, e.salary, e.job FROM emp_table e
WHERE job = 'Developer'">
+ <view name="emp_view1" start-index="12" stop-index="20" />
+ <select>
+ <projections start-index="77" stop-index="110">
+ <column-projection name="empnum" start-index="77"
stop-index="84">
+ <owner name="e" start-index="77" stop-index="77" />
+ </column-projection>
+ <column-projection name="ename" start-index="87"
stop-index="93">
+ <owner name="e" start-index="87" stop-index="87" />
+ </column-projection>
+ <column-projection name="salary" start-index="96"
stop-index="103">
+ <owner name="e" start-index="96" stop-index="96" />
+ </column-projection>
+ <column-projection name="job" start-index="106"
stop-index="110">
+ <owner name="e" start-index="106" stop-index="106" />
+ </column-projection>
+ </projections>
+ <from>
+ <simple-table alias="e" name="emp_table" start-index="117"
stop-index="127" />
+ </from>
+ <where start-index="129" stop-index="151">
+ <expr>
+ <binary-operation-expression start-index="135"
stop-index="151">
+ <left>
+ <column name="job" start-index="135"
stop-index="137" />
+ </left>
+ <right>
+ <literal-expression value="Developer"
start-index="141" stop-index="151" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_with_object_identifier2"
view-definition="SELECT d.deptno, d.deptname,
address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr FROM dept
d">
+ <view name="dept_view" start-index="12" stop-index="20" />
+ <select>
+ <projections start-index="74" stop-index="163">
+ <column-projection name="deptno" start-index="74"
stop-index="81">
+ <owner name="d" start-index="74" stop-index="74" />
+ </column-projection>
+ <column-projection name="deptname" start-index="84"
stop-index="93">
+ <owner name="d" start-index="84" stop-index="84" />
+ </column-projection>
+ <expression-projection alias="deptaddr"
text="address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip)"
start-index="96" stop-index="163">
+ <expr>
+ <function function-name="address_t"
text="address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip)"
start-index="96" stop-index="151">
+ <parameter>
+ <column name="deptstreet" start-index="106"
stop-index="117">
+ <owner name="d" start-index="106"
stop-index="106" />
+ </column>
+ </parameter>
+ <parameter>
+ <column name="deptcity" start-index="119"
stop-index="128">
+ <owner name="d" start-index="119"
stop-index="119" />
+ </column>
+ </parameter>
+ <parameter>
+ <column name="deptstate" start-index="130"
stop-index="140">
+ <owner name="d" start-index="130"
stop-index="130" />
+ </column>
+ </parameter>
+ <parameter>
+ <column name="deptzip" start-index="142"
stop-index="150">
+ <owner name="d" start-index="142"
stop-index="142" />
+ </column>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table alias="d" name="dept" start-index="170"
stop-index="175" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_host_table"
view-definition="SELECT a.empno, a.ename, b.dname FROM scott.emp a,
[email protected] b WHERE a.deptno = b.deptno">
+ <view name="company" start-index="12" stop-index="18" />
+ <select>
+ <projections start-index="30" stop-index="54">
+ <column-projection name="empno" start-index="30"
stop-index="36">
+ <owner name="a" start-index="30" stop-index="30" />
+ </column-projection>
+ <column-projection name="ename" start-index="39"
stop-index="45">
+ <owner name="a" start-index="39" stop-index="39" />
+ </column-projection>
+ <column-projection name="dname" start-index="48"
stop-index="54">
+ <owner name="b" start-index="48" stop-index="48" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" alias="a" start-index="61"
stop-index="71">
+ <owner name="scott" start-index="61"
stop-index="65" />
+ </simple-table>
+ </left>
+ <right>
+ <simple-table name="dept" alias="b" start-index="74"
stop-index="100">
+ <owner name="jward" start-index="74"
stop-index="78" />
+ </simple-table>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="102" stop-index="126">
+ <expr>
+ <binary-operation-expression start-index="108"
stop-index="126">
+ <left>
+ <column name="deptno" start-index="108"
stop-index="115">
+ <owner name="a" start-index="108"
stop-index="108" />
+ </column>
+ </left>
+ <right>
+ <column name="deptno" start-index="119"
stop-index="126">
+ <owner name="b" start-index="119"
stop-index="119" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_function_group_by_order_by"
view-definition="SELECT deptno, MIN(sal), AVG(sal), MAX(sal) FROM emp GROUP BY
deptno ORDER BY deptno">
+ <view name="dept_salaries" start-index="12" stop-index="24" />
+ <select>
+ <projections start-index="36" stop-index="71">
+ <column-projection name="deptno" start-index="36"
stop-index="41" />
+ <aggregation-projection type="MIN" expression="MIN(sal)"
start-index="44" stop-index="51">
+ <parameter>
+ <column name="sal" start-index="48" stop-index="50" />
+ </parameter>
+ </aggregation-projection>
+ <aggregation-projection type="AVG" expression="AVG(sal)"
start-index="54" stop-index="61">
+ <parameter>
+ <column name="sal" start-index="58" stop-index="60" />
+ </parameter>
+ </aggregation-projection>
+ <aggregation-projection type="MAX" expression="MAX(sal)"
start-index="64" stop-index="71">
+ <parameter>
+ <column name="sal" start-index="68" stop-index="70" />
+ </parameter>
+ </aggregation-projection>
+ </projections>
+ <from>
+ <simple-table name="emp" start-index="78" stop-index="80" />
+ </from>
+ <group-by>
+ <column-item name="deptno" start-index="91" stop-index="96" />
+ </group-by>
+ <order-by>
+ <column-item name="deptno" order-direction="ASC"
start-index="107" stop-index="112" />
+ </order-by>
+ </select>
+ </create-view>
</sql-parser-test-cases>
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 441c2d12fae..f9bae458b1a 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -17,7 +17,7 @@
-->
<sql-parser-test-cases>
- <insert sql-case-id="insert_into_values">
+ <insert sql-case-id="insert_into_values1">
<table name="test_nested" start-index="12" stop-index="22" />
<columns start-index="23" stop-index="23" />
<values>
@@ -29,6 +29,150 @@
</values>
</insert>
+ <insert sql-case-id="insert_into_values2">
+ <table name="emp_table" start-index="12" stop-index="20" />
+ <columns start-index="21" stop-index="21" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="29"
stop-index="29" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="John" start-index="31"
stop-index="36" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="1000.00" start-index="38"
stop-index="44" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Architect" start-index="46"
stop-index="56" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_values3">
+ <table name="emp_table" start-index="12" stop-index="20" />
+ <columns start-index="21" stop-index="21" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="2" start-index="29"
stop-index="29" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Robert" start-index="31"
stop-index="38" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="900.00" start-index="40"
stop-index="45" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Developer" start-index="47"
stop-index="57" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_values4">
+ <table name="emp_table" start-index="12" stop-index="20" />
+ <columns start-index="21" stop-index="21" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="3" start-index="29"
stop-index="29" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="James" start-index="31"
stop-index="37" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="2000.00" start-index="39"
stop-index="45" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Director" start-index="47"
stop-index="56" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_values5">
+ <table name="dept" start-index="12" stop-index="15" />
+ <columns start-index="16" stop-index="16" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="24"
stop-index="24" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Sales" start-index="26"
stop-index="32" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="500 Oracle pkwy"
start-index="34" stop-index="50" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Redwood S" start-index="52"
stop-index="62" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="CA" start-index="64"
stop-index="67" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="94065" start-index="69"
stop-index="75" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_values6">
+ <table name="dept" start-index="12" stop-index="15" />
+ <columns start-index="16" stop-index="16" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="2" start-index="24"
stop-index="24" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="ST" start-index="26"
stop-index="29" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="400 Oracle Pkwy"
start-index="31" stop-index="47" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Redwood S" start-index="49"
stop-index="59" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="CA" start-index="61"
stop-index="64" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="94065" start-index="66"
stop-index="72" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_values7">
+ <table name="dept" start-index="12" stop-index="15" />
+ <columns start-index="16" stop-index="16" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="3" start-index="24"
stop-index="24" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Apps" start-index="26"
stop-index="31" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="300 Oracle pkwy"
start-index="33" stop-index="49" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Redwood S" start-index="51"
stop-index="61" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="CA" start-index="63"
stop-index="66" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="94065" start-index="68"
stop-index="74" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
<insert sql-case-id="insert_with_all_placeholders" parameters="1, 1,
'init'">
<table name="t_order" start-index="12" stop-index="18" />
<columns start-index="20" stop-index="46">
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 1c7de9f5fc7..c9c08280ff9 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
@@ -25,9 +25,14 @@
<sql-case id="create_view_with_recursive" value="CREATE RECURSIVE VIEW
public.nums_1_100 (n) AS VALUES (1) UNION ALL SELECT n+1 FROM nums_1_100 WHERE
n = 100" db-types="PostgreSQL,openGauss" />
<sql-case id="create_view_with_option" value="CREATE OR REPLACE TEMP view
order_view (order_id,user_id) WITH (security_barrier=TRUE) AS SELECT * FROM
t_order" db-types="PostgreSQL,openGauss" />
<sql-case id="create_or_replace_view_with_select" value="create or replace
view named_moons as select p.name planet, p.class, m.name moon, m.radius from
planets p, moons m where p.name = m.planet_name(+) and m.name(+) not like
'S/%'" db-types="Oracle" />
- <sql-case id="create_view_as_simple_select" value="CREATE VIEW
laurel.employee AS SELECT * FROM laurel.emp" db-types="Oracle" />
+ <sql-case id="create_view_as_simple_select1" value="CREATE VIEW
laurel.employee AS SELECT * FROM laurel.emp" db-types="Oracle" />
+ <sql-case id="create_view_as_simple_select2" value="CREATE VIEW dept AS
SELECT * FROM scott.dept" db-types="Oracle" />
<sql-case id="create_view_as_where_where" 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" />
<sql-case id="create_view_select_partition" value="CREATE VIEW
Q1_2000_sales AS SELECT * FROM sales PARTITION (SALES_Q1_2000)"
db-types="Oracle" />
<sql-case id="create_view_select_with_check_option" value="CREATE VIEW
clerk AS SELECT employee_id, last_name, department_id, job_id FROM employees
WHERE job_id = 'PU_CLERK' or job_id = 'SH_CLERK' or job_id = 'ST_CLERK' WITH
CHECK OPTION" db-types="Oracle" />
<sql-case id="create_view_select_union" value="CREATE VIEW
all_employees_vw AS (SELECT employee_id, last_name, job_id, commission_pct,
department_id FROM employees) UNION (SELECT employee_id, last_name, job_id,
commission_pct, department_id FROM contract_workers)" db-types="Oracle" />
+ <sql-case id="create_view_with_object_identifier1" value="CREATE VIEW
emp_view1 OF employee_t WITH OBJECT IDENTIFIER (empno) AS SELECT e.empnum,
e.ename, e.salary, e.job FROM emp_table e WHERE job = 'Developer'"
db-types="Oracle" />
+ <sql-case id="create_view_with_object_identifier2" value="CREATE VIEW
dept_view OF dept_t WITH OBJECT IDENTIFIER (deptno) AS SELECT d.deptno,
d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS
deptaddr FROM dept d" db-types="Oracle" />
+ <sql-case id="create_view_select_host_table" value="CREATE VIEW company AS
SELECT a.empno, a.ename, b.dname FROM scott.emp a, [email protected] b
WHERE a.deptno = b.deptno" db-types="Oracle" />
+ <sql-case id="create_view_select_function_group_by_order_by" value="CREATE
VIEW dept_salaries AS SELECT deptno, MIN(sal), AVG(sal), MAX(sal) FROM emp
GROUP BY deptno ORDER BY deptno" db-types="Oracle" />
</sql-cases>
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 e0b952221e6..a1a9d530623 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
@@ -17,7 +17,13 @@
-->
<sql-cases>
- <sql-case id="insert_into_values" value="INSERT INTO test_nested
VALUES(1)" db-types="MySQL" />
+ <sql-case id="insert_into_values1" value="INSERT INTO test_nested
VALUES(1)" db-types="MySQL" />
+ <sql-case id="insert_into_values2" value="insert into emp_table
values(1,'John',1000.00,'Architect')" db-types="Oracle" />
+ <sql-case id="insert_into_values3" value="insert into emp_table
values(2,'Robert',900.00,'Developer')" db-types="Oracle" />
+ <sql-case id="insert_into_values4" value="insert into emp_table
values(3,'James',2000.00,'Director')" db-types="Oracle" />
+ <sql-case id="insert_into_values5" value="insert into dept
values(1,'Sales','500 Oracle pkwy','Redwood S','CA','94065')" db-types="Oracle"
/>
+ <sql-case id="insert_into_values6" value="insert into dept
values(2,'ST','400 Oracle Pkwy','Redwood S','CA','94065')" db-types="Oracle" />
+ <sql-case id="insert_into_values7" value="insert into dept
values(3,'Apps','300 Oracle pkwy','Redwood S','CA','94065');" db-types="Oracle"
/>
<sql-case id="insert_with_all_placeholders" value="INSERT INTO t_order
(order_id, user_id, status) VALUES (?, ?, ?)" />
<sql-case id="insert_with_historical_type_cast_syntax" value="INSERT INTO
t_order (order_id, user_id, status) VALUES (?, ?::int4, ?::text)"
db-types="PostgreSQL,openGauss" />
<sql-case id="insert_with_now_function" value="INSERT INTO t_order_item
(item_id, order_id, user_id, status, creation_date) VALUES (?, ?, ?, 'insert',
now())" db-types="MySQL" />
