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 bac3c499612 Support parsing Oracle explain plan sql (#30220)
bac3c499612 is described below
commit bac3c499612b86732abe37610b8359ca96ad06ce
Author: LotusMoon <[email protected]>
AuthorDate: Wed Feb 21 14:35:01 2024 +0800
Support parsing Oracle explain plan sql (#30220)
---
.../parser/src/main/resources/case/dal/explain.xml | 215 +++++++++++++++++++++
.../main/resources/sql/supported/dal/explain.xml | 3 +
2 files changed, 218 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 684a6a33429..f41ab8f722c 100644
--- a/test/it/parser/src/main/resources/case/dal/explain.xml
+++ b/test/it/parser/src/main/resources/case/dal/explain.xml
@@ -1600,4 +1600,219 @@
</where>
</select>
</describe>
+
+ <describe sql-case-id="explain_for_select_group_by_multi_columns">
+ <select>
+ <projections start-index="24" stop-index="65">
+ <column-projection name="prod_name" start-index="24"
stop-index="34">
+ <owner name="p" start-index="24" stop-index="24"/>
+ </column-projection>
+ <column-projection name="time_id" start-index="37"
stop-index="45">
+ <owner name="t" start-index="37" stop-index="37"/>
+ </column-projection>
+ <aggregation-projection type="SUM"
expression="sum(s.amount_sold)" start-index="48" stop-index="65"/>
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="sales" start-index="72"
stop-index="78" alias="s"/>
+ </left>
+ <right>
+ <simple-table name="times" start-index="81"
stop-index="87" alias="t"/>
+ </right>
+ </join-table>
+ </left>
+ <right>
+ <simple-table name="products" start-index="90"
stop-index="99" alias="p"/>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="101" stop-index="240">
+ <expr>
+ <binary-operation-expression start-index="107"
stop-index="240">
+ <operator>and</operator>
+ <left>
+ <binary-operation-expression start-index="107"
stop-index="207">
+ <operator>and</operator>
+ <left>
+ <binary-operation-expression
start-index="107" stop-index="178">
+ <operator>and</operator>
+ <left>
+ <binary-operation-expression
start-index="107" stop-index="153">
+ <operator>and</operator>
+ <left>
+
<binary-operation-expression start-index="107" stop-index="127">
+ <operator>=</operator>
+ <left>
+ <column
name="time_id" start-index="107" stop-index="115">
+ <owner
name="s" start-index="107" stop-index="107"/>
+ </column>
+ </left>
+ <right>
+ <column
name="time_id" start-index="119" stop-index="127">
+ <owner
name="t" start-index="119" stop-index="119"/>
+ </column>
+ </right>
+
</binary-operation-expression>
+ </left>
+ <right>
+
<binary-operation-expression start-index="133" stop-index="153">
+ <operator>=</operator>
+ <left>
+ <column
name="prod_id" start-index="133" stop-index="141">
+ <owner
name="s" start-index="133" stop-index="133"/>
+ </column>
+ </left>
+ <right>
+ <column
name="prod_id" start-index="145" stop-index="153">
+ <owner
name="p" start-index="145" stop-index="145"/>
+ </column>
+ </right>
+
</binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression
start-index="159" stop-index="178">
+ <operator>=</operator>
+ <left>
+ <column name="fiscal_year"
start-index="159" stop-index="171">
+ <owner name="t"
start-index="159" stop-index="159"/>
+ </column>
+ </left>
+ <right>
+ <literal-expression
value="2000" start-index="175" stop-index="178"/>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression
start-index="184" stop-index="207">
+ <operator>=</operator>
+ <left>
+ <column name="fiscal_week_number"
start-index="184" stop-index="203">
+ <owner name="t"
start-index="184" stop-index="184"/>
+ </column>
+ </left>
+ <right>
+ <literal-expression value="3"
start-index="207" stop-index="207"/>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="213"
stop-index="240">
+ <operator>=</operator>
+ <left>
+ <column name="prod_category"
start-index="213" stop-index="227">
+ <owner name="p" start-index="213"
stop-index="213"/>
+ </column>
+ </left>
+ <right>
+ <literal-expression value="Hardware"
start-index="231" stop-index="240"/>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <group-by>
+ <column-item name="time_id" start-index="251" stop-index="259">
+ <owner name="t" start-index="251" stop-index="251"/>
+ </column-item>
+ <column-item name="prod_name" start-index="262"
stop-index="272">
+ <owner name="p" start-index="262" stop-index="262"/>
+ </column-item>
+ </group-by>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_in_sub_query">
+ <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="120">
+ <expr>
+ <in-expression start-index="58" stop-index="120">
+ <left>
+ <column name="time_id" start-index="58"
stop-index="64"/>
+ </left>
+ <right>
+ <subquery start-index="69" stop-index="120">
+ <select>
+ <projections start-index="77"
stop-index="83">
+ <column-projection name="time_id"
start-index="77" stop-index="83"/>
+ </projections>
+ <from>
+ <simple-table name="times"
start-index="90" stop-index="94"/>
+ </from>
+ <where start-index="96" stop-index="119">
+ <expr>
+ <binary-operation-expression
start-index="102" stop-index="119">
+ <left>
+ <column name="fiscal_year"
start-index="102" stop-index="112"/>
+ </left>
+ <right>
+ <literal-expression
value="2000" start-index="116" stop-index="119"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_between_to_date_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="150">
+ <expr>
+ <between-expression start-index="58" stop-index="150">
+ <left>
+ <column name="time_id" start-index="58"
stop-index="64"/>
+ </left>
+ <and-expr>
+ <function
text="to_date('31-DEC-2000','dd-MON-yyyy')" function-name="to_date"
start-index="115" stop-index="150">
+ <parameter>
+ <literal-expression value="'31-DEC-2000'"
start-index="123" stop-index="135"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="'dd-MON-yyyy'"
start-index="137" stop-index="149"/>
+ </parameter>
+ </function>
+ </and-expr>
+ <between-expr>
+ <function
text="to_date('01-JAN-2000','dd-MON-yyyy')" function-name="to_date"
start-index="74" stop-index="109">
+ <parameter>
+ <literal-expression value="'01-JAN-2000'"
start-index="82" stop-index="94"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="'dd-MON-yyyy'"
start-index="96" stop-index="108"/>
+ </parameter>
+ </function>
+ </between-expr>
+ </between-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 374d6307f8f..e0858cfeb33 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
@@ -91,4 +91,7 @@
<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-case id="explain_for_select_group_by_multi_columns" value="explain
plan for select p.prod_name, t.time_id, sum(s.amount_sold) from sales s, times
t, products p where s.time_id = t.time_id and s.prod_id = p.prod_id and
t.fiscal_year = 2000 and t.fiscal_week_number = 3 and p.prod_category =
'Hardware' group by t.time_id, p.prod_name;" db-types="Oracle"/>
+ <sql-case id="explain_for_select_in_sub_query" value="explain plan for
select sum(amount_sold) from sales where time_id in (select time_id from times
where fiscal_year = 2000);" db-types="Oracle"/>
+ <sql-case id="explain_for_select_between_to_date_function" value="explain
plan for select sum(amount_sold) from sales where time_id between
to_date('01-JAN-2000','dd-MON-yyyy') and to_date('31-DEC-2000','dd-MON-yyyy')
;" db-types="Oracle"/>
</sql-cases>
