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 f13046ccbbc Support parsing oracle explain plan sql(27171) (#29728)
f13046ccbbc is described below
commit f13046ccbbc82c75a14953fd8c4aab26001b1ee3
Author: kanha gupta <[email protected]>
AuthorDate: Fri Jan 26 11:26:40 2024 +0530
Support parsing oracle explain plan sql(27171) (#29728)
* Support parsing Oracle EXPLAIN PLAN sql(27171)
* Support parsing Oracle EXPLAIN PLAN sql(27171)
* Support parsing Oracle EXPLAIN PLAN sql(27171)
* Support parsing Oracle EXPLAIN PLAN sql(27171)
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 5 +
.../visitor/statement/OracleStatementVisitor.java | 3 +
.../parser/src/main/resources/case/dal/explain.xml | 219 +++++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 13 ++
.../main/resources/sql/supported/dal/explain.xml | 5 +
.../main/resources/sql/supported/dml/select.xml | 1 +
6 files changed, 246 insertions(+)
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 c056d55ad8c..b746b5ad67f 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
@@ -32,12 +32,17 @@ literals
| booleanLiterals
| nullValueLiterals
| intervalLiterals
+ | bindLiterals
;
intervalLiterals
: INTERVAL stringLiterals intervalUnit (intervalPrecision)? (TO
intervalUnit (intervalPrecision)?)?
;
+bindLiterals
+ : COLON_ identifier
+ ;
+
intervalPrecision
: LP_ INTEGER_ RP_
;
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index cd9f8cc8112..a75922e31e8 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -245,6 +245,9 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
if (null != ctx.intervalLiterals()) {
return visit(ctx.intervalLiterals());
}
+ if (null != ctx.bindLiterals()) {
+ return visit(ctx.bindLiterals());
+ }
throw new IllegalStateException("Literals must have string, number,
dateTime, hex, bit, interval, boolean or null.");
}
diff --git a/test/it/parser/src/main/resources/case/dal/explain.xml
b/test/it/parser/src/main/resources/case/dal/explain.xml
index a07aa935c45..684a6a33429 100644
--- a/test/it/parser/src/main/resources/case/dal/explain.xml
+++ b/test/it/parser/src/main/resources/case/dal/explain.xml
@@ -1381,4 +1381,223 @@
</where>
</select>
</describe>
+
+ <describe sql-case-id="explain_for_select_where_between">
+ <select>
+ <projections start-index="24" stop-index="53">
+ <aggregation-projection type="SUM" alias="total_revenue"
expression="SUM(amount_sold)" start-index="24" stop-index="39"/>
+ </projections>
+ <from>
+ <simple-table name="sales" start-index="60" stop-index="64"/>
+ </from>
+ <where start-index="66" stop-index="114">
+ <expr>
+ <between-expression start-index="72" stop-index="114">
+ <left>
+ <column name="time_id" start-index="72"
stop-index="78"/>
+ </left>
+ <between-expr>
+ <literal-expression value="01-JAN-00"
start-index="88" stop-index="98"/>
+ </between-expr>
+ <and-expr>
+ <literal-expression value="31-DEC-00"
start-index="104" stop-index="114"/>
+ </and-expr>
+ </between-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_where_between_groupby_having">
+ <select>
+ <projections start-index="24" stop-index="49">
+ <column-projection name="cust_last_name" start-index="24"
stop-index="39">
+ <owner name="c" start-index="24" stop-index="24"/>
+ </column-projection>
+ <aggregation-projection type="COUNT" expression="COUNT(*)"
start-index="42" stop-index="49"/>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="sales" alias="s" start-index="56"
stop-index="62"/>
+ </left>
+ <right>
+ <simple-table name="customers" alias="c"
start-index="65" stop-index="75"/>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="77" stop-index="205">
+ <expr>
+ <binary-operation-expression start-index="83"
stop-index="205">
+ <left>
+ <binary-operation-expression start-index="83"
stop-index="103">
+ <left>
+ <column name="cust_id" start-index="83"
stop-index="91">
+ <owner name="s" start-index="83"
stop-index="83"/>
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="cust_id" start-index="95"
stop-index="103">
+ <owner name="c" start-index="95"
stop-index="95"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <between-expression start-index="109"
stop-index="205">
+ <left>
+ <column name="time_id" start-index="109"
stop-index="117">
+ <owner name="s" start-index="109"
stop-index="109"/>
+ </column>
+ </left>
+ <between-expr>
+ <function function-name="TO_DATE"
text="TO_DATE('01-JUL-1999', 'DD-MON-YYYY')" start-index="127" stop-index="163">
+ <parameter>
+ <literal-expression
value="'01-JUL-1999'" start-index="135" stop-index="147"/>
+ </parameter>
+ <parameter>
+ <literal-expression
value="'DD-MON-YYYY'" start-index="150" stop-index="162"/>
+ </parameter>
+ </function>
+ </between-expr>
+ <and-expr>
+ <function function-name="TO_DATE"
text="TO_DATE('01-OCT-1999', 'DD-MON-YYYY')" start-index="169" stop-index="205">
+ <parameter>
+ <literal-expression
value="'01-OCT-1999'" start-index="177" stop-index="189"/>
+ </parameter>
+ <parameter>
+ <literal-expression
value="'DD-MON-YYYY'" start-index="192" stop-index="204"/>
+ </parameter>
+ </function>
+ </and-expr>
+ </between-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <group-by>
+ <column-item name="cust_last_name" start-index="216"
stop-index="231">
+ <owner name="c" start-index="216" stop-index="216"/>
+ </column-item>
+ </group-by>
+ <having start-index="233" stop-index="253">
+ <expr>
+ <binary-operation-expression start-index="240"
stop-index="253">
+ <left>
+ <aggregation-projection type="COUNT"
expression="COUNT(*)" start-index="240" stop-index="247"/>
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="100" start-index="251"
stop-index="253"/>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_alias_where_colon">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24"/>
+ </projections>
+ <from>
+ <simple-table name="sales" alias="s" start-index="31"
stop-index="37"/>
+ </from>
+ <where start-index="39" stop-index="72">
+ <expr>
+ <in-expression start-index="45" stop-index="72">
+ <left>
+ <column name="time_id" start-index="45"
stop-index="51"/>
+ </left>
+ <right>
+ <list-expression start-index="56" stop-index="72">
+ <items>
+ <common-expression start-index="58"
stop-index="59" text=":a"/>
+ </items>
+ <items>
+ <common-expression start-index="62"
stop-index="63" text=":b"/>
+ </items>
+ <items>
+ <common-expression start-index="66"
stop-index="67" text=":c"/>
+ </items>
+ <items>
+ <common-expression start-index="70"
stop-index="71" text=":d"/>
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_where_todate_function">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24"/>
+ </projections>
+ <from>
+ <simple-table name="sales" start-index="31" stop-index="35"/>
+ </from>
+ <where start-index="37" stop-index="89">
+ <expr>
+ <binary-operation-expression start-index="43"
stop-index="89">
+ <left>
+ <column name="time_id" start-index="43"
stop-index="49"/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <function function-name="to_date"
text="to_date('01-jan-2001', 'dd-mon-yyyy')" start-index="53" stop-index="89">
+ <parameter>
+ <literal-expression value="'01-jan-2001'"
start-index="61" stop-index="73"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="'dd-mon-yyyy'"
start-index="76" stop-index="88"/>
+ </parameter>
+ </function>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_where_colon">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24"/>
+ </projections>
+ <from>
+ <simple-table name="sales" start-index="31" stop-index="35"/>
+ </from>
+ <where start-index="37" stop-index="69">
+ <expr>
+ <in-expression start-index="43" stop-index="69">
+ <left>
+ <column name="time_id" start-index="43"
stop-index="49"/>
+ </left>
+ <right>
+ <list-expression start-index="54" stop-index="69">
+ <items>
+ <common-expression start-index="55"
stop-index="56" text=":a"/>
+ </items>
+ <items>
+ <common-expression start-index="59"
stop-index="60" text=":b"/>
+ </items>
+ <items>
+ <common-expression start-index="63"
stop-index="64" text=":c"/>
+ </items>
+ <items>
+ <common-expression start-index="67"
stop-index="68" text=":d"/>
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
</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 423914b6bf7..4ce1ab9be81 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -8187,4 +8187,17 @@
</simple-table>
</from>
</select>
+
+ <select sql-case-id="select_from_dbms_table">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <from>
+ <collection-table>
+ <column name="display" start-index="20" stop-index="37">
+ <owner name="dbms_xplan" start-index="20" stop-index="29"/>
+ </column>
+ </collection-table>
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dal/explain.xml
b/test/it/parser/src/main/resources/sql/supported/dal/explain.xml
index e906310eddf..374d6307f8f 100644
--- a/test/it/parser/src/main/resources/sql/supported/dal/explain.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dal/explain.xml
@@ -86,4 +86,9 @@
<sql-case id="explain_for_select_emp_range_where" value="EXPLAIN PLAN FOR
SELECT * FROM emp_range WHERE hire_date <
TO_DATE('1-JAN-1992','DD-MON-YYYY');" db-types="Oracle" />
<sql-case id="explain_for_select_where_and_function" value="EXPLAIN PLAN
FOR SELECT * FROM emp_range WHERE hire_date >=
TO_DATE('1-JAN-1996','DD-MON-YYYY');" db-types="Oracle" />
<sql-case id="explain_for_select_xml_query" value="EXPLAIN PLAN FOR SELECT
XMLQuery('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE RETURNING
CONTENT) FROM po_clob WHERE XMLExists('/PurchaseOrder/LineItems/LineItem
[ora:contains(Description, "Picnic") > 0]' PASSING OBJECT_VALUE)
AND XMLExists('/PurchaseOrder[User="SBELL"]' PASSING OBJECT_VALUE);"
db-types="Oracle" />
+ <sql-case id="explain_for_select_where_between" value="EXPLAIN PLAN FOR
SELECT SUM(amount_sold) total_revenue FROM sales WHERE time_id BETWEEN
'01-JAN-00' AND '31-DEC-00';" db-types="Oracle"/>
+ <sql-case id="explain_for_select_where_between_groupby_having"
value="EXPLAIN PLAN FOR SELECT c.cust_last_name, COUNT(*) FROM sales s,
customers c WHERE s.cust_id = c.cust_id AND s.time_id BETWEEN
TO_DATE('01-JUL-1999', 'DD-MON-YYYY') AND TO_DATE('01-OCT-1999', 'DD-MON-YYYY')
GROUP BY c.cust_last_name HAVING COUNT(*) > 100;" db-types="Oracle"/>
+ <sql-case id="explain_for_select_alias_where_colon" value="explain plan
for select * from sales s where time_id in ( :a, :b, :c, :d);"
db-types="Oracle"/>
+ <sql-case id="explain_for_select_where_todate_function" value="explain
plan for select * from sales where time_id = to_date('01-jan-2001',
'dd-mon-yyyy');" db-types="Oracle"/>
+ <sql-case id="explain_for_select_where_colon" value="explain plan for
select * from sales where time_id in (:a, :b, :c, :d);" 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 352eecf1182..0336c183f4a 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
@@ -249,4 +249,5 @@
<sql-case id="select_with_database_files" value="SELECT name, size / 128.0
- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files" db-types="SQLServer"/>
<sql-case id="select_with_contains_function" value="SELECT product_id FROM
products WHERE CONTAINS(product_description, '"Snap Happy 100EZ" OR
FORMSOF(THESAURUS,"Snap Happy") OR "100EZ"') AND
product_cost < 200" db-types="SQLServer"/>
<sql-case id="select_with_default_schema" value="SELECT
has_backup_checksums, database_name, * FROM msdb..backupset"
db-types="SQLServer"/>
+ <sql-case id="select_from_dbms_table" value="select * from
table(dbms_xplan.display);" db-types="Oracle"/>
</sql-cases>
