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 b7fe6eaeb87 Fix oracle create view parse rule (#29075)
b7fe6eaeb87 is described below
commit b7fe6eaeb8700a4613de47d5f035ea6942071696
Author: niu niu <[email protected]>
AuthorDate: Mon Nov 20 09:02:51 2023 +0800
Fix oracle create view parse rule (#29075)
---
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 4 +-
.../src/main/resources/case/ddl/create-view.xml | 781 ++++++++++++++++++++-
.../parser/src/main/resources/case/dml/select.xml | 27 +
.../resources/sql/supported/ddl/create-view.xml | 25 +-
.../main/resources/sql/supported/dml/select.xml | 1 +
5 files changed, 833 insertions(+), 5 deletions(-)
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 c9cddf286cd..a0620e14401 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
@@ -22,8 +22,8 @@ import BaseRule, DCLStatement, DMLStatement;
createView
: CREATE (OR REPLACE)? (NO? FORCE)? (EDITIONING | EDITIONABLE EDITIONING?
| NONEDITIONABLE)? VIEW viewName
( SHARING EQ_ (METADATA | DATA | EXTENDED DATA | NONE))?
- ( LP_ (alias (VISIBLE | INVISIBLE)? inlineConstraint* (COMMA_ alias
(VISIBLE | INVISIBLE)? inlineConstraint*)*
- | outOfLineConstraint) RP_ | objectViewClause | xmlTypeViewClause)?
+ ( LP_ ((alias (VISIBLE | INVISIBLE)? inlineConstraint* |
outOfLineConstraint) (COMMA_ (alias (VISIBLE | INVISIBLE)? inlineConstraint* |
outOfLineConstraint))*) RP_
+ | objectViewClause | xmlTypeViewClause)?
( DEFAULT COLLATION collationName)? (BEQUEATH (CURRENT_USER | DEFINER))?
AS select subqueryRestrictionClause?
( CONTAINER_MAP | CONTAINERS_DEFAULT)?
;
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 b02db9f8945..dd05e5686e3 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,6 +273,460 @@
</select>
</create-view>
+ <create-view sql-case-id="create_view_as_simple_select3"
view-definition="SELECT empno, ename, mgr, deptno FROM company.emp">
+ <view name="emp" start-index="12" stop-index="14" />
+ <select>
+ <projections start-index="26" stop-index="50">
+ <column-projection name="empno" start-index="26"
stop-index="30" />
+ <column-projection name="ename" start-index="33"
stop-index="37" />
+ <column-projection name="mgr" start-index="40" stop-index="42"
/>
+ <column-projection name="deptno" start-index="45"
stop-index="50" />
+ </projections>
+ <from>
+ <simple-table name="emp" start-index="57" stop-index="67">
+ <owner name="company" start-index="57" stop-index="63" />
+ </simple-table>
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_simple_select4"
view-definition="SELECT empno, ename, deptno FROM emp">
+ <view name="emp_v" start-index="12" stop-index="16" />
+ <select>
+ <projections start-index="28" stop-index="47">
+ <column-projection name="empno" start-index="28"
stop-index="32" />
+ <column-projection name="ename" start-index="35"
stop-index="39" />
+ <column-projection name="deptno" start-index="42"
stop-index="47" />
+ </projections>
+ <from>
+ <simple-table name="emp" start-index="54" stop-index="56" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_simple_select5"
view-definition="SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, MANAGER_ID
FROM EMPLOYEES">
+ <view name="hr_verify" start-index="12" stop-index="20" />
+ <select>
+ <projections start-index="32" stop-index="84">
+ <column-projection name="EMPLOYEE_ID" start-index="32"
stop-index="42" />
+ <column-projection name="FIRST_NAME" start-index="45"
stop-index="54" />
+ <column-projection name="LAST_NAME" start-index="57"
stop-index="65" />
+ <column-projection name="EMAIL" start-index="68"
stop-index="72" />
+ <column-projection name="MANAGER_ID" start-index="75"
stop-index="84" />
+ </projections>
+ <from>
+ <simple-table name="EMPLOYEES" start-index="91"
stop-index="99" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition1"
view-definition="SELECT e1.ename, e2.empno, e2.deptno FROM emp e1, emp e2 WHERE
e1.empno = e2.empno">
+ <view name="emp_emp" start-index="12" stop-index="18" />
+ <select>
+ <projections start-index="30" stop-index="58">
+ <column-projection name="ename" start-index="30"
stop-index="37">
+ <owner name="e1" start-index="30" stop-index="31" />
+ </column-projection>
+ <column-projection name="empno" start-index="40"
stop-index="47">
+ <owner name="e2" start-index="40" stop-index="41" />
+ </column-projection>
+ <column-projection name="deptno" start-index="50"
stop-index="58">
+ <owner name="e2" start-index="50" stop-index="51" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" alias="e1" start-index="65"
stop-index="70" />
+ </left>
+ <right>
+ <simple-table name="emp" alias="e2" start-index="73"
stop-index="78" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="80" stop-index="104">
+ <expr>
+ <binary-operation-expression start-index="86"
stop-index="104">
+ <left>
+ <column name="empno" start-index="86"
stop-index="93">
+ <owner name="e1" start-index="86"
stop-index="87" />
+ </column>
+ </left>
+ <right>
+ <column name="empno" start-index="97"
stop-index="104">
+ <owner name="e2" start-index="97"
stop-index="98" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition2"
view-definition="SELECT * FROM employees WHERE salary < 10000">
+ <view name="lowsal" start-index="12" stop-index="17" />
+ <select>
+ <projections start-index="29" stop-index="29">
+ <shorthand-projection start-index="29" stop-index="29" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="36"
stop-index="44" />
+ </from>
+ <where start-index="47" stop-index="66">
+ <expr>
+ <binary-operation-expression start-index="53"
stop-index="66">
+ <left>
+ <column name="salary" start-index="53"
stop-index="58" />
+ </left>
+ <right>
+ <literal-expression value="10000" start-index="62"
stop-index="66" />
+ </right>
+ <operator><</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition3"
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>
+ <projections start-index="44" stop-index="80">
+ <column-projection name="employee_id" start-index="44"
stop-index="54" />
+ <column-projection name="last_name" start-index="57"
stop-index="65" />
+ <column-projection name="department_id" start-index="68"
stop-index="80" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="employees" start-index="87"
stop-index="95" />
+ </left>
+ <right>
+ <simple-table name="departments" start-index="98"
stop-index="108" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="110" stop-index="166">
+ <expr>
+ <binary-operation-expression start-index="116"
stop-index="166">
+ <left>
+ <column name="department_id" start-index="116"
stop-index="138">
+ <owner name="employees" start-index="116"
stop-index="124" />
+ </column>
+ </left>
+ <right>
+ <column name="department_id" start-index="142"
stop-index="166">
+ <owner name="departments" start-index="142"
stop-index="152" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition4"
view-definition="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'">
+ <view name="clerk" start-index="12" stop-index="16" />
+ <select>
+ <projections start-index="28" stop-index="72">
+ <column-projection name="employee_id" start-index="28"
stop-index="38" />
+ <column-projection name="last_name" start-index="41"
stop-index="49" />
+ <column-projection name="department_id" start-index="52"
stop-index="64" />
+ <column-projection name="job_id" start-index="67"
stop-index="72" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="79"
stop-index="87" />
+ </from>
+ <where start-index="89" stop-index="159">
+ <expr>
+ <binary-operation-expression start-index="95"
stop-index="159">
+ <left>
+ <binary-operation-expression start-index="95"
stop-index="136">
+ <left>
+ <binary-operation-expression
start-index="95" stop-index="113">
+ <left>
+ <column name="job_id"
start-index="95" stop-index="100" />
+ </left>
+ <right>
+ <literal-expression
value="PU_CLERK" start-index="104" stop-index="113" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression
start-index="118" stop-index="136">
+ <left>
+ <column name="job_id"
start-index="118" stop-index="123" />
+ </left>
+ <right>
+ <literal-expression
value="SH_CLERK" start-index="127" stop-index="136" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </right>
+ <operator>or</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="141"
stop-index="159">
+ <left>
+ <column name="job_id" start-index="141"
stop-index="146" />
+ </left>
+ <right>
+ <literal-expression value="ST_CLERK"
start-index="150" stop-index="159" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </right>
+ <operator>or</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition5"
view-definition="SELECT last_name, salary*12 annual_salary FROM employees WHERE
department_id = 20">
+ <view name="emp_view" start-index="12" stop-index="19" />
+ <select>
+ <projections start-index="31" stop-index="64">
+ <column-projection name="last_name" start-index="31"
stop-index="39" />
+ <expression-projection alias="annual_salary" text="salary*12"
start-index="42" stop-index="64">
+ <expr>
+ <binary-operation-expression start-index="42"
stop-index="50">
+ <left>
+ <column name="salary" start-index="42"
stop-index="47" />
+ </left>
+ <right>
+ <literal-expression value="12"
start-index="49" stop-index="50" />
+ </right>
+ <operator>*</operator>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="71"
stop-index="79" />
+ </from>
+ <where start-index="81" stop-index="104">
+ <expr>
+ <binary-operation-expression start-index="87"
stop-index="104">
+ <left>
+ <column name="department_id" start-index="87"
stop-index="99" />
+ </left>
+ <right>
+ <literal-expression value="20" start-index="103"
stop-index="104" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition6"
view-definition="SELECT empno, ename, deptno FROM emp WHERE sal > 1000">
+ <view name="emp_v_2" start-index="12" stop-index="18" />
+ <select>
+ <projections start-index="30" stop-index="49">
+ <column-projection name="empno" start-index="30"
stop-index="34" />
+ <column-projection name="ename" start-index="37"
stop-index="41" />
+ <column-projection name="deptno" start-index="44"
stop-index="49" />
+ </projections>
+ <from>
+ <simple-table name="emp" start-index="56" stop-index="58" />
+ </from>
+ <where start-index="60" stop-index="75">
+ <expr>
+ <binary-operation-expression start-index="66"
stop-index="75">
+ <left>
+ <column name="sal" start-index="66"
stop-index="68" />
+ </left>
+ <right>
+ <literal-expression value="1000" start-index="72"
stop-index="75" />
+ </right>
+ <operator>></operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition7"
view-definition="SELECT e.*, Loc, d.dname FROM emp_v e, dept d WHERE e.deptno =
d.deptno (+)">
+ <view name="emp_dept_oj1" start-index="12" stop-index="23" />
+ <select>
+ <projections start-index="35" stop-index="51">
+ <shorthand-projection start-index="35" stop-index="37">
+ <owner name="e" start-index="35" stop-index="35" />
+ </shorthand-projection>
+ <column-projection name="Loc" start-index="40" stop-index="42"
/>
+ <column-projection name="dname" start-index="45"
stop-index="51">
+ <owner name="d" start-index="45" stop-index="45" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp_v" alias="e" start-index="58"
stop-index="64" />
+ </left>
+ <right>
+ <simple-table name="dept" alias="d" start-index="67"
stop-index="72" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="74" stop-index="102">
+ <expr>
+ <binary-operation-expression start-index="80"
stop-index="102">
+ <left>
+ <column name="deptno" start-index="80"
stop-index="87">
+ <owner name="e" start-index="80"
stop-index="80" />
+ </column>
+ </left>
+ <right>
+ <outer-join-expression>
+ <column name="deptno" start-index="91"
stop-index="98">
+ <owner name="d" start-index="91"
stop-index="91" />
+ </column>
+ <join-operator>(+)</join-operator>
+ </outer-join-expression>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition8"
view-definition="SELECT employee_id, last_name, job_id, salary, commission_pct,
department_id FROM employees WHERE department_id = 50">
+ <view name="employees_50_vw" start-index="12" stop-index="26" />
+ <select>
+ <projections start-index="38" stop-index="106">
+ <column-projection name="employee_id" start-index="38"
stop-index="48" />
+ <column-projection name="last_name" start-index="51"
stop-index="59" />
+ <column-projection name="job_id" start-index="62"
stop-index="67" />
+ <column-projection name="salary" start-index="70"
stop-index="75" />
+ <column-projection name="commission_pct" start-index="78"
stop-index="91" />
+ <column-projection name="department_id" start-index="94"
stop-index="106" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="113"
stop-index="121" />
+ </from>
+ <where start-index="123" stop-index="146">
+ <expr>
+ <binary-operation-expression start-index="129"
stop-index="146">
+ <left>
+ <column name="department_id" start-index="129"
stop-index="141" />
+ </left>
+ <right>
+ <literal-expression value="50" start-index="145"
stop-index="146" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition9"
view-definition="SELECT d.department_id, d.department_name, l.location_id,
l.city FROM departments d, locations l WHERE d.location_id = l.location_id">
+ <view name="locations_view" start-index="12" stop-index="25" />
+ <select>
+ <projections start-index="37" stop-index="93">
+ <column-projection name="department_id" start-index="37"
stop-index="51">
+ <owner name="d" start-index="37" stop-index="37" />
+ </column-projection>
+ <column-projection name="department_name" start-index="54"
stop-index="70">
+ <owner name="d" start-index="54" stop-index="54" />
+ </column-projection>
+ <column-projection name="location_id" start-index="73"
stop-index="85">
+ <owner name="l" start-index="73" stop-index="73" />
+ </column-projection>
+ <column-projection name="city" start-index="88"
stop-index="93">
+ <owner name="l" start-index="88" stop-index="88" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="departments" alias="d"
start-index="100" stop-index="112" />
+ </left>
+ <right>
+ <simple-table name="locations" alias="l"
start-index="115" stop-index="125" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="127" stop-index="161">
+ <expr>
+ <binary-operation-expression start-index="133"
stop-index="161">
+ <left>
+ <column name="location_id" start-index="133"
stop-index="145">
+ <owner name="d" start-index="133"
stop-index="133" />
+ </column>
+ </left>
+ <right>
+ <column name="location_id" start-index="149"
stop-index="161">
+ <owner name="l" start-index="149"
stop-index="149" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_as_select_with_condition10"
view-definition="SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e,
dept d WHERE e.deptno (+) = d.deptno">
+ <view name="emp_dept_oj2" start-index="12" stop-index="23" />
+ <select>
+ <projections start-index="35" stop-index="76">
+ <column-projection name="empno" start-index="35"
stop-index="41">
+ <owner name="e" start-index="35" stop-index="35" />
+ </column-projection>
+ <column-projection name="ename" start-index="44"
stop-index="50">
+ <owner name="e" start-index="44" stop-index="44" />
+ </column-projection>
+ <column-projection name="deptno" start-index="53"
stop-index="60">
+ <owner name="e" start-index="53" stop-index="53" />
+ </column-projection>
+ <column-projection name="dname" start-index="63"
stop-index="69">
+ <owner name="d" start-index="63" stop-index="63" />
+ </column-projection>
+ <column-projection name="loc" start-index="72" stop-index="76">
+ <owner name="d" start-index="72" stop-index="72" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" alias="e" start-index="83"
stop-index="87" />
+ </left>
+ <right>
+ <simple-table name="dept" alias="d" start-index="90"
stop-index="95" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="97" stop-index="125">
+ <expr>
+ <binary-operation-expression start-index="103"
stop-index="125">
+ <left>
+ <outer-join-expression>
+ <column name="deptno" start-index="103"
stop-index="110">
+ <owner name="e" start-index="103"
stop-index="103" />
+ </column>
+ <join-operator>(+)</join-operator>
+ </outer-join-expression>
+ </left>
+ <right>
+ <column name="deptno" start-index="118"
stop-index="125">
+ <owner name="d" start-index="118"
stop-index="118" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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>
@@ -323,7 +777,7 @@
</select>
</create-view>
- <create-view sql-case-id="create_view_select_with_check_option"
view-definition="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'">
+ <create-view sql-case-id="create_view_select_with_check_option1"
view-definition="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'">
<view name="clerk" start-index="12" stop-index="16" />
<select>
<projections start-index="28" stop-index="72">
@@ -383,6 +837,47 @@
</select>
</create-view>
+ <create-view sql-case-id="create_view_select_with_check_option2"
view-definition="SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE
e1.mgr = e2.empno">
+ <view name="emp_mgr" start-index="12" stop-index="18" />
+ <select>
+ <projections start-index="30" stop-index="53">
+ <column-projection name="ename" start-index="30"
stop-index="37">
+ <owner name="e1" start-index="30" stop-index="31" />
+ </column-projection>
+ <column-projection name="ename" alias="mname" start-index="40"
stop-index="53">
+ <owner name="e2" start-index="40" stop-index="41" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" alias="e1" start-index="60"
stop-index="65" />
+ </left>
+ <right>
+ <simple-table name="emp" alias="e2" start-index="68"
stop-index="73" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="75" stop-index="97">
+ <expr>
+ <binary-operation-expression start-index="81"
stop-index="97">
+ <left>
+ <column name="mgr" start-index="81"
stop-index="86">
+ <owner start-index="81" stop-index="82"
name="e1" />
+ </column>
+ </left>
+ <right>
+ <column name="empno" start-index="90"
stop-index="97">
+ <owner start-index="90" stop-index="91"
name="e2" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
<create-view sql-case-id="create_view_select_union"
view-definition="(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)">
<view name="all_employees_vw" start-index="12" stop-index="27" />
<select>
@@ -584,4 +1079,288 @@
</order-by>
</select>
</create-view>
+
+ <create-view sql-case-id="create_view_select_in1" view-definition="SELECT
ename, empno, job, dname FROM emp, dept WHERE emp.deptno IN (10, 30) AND
emp.deptno = dept.deptno">
+ <view name="division1_staff" start-index="12" stop-index="26" />
+ <select>
+ <projections start-index="38" stop-index="61">
+ <column-projection name="ename" start-index="38"
stop-index="42" />
+ <column-projection name="empno" start-index="45"
stop-index="49" />
+ <column-projection name="job" start-index="52" stop-index="54"
/>
+ <column-projection name="dname" start-index="57"
stop-index="61" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" start-index="68"
stop-index="70" />
+ </left>
+ <right>
+ <simple-table name="dept" start-index="73"
stop-index="76" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="78" stop-index="134">
+ <expr>
+ <binary-operation-expression start-index="84"
stop-index="134">
+ <left>
+ <in-expression start-index="84" stop-index="105">
+ <left>
+ <column name="deptno" start-index="84"
stop-index="93">
+ <owner name="emp" start-index="84"
stop-index="86" />
+ </column>
+ </left>
+ <right>
+ <list-expression start-index="98"
stop-index="105">
+ <items>
+ <literal-expression
start-index="99" stop-index="100" value="10" />
+ </items>
+ <items>
+ <literal-expression
start-index="103" stop-index="104" value="30" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="111"
stop-index="134">
+ <left>
+ <column name="deptno" start-index="111"
stop-index="120">
+ <owner name="emp" start-index="111"
stop-index="113" />
+ </column>
+ </left>
+ <right>
+ <column name="deptno" start-index="124"
stop-index="134">
+ <owner name="dept" start-index="124"
stop-index="127" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_in2" view-definition="SELECT
emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc FROM emp, dept
WHERE emp.deptno = dept.deptno AND dept.loc IN ('DALLAS', 'NEW YORK',
'BOSTON')">
+ <view name="emp_dept" start-index="12" stop-index="19" />
+ <select>
+ <projections start-index="31" stop-index="93">
+ <column-projection name="empno" start-index="31"
stop-index="39">
+ <owner name="emp" start-index="31" stop-index="33" />
+ </column-projection>
+ <column-projection name="ename" start-index="42"
stop-index="50">
+ <owner name="emp" start-index="42" stop-index="44" />
+ </column-projection>
+ <column-projection name="deptno" start-index="53"
stop-index="62">
+ <owner name="emp" start-index="53" stop-index="55" />
+ </column-projection>
+ <column-projection name="sal" start-index="65" stop-index="71">
+ <owner name="emp" start-index="65" stop-index="67" />
+ </column-projection>
+ <column-projection name="dname" start-index="74"
stop-index="83">
+ <owner name="dept" start-index="74" stop-index="77" />
+ </column-projection>
+ <column-projection name="loc" start-index="86" stop-index="93">
+ <owner name="dept" start-index="86" stop-index="89" />
+ </column-projection>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="emp" start-index="100"
stop-index="102" />
+ </left>
+ <right>
+ <simple-table name="dept" start-index="105"
stop-index="108" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="110" stop-index="188">
+ <expr>
+ <binary-operation-expression start-index="116"
stop-index="188">
+ <left>
+ <binary-operation-expression start-index="116"
stop-index="139">
+ <left>
+ <column name="deptno" start-index="116"
stop-index="125">
+ <owner name="emp" start-index="116"
stop-index="118" />
+ </column>
+ </left>
+ <right>
+ <column name="deptno" start-index="129"
stop-index="139">
+ <owner name="dept" start-index="129"
stop-index="132" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <in-expression start-index="145" stop-index="188">
+ <left>
+ <column name="loc" start-index="145"
stop-index="152">
+ <owner name="dept" start-index="145"
stop-index="148" />
+ </column>
+ </left>
+ <right>
+ <list-expression start-index="157"
stop-index="188">
+ <items>
+ <literal-expression
start-index="158" stop-index="165" value="DALLAS" />
+ </items>
+ <items>
+ <literal-expression
start-index="168" stop-index="177" value="NEW YORK" />
+ </items>
+ <items>
+ <literal-expression
start-index="180" stop-index="187" value="BOSTON" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_with_read_only"
view-definition="SELECT cust_last_name, nls_language, credit_limit FROM
customers">
+ <view name="customer_ro" start-index="12" stop-index="22" />
+ <select>
+ <projections start-index="59" stop-index="100">
+ <column-projection name="cust_last_name" start-index="59"
stop-index="72" />
+ <column-projection name="nls_language" start-index="75"
stop-index="86" />
+ <column-projection name="credit_limit" start-index="89"
stop-index="100" />
+ </projections>
+ <from>
+ <simple-table name="customers" start-index="107"
stop-index="115" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_of_timestamp_minute"
view-definition="SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP -
INTERVAL '60' MINUTE)">
+ <view name="hour_ago" start-index="12" stop-index="19" />
+ <select>
+ <projections start-index="31" stop-index="31">
+ <shorthand-projection start-index="31" stop-index="31" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="38"
stop-index="46" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_sys_context"
view-definition="SELECT * FROM employees WHERE department_id =
SYS_CONTEXT('hr_context', 'department_id')">
+ <view name="hr_org_secure_view" start-index="12" stop-index="29" />
+ <select>
+ <projections start-index="41" stop-index="41">
+ <shorthand-projection start-index="41" stop-index="41" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="48"
stop-index="56" />
+ </from>
+ <where start-index="58" stop-index="121">
+ <expr>
+ <binary-operation-expression start-index="64"
stop-index="121">
+ <left>
+ <column name="department_id" start-index="64"
stop-index="76" />
+ </left>
+ <right>
+ <function function-name="SYS_CONTEXT"
text="SYS_CONTEXT('hr_context', 'department_id')" start-index="80"
stop-index="121">
+ <parameter>
+ <literal-expression value="hr_context"
start-index="92" stop-index="103" />
+ </parameter>
+ <parameter>
+ <literal-expression value="department_id"
start-index="106" stop-index="120" />
+ </parameter>
+ </function>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_of_with_object_identifier"
view-definition="SELECT e.employee_id, e.last_name, e.job_id FROM employees e
WHERE job_id = 'IT_PROG'">
+ <view name="it_prog_view" start-index="12" stop-index="23" />
+ <select>
+ <projections start-index="89" stop-index="124">
+ <column-projection name="employee_id" start-index="89"
stop-index="101">
+ <owner name="e" start-index="89" stop-index="89" />
+ </column-projection>
+ <column-projection name="last_name" start-index="104"
stop-index="114">
+ <owner name="e" start-index="104" stop-index="104" />
+ </column-projection>
+ <column-projection name="job_id" start-index="117"
stop-index="124">
+ <owner name="e" start-index="117" stop-index="117" />
+ </column-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" alias="e" start-index="131"
stop-index="141" />
+ </from>
+ <where start-index="143" stop-index="166">
+ <expr>
+ <binary-operation-expression start-index="149"
stop-index="166">
+ <left>
+ <column name="job_id" start-index="149"
stop-index="154" />
+ </left>
+ <right>
+ <literal-expression value="IT_PROG"
start-index="158" stop-index="166" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_define_column_with_select"
view-definition="SELECT employee_id, last_name, email FROM employees">
+ <view name="emp_sal" start-index="12" stop-index="18" />
+ <select>
+ <projections start-index="151" stop-index="179">
+ <column-projection name="employee_id" start-index="151"
stop-index="161" />
+ <column-projection name="last_name" start-index="164"
stop-index="172" />
+ <column-projection name="email" start-index="175"
stop-index="179" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="186"
stop-index="194" />
+ </from>
+ </select>
+ </create-view>
+
+ <create-view sql-case-id="create_view_select_join_using"
view-definition="SELECT employee_id, last_name, salary, location_id FROM
employees JOIN departments USING (department_id) WHERE department_id = 10">
+ <view name="employees_view" start-index="12" stop-index="25" />
+ <select>
+ <projections start-index="37" stop-index="79">
+ <column-projection name="employee_id" start-index="37"
stop-index="47" />
+ <column-projection name="last_name" start-index="50"
stop-index="58" />
+ <column-projection name="salary" start-index="61"
stop-index="66" />
+ <column-projection name="location_id" start-index="69"
stop-index="79" />
+ </projections>
+ <from>
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="employees" start-index="86"
stop-index="94" />
+ </left>
+ <right>
+ <simple-table name="departments" start-index="101"
stop-index="111" />
+ </right>
+ <using-columns name="department_id" start-index="120"
stop-index="132" />
+ </join-table>
+ </from>
+ <where start-index="135" stop-index="158">
+ <expr>
+ <binary-operation-expression start-index="141"
stop-index="158">
+ <left>
+ <column name="department_id" start-index="141"
stop-index="153" />
+ </left>
+ <right>
+ <literal-expression value="10" start-index="157"
stop-index="158" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </create-view>
</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 8a497f44e3c..fa588d4f47a 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -7174,4 +7174,31 @@
<column-item name="NAME" order-direction="ASC" start-index="148"
stop-index="151" />
</group-by>
</select>
+
+ <select sql-case-id="select_with_user_updatable_columns">
+ <projections start-index="7" stop-index="28">
+ <column-projection name="column_name" start-index="7"
stop-index="17" />
+ <column-projection name="updatable" start-index="20"
stop-index="28" />
+ </projections>
+ <from>
+ <simple-table name="user_updatable_columns" start-index="35"
stop-index="56" />
+ </from>
+ <where start-index="58" stop-index="92">
+ <expr>
+ <binary-operation-expression text="table_name =
'LOCATIONS_VIEW'" start-index="64" stop-index="92">
+ <left>
+ <column name="table_name" start-index="64"
stop-index="73" />
+ </left>
+ <right>
+ <literal-expression start-index="77" stop-index="92"
value="LOCATIONS_VIEW" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="column_name" order-direction="ASC"
start-index="103" stop-index="113" />
+ <column-item name="updatable" order-direction="ASC"
start-index="116" stop-index="124" />
+ </order-by>
+ </select>
</sql-parser-test-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 c9c08280ff9..b48261a6313 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
@@ -27,12 +27,33 @@
<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_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_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_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" />
+ <sql-case id="create_view_as_select_with_condition4" 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'"
db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition5" value="CREATE VIEW
emp_view AS SELECT last_name, salary*12 annual_salary FROM employees WHERE
department_id = 20" db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition6" value="CREATE VIEW
emp_v_2 AS SELECT empno, ename, deptno FROM emp WHERE sal > 1000"
db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition7" value="CREATE VIEW
emp_dept_oj1 AS SELECT e.*, Loc, d.dname FROM emp_v e, dept d WHERE e.deptno =
d.deptno (+)" db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition8" value="CREATE VIEW
employees_50_vw AS SELECT employee_id, last_name, job_id, salary,
commission_pct, department_id FROM employees WHERE department_id = 50"
db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition9" value="CREATE VIEW
locations_view AS SELECT d.department_id, d.department_name, l.location_id,
l.city FROM departments d, locations l WHERE d.location_id = l.location_id"
db-types="Oracle" />
+ <sql-case id="create_view_as_select_with_condition10" value="CREATE VIEW
emp_dept_oj2 AS SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e,
dept d WHERE e.deptno (+) = d.deptno" 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_with_check_option1" 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_with_check_option2" value="CREATE VIEW
emp_mgr AS SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE e1.mgr =
e2.empno 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-case id="create_view_select_in1" value="CREATE VIEW division1_staff
AS SELECT ename, empno, job, dname FROM emp, dept WHERE emp.deptno IN (10, 30)
AND emp.deptno = dept.deptno" db-types="Oracle" />
+ <sql-case id="create_view_select_in2" value="CREATE VIEW emp_dept AS
SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc FROM
emp, dept WHERE emp.deptno = dept.deptno AND dept.loc IN ('DALLAS', 'NEW YORK',
'BOSTON')" db-types="Oracle" />
+ <sql-case id="create_view_select_with_read_only" value="CREATE VIEW
customer_ro (name, language, credit) AS SELECT cust_last_name, nls_language,
credit_limit FROM customers WITH READ ONLY" db-types="Oracle" />
+ <sql-case id="create_view_select_of_timestamp_minute" value="CREATE VIEW
hour_ago AS SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL
'60' MINUTE)" db-types="Oracle" />
+ <sql-case id="create_view_select_sys_context" value="CREATE VIEW
hr_org_secure_view AS SELECT * FROM employees WHERE department_id =
SYS_CONTEXT('hr_context', 'department_id')" db-types="Oracle" />
+ <sql-case id="create_view_of_with_object_identifier" value="CREATE VIEW
it_prog_view OF employee_type WITH OBJECT IDENTIFIER (employee_id) AS SELECT
e.employee_id, e.last_name, e.job_id FROM employees e WHERE job_id = 'IT_PROG'"
db-types="Oracle" />
+ <sql-case id="create_view_define_column_with_select" value="CREATE VIEW
emp_sal (emp_id, last_name, email UNIQUE RELY DISABLE NOVALIDATE, CONSTRAINT
id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE) AS SELECT employee_id,
last_name, email FROM employees" db-types="Oracle" />
+ <sql-case id="create_view_select_join_using" value="CREATE VIEW
employees_view AS SELECT employee_id, last_name, salary, location_id FROM
employees JOIN departments USING (department_id) WHERE department_id = 10"
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 00db43c5587..cff56d26a3f 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
@@ -217,4 +217,5 @@
<sql-case id="select_wm_concat_function2" value="SELECT WM_CONCAT(NAME)
NAME FROM TEST_TABLE" db-types="Oracle" />
<sql-case id="select_wm_concat_function3" value="SELECT
REPLACE(WM_CONCAT(NAME),',','|') FROM TEST_TABLE" db-types="Oracle" />
<sql-case id="select_wm_concat_function4" value="SELECT
NAME,WM_CONCAT(DECODE(SUBSTR((TO_CHAR(NAME)),0,1),'.','0'||TO_CHAR(NAME),TO_CHAR(NAME)))
WM_NAME FROM TEST_TABLE WHERE NAME ='TEST' GROUP BY NAME" db-types="Oracle" />
+ <sql-case id="select_with_user_updatable_columns" value="SELECT
column_name, updatable FROM user_updatable_columns WHERE table_name =
'LOCATIONS_VIEW' ORDER BY column_name, updatable" db-types="Oracle" />
</sql-cases>
