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 b0d69d08375 Support parsing Oracle EXPLAIN PLAN sql (#29663)
b0d69d08375 is described below
commit b0d69d083756aad40a8c0d74fcb089589117b413
Author: kanha gupta <[email protected]>
AuthorDate: Sat Jan 6 05:20:01 2024 +0530
Support parsing Oracle EXPLAIN PLAN sql (#29663)
---
.../parser/src/main/resources/case/dal/explain.xml | 147 +++++++++++++++++++++
.../main/resources/sql/supported/dal/explain.xml | 4 +
2 files changed, 151 insertions(+)
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 83fc7fb0060..d4ea9be6420 100644
--- a/test/it/parser/src/main/resources/case/dal/explain.xml
+++ b/test/it/parser/src/main/resources/case/dal/explain.xml
@@ -1067,4 +1067,151 @@
<comment start-index="0" stop-index="19" text="/*FORCE_IMCI_NODES*/" />
<comment start-index="36" stop-index="76" text="/*+
SET_VAR(cost_threshold_for_imci=0) */" />
</describe>
+
+ <describe sql-case-id="explain_for_select_with_group_by">
+ <select>
+ <projections start-index="24" stop-index="64">
+ <column-projection name="calendar_month_desc" start-index="24"
stop-index="44" >
+ <owner name="t" start-index="24" stop-index="24" />
+ </column-projection>
+ <aggregation-projection type="SUM"
expression="SUM(s.amount_sold)" start-index="47" stop-index="64" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="sales" alias="s" start-index="72"
stop-index="78" />
+ </left>
+ <right>
+ <simple-table name="times" alias="t" start-index="81"
stop-index="87" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="89" stop-index="115">
+ <expr>
+ <binary-operation-expression start-index="95"
stop-index="115">
+ <left>
+ <column name="time_id" start-index="95"
stop-index="103" >
+ <owner name="s" start-index="95"
stop-index="95" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="time_id" start-index="107"
stop-index="115" >
+ <owner name="t" start-index="107"
stop-index="107" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <group-by>
+ <column-item name="calendar_month_desc" start-index="126"
stop-index="146" >
+ <owner name="t" start-index="126" stop-index="126" />
+ </column-item>
+ </group-by>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_with_function">
+ <select>
+ <projections start-index="24" stop-index="39">
+ <aggregation-projection type="SUM"
expression="SUM(amount_sold)" start-index="24" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="sales" start-index="46" stop-index="50" />
+ </from>
+ <where start-index="52" stop-index="89">
+ <expr>
+ <binary-operation-expression start-index="58"
stop-index="89">
+ <left>
+ <function function-name="TO_CHAR"
text="TO_CHAR(time_id,'yyyy')" start-index="58" stop-index="80">
+ <parameter>
+ <column name="time_id" start-index="66"
stop-index="72" />
+ </parameter>
+ <parameter>
+ <literal-expression value="yyyy"
start-index="74" stop-index="79" />
+ </parameter>
+ </function>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="2000" start-index="84"
stop-index="89"/>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_with_function_and_function">
+ <select>
+ <projections start-index="24" stop-index="41">
+ <aggregation-projection type="SUM"
expression="SUM(quantity_sold)" start-index="24" stop-index="41" />
+ </projections>
+ <from>
+ <simple-table name="sales" start-index="48" stop-index="52" />
+ </from>
+ <where start-index="54" stop-index="110" >
+ <expr>
+ <binary-operation-expression start-index="60"
stop-index="110">
+ <left>
+ <column name="time_id" start-index="60"
stop-index="66" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <function function-name="TO_TIMESTAMP"
text="TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')" start-index="70"
stop-index="110">
+ <parameter>
+ <literal-expression value="1-jan-2000"
start-index="83" stop-index="94" />
+ </parameter>
+ <parameter>
+ <literal-expression value="dd-mon-yyyy"
start-index="97" stop-index="109" />
+ </parameter>
+ </function>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24" />
+ </projections>
+ <from>
+ <simple-table name="customers" start-index="31"
stop-index="42">
+ <owner name="sh" start-index="31" stop-index="32" />
+ </simple-table>
+ </from>
+ <where start-index="44" stop-index="101" >
+ <expr>
+ <binary-operation-expression start-index="50"
stop-index="101">
+ <left>
+ <binary-operation-expression start-index="50"
stop-index="72">
+ <left>
+ <column name="cust_city" start-index="50"
stop-index="58"/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="Los Angeles"
start-index="60" stop-index="72" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <binary-operation-expression start-index="78"
stop-index="101">
+ <left>
+ <column name="cust_state_province"
start-index="78" stop-index="96" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="CA"
start-index="98" stop-index="101"/>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
</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 7afaf18e4ff..e498bfe7cad 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
@@ -74,4 +74,8 @@
<sql-case id="explain_for_select_with_unique_partition_by" value="EXPLAIN
PLAN FOR SELECT country, prod, year, sales FROM sales_view WHERE country IN
('Italy', 'Japan') MODEL UNIQUE DIMENSION PARTITION BY (country) DIMENSION BY
(prod, year)
MEASURES (sale sales) RULES UPSERT (sales['Bounce', 2003] =
AVG(sales)[ANY, 2002] * 1.24, sales[prod <> 'Bounce', 2003] =
sales['Bounce', 2003] * 0.25)" db-types="Oracle" />
<sql-case id="explain_with_select_comment" value="/*FORCE_IMCI_NODES*/
explain select /*+ SET_VAR(cost_threshold_for_imci=0) */ * from t_order"
db-types="MySQL" />
+ <sql-case id="explain_for_select_with_group_by" value="EXPLAIN PLAN FOR
SELECT t.calendar_month_desc, SUM(s.amount_sold) FROM sales s, times t WHERE
s.time_id = t.time_id GROUP BY t.calendar_month_desc" db-types="Oracle" />
+ <sql-case id="explain_for_select_with_function" value="EXPLAIN PLAN FOR
SELECT SUM(amount_sold) FROM sales WHERE TO_CHAR(time_id,'yyyy') = '2000'"
db-types="Oracle" />
+ <sql-case id="explain_for_select_with_function_and_function"
value="EXPLAIN PLAN FOR SELECT SUM(quantity_sold) FROM sales WHERE time_id =
TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')" db-types="Oracle" />
+ <sql-case id="explain_for_select" value="EXPLAIN PLAN FOR SELECT * FROM
sh.customers WHERE cust_city='Los Angeles' AND cust_state_province='CA'"
db-types="Oracle" />
</sql-cases>
