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 1441e5fc418 Fix Oracle CONNECT BY, PRIOR was not properly
parsed(#26858) (#27091)
1441e5fc418 is described below
commit 1441e5fc418ec449e24e135d803db8d15568a5e0
Author: ZhangCheng <[email protected]>
AuthorDate: Fri Jul 14 12:19:34 2023 +0800
Fix Oracle CONNECT BY, PRIOR was not properly parsed(#26858) (#27091)
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 4 +-
.../parser/src/main/resources/case/dml/insert.xml | 53 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
3 files changed, 57 insertions(+), 1 deletion(-)
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 c6576e7047f..18b51dc18a6 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
@@ -591,7 +591,6 @@ expr
: expr andOperator expr
| expr orOperator expr
| notOperator expr
- | PRIOR expr
| LP_ expr RP_
| booleanPrimary
| aggregationFunction
@@ -613,6 +612,7 @@ notOperator
booleanPrimary
: booleanPrimary IS NOT? (TRUE | FALSE | UNKNOWN | NULL)
+ | PRIOR predicate
| booleanPrimary SAFE_EQ_ predicate
| booleanPrimary comparisonOperator predicate
| booleanPrimary comparisonOperator (ALL | ANY) subquery
@@ -625,6 +625,7 @@ comparisonOperator
predicate
: bitExpr NOT? IN subquery
+ | PRIOR predicate
| bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_
| bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_ AND predicate
| bitExpr NOT? BETWEEN bitExpr AND predicate
@@ -658,6 +659,7 @@ simpleExpr
| caseExpression
| columnName
| privateExprOfDb
+ | PRIOR identifier
;
functionCall
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 bdf6fdd2fbf..62c2fa51835 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -2287,4 +2287,57 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_with_connect_by_and_prior">
+ <table name="t" start-index="12" stop-index="12"
literal-start-index="12" literal-stop-index="12"/>
+ <columns start-index="14" stop-index="29" literal-start-index="14"
literal-stop-index="29">
+ <column name="c1" start-index="15" stop-index="16"
literal-start-index="15" literal-stop-index="16"/>
+ <column name="c2" start-index="18" stop-index="19"
literal-start-index="18" literal-stop-index="19"/>
+ <column name="c3" start-index="21" stop-index="22"
literal-start-index="21" literal-stop-index="22"/>
+ <column name="c4" start-index="24" stop-index="25"
literal-start-index="24" literal-stop-index="25"/>
+ <column name="c5" start-index="27" stop-index="28"
literal-start-index="27" literal-stop-index="28"/>
+ </columns>
+ <select-subquery>
+ <from>
+ <simple-table name="t" start-index="91" stop-index="91"
literal-start-index="91" literal-stop-index="91"/>
+ </from>
+ <projections distinct-row="false" start-index="38" stop-index="84"
literal-start-index="38" literal-stop-index="84">
+ <column-projection name="c1" start-index="38" stop-index="39"
literal-start-index="38" literal-stop-index="39"/>
+ <column-projection name="c2" start-index="41" stop-index="42"
literal-start-index="41" literal-stop-index="42"/>
+ <column-projection name="c4" start-index="80" stop-index="81"
literal-start-index="80" literal-stop-index="81"/>
+ <column-projection name="c5" start-index="83" stop-index="84"
literal-start-index="83" literal-stop-index="84"/>
+ <expression-projection text="regexp_substr(c3, '[^,]+', 1, l)"
literal-text="regexp_substr(c3, '[^,]+', 1, l)" alias="c3" start-index="44"
stop-index="78" literal-start-index="44" literal-stop-index="78">
+ <expr start-index="0">
+ <function function-name="regexp_substr"
text="regexp_substr(c3, '[^,]+', 1, l)" literal-text="regexp_substr(c3,
'[^,]+', 1, l)" start-index="44" stop-index="75" literal-start-index="44"
literal-stop-index="75">
+ <parameter>
+ <column name="c3" start-index="58"
stop-index="59" literal-start-index="58" literal-stop-index="59"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="[^,]+"
start-index="62" stop-index="68" literal-start-index="62"
literal-stop-index="68"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="71"
stop-index="71" literal-start-index="71" literal-stop-index="71"/>
+ </parameter>
+ <parameter>
+ <column name="l" start-index="74"
stop-index="74" literal-start-index="74" literal-stop-index="74"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <where start-index="93" stop-index="102" literal-start-index="93"
literal-stop-index="102">
+ <expr >
+ <binary-operation-expression start-index="99"
stop-index="102" literal-start-index="99" literal-stop-index="102">
+ <left>
+ <column name="id" start-index="99"
stop-index="100" literal-start-index="99" literal-stop-index="100"/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="102"
stop-index="102" literal-start-index="102" literal-stop-index="102"/>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select-subquery>
+ </insert>
</sql-parser-test-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 5a5ad3e4e13..36eebb71a37 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
@@ -92,4 +92,5 @@
<sql-case id="insert_with_negative_value" value="insert into t_order
(order_id, user_id, status) values (?, ?, ?)" />
<sql-case id="insert_datetime_literals" value="INSERT INTO date_tab VALUES
( TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00',
TIMESTAMP'1999-12-01 10:00:00');" db-types="Oracle" />
<sql-case id="insert_with_content_keyword" value="INSERT INTO SYS_MQ_MSG
(ID, CONTENT) VALUES (1, 'test');" db-types="Oracle" />
+ <sql-case id="insert_with_connect_by_and_prior" value="Insert Into t
(c1,c2,c3,c4,c5) select c1,c2,regexp_substr(c3, '[^,]+', 1, l) c3,c4,c5 from t
where id=1 connect by l <= regexp_count(c3, ',') + 1 and ID = prior ID and
prior dbms_random.value is not null;" db-types="Oracle" />
</sql-cases>
