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 14aa8a474d9 Add oracle explain parse test (#29085)
14aa8a474d9 is described below
commit 14aa8a474d9a46404466414ddc82e36c42739caf
Author: niu niu <[email protected]>
AuthorDate: Mon Nov 20 08:57:42 2023 +0800
Add oracle explain parse test (#29085)
* Format explain case xml
* Add oracle explain parse test
---
.../parser/src/main/resources/case/dal/explain.xml | 226 ++++++++++++++++++++-
.../main/resources/sql/supported/dal/explain.xml | 6 +
2 files changed, 225 insertions(+), 7 deletions(-)
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 656619f37b7..d8c4eee4b63 100644
--- a/test/it/parser/src/main/resources/case/dal/explain.xml
+++ b/test/it/parser/src/main/resources/case/dal/explain.xml
@@ -24,7 +24,7 @@
</projections>
</select>
</describe>
-
+
<describe sql-case-id="explain_update_without_condition">
<update>
<table start-index="15" stop-index="21">
@@ -40,7 +40,7 @@
</set>
</update>
</describe>
-
+
<describe sql-case-id="explain_insert_without_parameters">
<insert>
<table name="t_order" start-index="20" stop-index="26" />
@@ -67,7 +67,7 @@
</values>
</insert>
</describe>
-
+
<describe sql-case-id="explain_delete_without_sharding_value">
<delete>
<table name="t_order" start-index="20" stop-index="26" />
@@ -87,7 +87,7 @@
</where>
</delete>
</describe>
-
+
<describe sql-case-id="explain_select_with_binding_tables">
<select>
<from>
@@ -123,7 +123,7 @@
</where>
</select>
</describe>
-
+
<describe sql-case-id="explain_create_table_as_select">
<create-table>
<table name="t_order_new" start-index="21" stop-index="31" />
@@ -137,7 +137,7 @@
</select>
</create-table>
</describe>
-
+
<describe
sql-case-id="explain_create_table_as_select_with_explicit_column_names">
<create-table>
<table name="t_order_new" start-index="21" stop-index="31" />
@@ -154,7 +154,7 @@
</select>
</create-table>
</describe>
-
+
<describe sql-case-id="explain_create_remote_table_as_select">
<create-table>
<table name="t_order_new" start-index="28" stop-index="38" />
@@ -192,6 +192,7 @@
</select>
</create-table>
</describe>
+
<describe sql-case-id="explain_with_analyze">
<select>
<projections start-index="23" stop-index="23">
@@ -215,6 +216,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_with_analyze_format">
<select>
<projections start-index="37" stop-index="37">
@@ -238,11 +240,13 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_with_analyze_delete">
<delete>
<table name="t_order" start-index="28" stop-index="34" />
</delete>
</describe>
+
<describe sql-case-id="explain_with_analyze_delete_condition">
<delete>
<table name="t1" start-index="23" stop-index="24" />
@@ -273,6 +277,7 @@
</where>
</delete>
</describe>
+
<describe sql-case-id="explain_with_analyze_update">
<update>
<table start-index="7" stop-index="13">
@@ -288,6 +293,7 @@
</set>
</update>
</describe>
+
<describe sql-case-id="explain_with_analyze_insert">
<insert>
<table name="t_order" start-index="28" stop-index="34" />
@@ -303,32 +309,40 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="desc_table">
<simple-table name="tableName" start-index="5" stop-index="13" />
</describe>
+
<describe sql-case-id="desc_table_with_col_name">
<simple-table name="tableName" start-index="5" stop-index="13" />
<column-wild name="colName" start-index="15" stop-index="21" />
</describe>
+
<describe sql-case-id="desc_table_with_placeholder">
<simple-table name="tableName" start-index="5" stop-index="13" />
<column-wild name="___" start-index="15" stop-index="17" />
</describe>
+
<describe sql-case-id="desc_table_with_wild">
<simple-table name="tableName" start-index="5" stop-index="13" />
<column-wild name="u%" start-delimiter="`" end-delimiter="`"
start-index="15" stop-index="18" />
</describe>
+
<describe sql-case-id="describe_table">
<simple-table name="tableName" start-index="9" stop-index="17" />
</describe>
+
<describe sql-case-id="describe_table_with_col_name">
<simple-table name="tableName" start-index="9" stop-index="17" />
<column-wild name="colName" start-index="19" stop-index="25" />
</describe>
+
<describe sql-case-id="describe_table_with_placeholder">
<simple-table name="tableName" start-index="5" stop-index="13" />
<column-wild name="___" start-index="15" stop-index="17" />
</describe>
+
<describe sql-case-id="describe_table_with_wild">
<simple-table name="tableName" start-index="5" stop-index="13" />
<column-wild name="u%" start-delimiter="`" end-delimiter="`"
start-index="15" stop-index="18" />
@@ -353,6 +367,7 @@
</describe>
<describe sql-case-id="explain_create_materialized_view_with_data" />
+
<describe sql-case-id="explain_create_materialized_view_with_no_data" />
<describe sql-case-id="explain_performance">
@@ -426,6 +441,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_for_select_with_analyze">
<select>
<projections start-index="24" stop-index="24">
@@ -449,6 +465,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_for_select_with_statement">
<select>
<projections start-index="53" stop-index="53">
@@ -472,6 +489,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_for_select_with_into">
<select>
<projections start-index="37" stop-index="37">
@@ -495,6 +513,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_for_select_with_into_dblink">
<select>
<projections start-index="65" stop-index="65">
@@ -518,6 +537,7 @@
</where>
</select>
</describe>
+
<describe sql-case-id="explain_for_update_without_condition">
<update>
<table start-index="24" stop-index="30">
@@ -549,6 +569,7 @@
</set>
</update>
</describe>
+
<describe sql-case-id="explain_for_update_with_statement">
<update>
<table start-index="37" stop-index="43">
@@ -564,6 +585,7 @@
</set>
</update>
</describe>
+
<describe sql-case-id="explain_for_update_with_into">
<update>
<table start-index="21" stop-index="27">
@@ -579,6 +601,7 @@
</set>
</update>
</describe>
+
<describe sql-case-id="explain_for_update_with_into_dblink">
<update>
<table start-index="48" stop-index="54">
@@ -594,6 +617,7 @@
</set>
</update>
</describe>
+
<describe sql-case-id="explain_for_insert_without_parameters">
<insert>
<table name="t_order" start-index="29" stop-index="35" />
@@ -620,6 +644,7 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="explain_for_with_analyze_insert">
<insert>
<table name="t_order" start-index="29" stop-index="35" />
@@ -635,6 +660,7 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="explain_for_insert_statement">
<insert>
<table name="t_order" start-index="58" stop-index="64" />
@@ -650,6 +676,7 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="explain_for_insert_into">
<insert>
<table name="t_order" start-index="42" stop-index="48" />
@@ -665,6 +692,7 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="explain_for_insert_into_dblink">
<insert>
<table name="t_order" start-index="70" stop-index="76" />
@@ -680,6 +708,7 @@
</values>
</insert>
</describe>
+
<describe sql-case-id="explain_for_delete_without_sharding_value">
<delete>
<table name="t_order" start-index="29" stop-index="35" />
@@ -726,6 +755,7 @@
</where>
</delete>
</describe>
+
<describe sql-case-id="explain_for_delete_statement">
<delete>
<table name="t_order" start-index="58" stop-index="64" />
@@ -746,6 +776,7 @@
</where>
</delete>
</describe>
+
<describe sql-case-id="explain_for_delete_into">
<delete>
<table name="t_order" start-index="42" stop-index="48" />
@@ -766,6 +797,7 @@
</where>
</delete>
</describe>
+
<describe sql-case-id="explain_for_delete_into_dblink">
<delete>
<table name="t_order" start-index="70" stop-index="76" />
@@ -786,4 +818,184 @@
</where>
</delete>
</describe>
+
+ <describe sql-case-id="explain_set_statement_id_with_select">
+ <select>
+ <projections start-index="68" stop-index="76">
+ <column-projection name="last_name" start-index="68"
stop-index="76" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="83"
stop-index="91" />
+ </from>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_set_statement_id_with_into_select1">
+ <select>
+ <projections start-index="109" stop-index="109">
+ <shorthand-projection start-index="109" stop-index="109" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="t" start-index="116"
stop-index="116" />
+ </left>
+ <right>
+ <simple-table name="v" start-index="119"
stop-index="119" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="121" stop-index="159">
+ <expr>
+ <binary-operation-expression start-index="127"
stop-index="159">
+ <left>
+ <column name="department_id" start-index="127"
stop-index="141">
+ <owner name="t" start-index="127"
stop-index="127" />
+ </column>
+ </left>
+ <right>
+ <column name="department_id" start-index="145"
stop-index="159">
+ <owner name="v" start-index="145"
stop-index="145" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ <comment start-index="69" stop-index="93" text="/*+ LEADING(E@SEL$2
D@SEL$2 T@SEL$1) */" />
+ </describe>
+
+ <describe sql-case-id="explain_set_statement_id_with_into_select2">
+ <select>
+ <projections start-index="95" stop-index="95">
+ <shorthand-projection start-index="95" stop-index="95" />
+ </projections>
+ <from>
+ <join-table join-type="COMMA">
+ <left>
+ <simple-table name="t" start-index="102"
stop-index="102" />
+ </left>
+ <right>
+ <simple-table name="v" start-index="105"
stop-index="105" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="107" stop-index="145">
+ <expr>
+ <binary-operation-expression start-index="113"
stop-index="145">
+ <left>
+ <column name="department_id" start-index="113"
stop-index="127">
+ <owner name="t" start-index="113"
stop-index="113" />
+ </column>
+ </left>
+ <right>
+ <column name="department_id" start-index="131"
stop-index="145">
+ <owner name="v" start-index="131"
stop-index="131" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ <comment start-index="69" stop-index="93" text="/*+ LEADING(v.e v.d t)
*/" />
+ </describe>
+
+ <describe sql-case-id="explain_set_statement_id_with_into_update">
+ <update>
+ <table start-index="76" stop-index="84">
+ <simple-table name="employees" start-index="76"
stop-index="84" />
+ </table>
+ <set start-index="86" stop-index="111">
+ <assignment start-index="90" stop-index="111">
+ <column name="salary" start-index="90" stop-index="95" />
+ <assignment-value>
+ <binary-operation-expression start-index="99"
stop-index="111">
+ <left>
+ <column name="salary" start-index="99"
stop-index="104" />
+ </left>
+ <right>
+ <literal-expression value="1.10"
start-index="108" stop-index="111" />
+ </right>
+ <operator>*</operator>
+ </binary-operation-expression>
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="113" stop-index="198">
+ <expr>
+ <binary-operation-expression start-index="119"
stop-index="198">
+ <left>
+ <column name="department_id" start-index="119"
stop-index="131" />
+ </left>
+ <right>
+ <subquery start-index="135" stop-index="198">
+ <select>
+ <projections start-index="143"
stop-index="155">
+ <column-projection
name="department_id" start-index="143" stop-index="155" />
+ </projections>
+ <from>
+ <simple-table name="departments"
start-index="162" stop-index="172" />
+ </from>
+ <where start-index="174" stop-index="197">
+ <expr>
+ <binary-operation-expression
start-index="180" stop-index="197">
+ <left>
+ <column name="location_id"
start-index="180" stop-index="190" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression
value="1700" start-index="194" stop-index="197" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_with_unique_partition_by">
+ <select>
+ <projections start-index="24" stop-index="49">
+ <column-projection name="country" start-index="24"
stop-index="30" />
+ <column-projection name="prod" start-index="33"
stop-index="36" />
+ <column-projection name="year" start-index="39"
stop-index="42" />
+ <column-projection name="sales" start-index="45"
stop-index="49" />
+ </projections>
+ <from>
+ <simple-table name="sales_view" start-index="56"
stop-index="65" />
+ </from>
+ <where start-index="67" stop-index="101">
+ <expr>
+ <in-expression start-index="73" stop-index="101">
+ <not>false</not>
+ <left>
+ <column name="country" start-index="73"
stop-index="79" />
+ </left>
+ <right>
+ <list-expression start-index="84" stop-index="101">
+ <items>
+ <literal-expression value="Italy"
start-index="85" stop-index="91" />
+ </items>
+ <items>
+ <literal-expression value="Japan"
start-index="94" stop-index="100" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ <model start-index="103" stop-index="329">
+ <cell-assignment-column name="sales" start-index="215"
stop-index="219" />
+ <cell-assignment-column name="sales" start-index="269"
stop-index="273" />
+ </model>
+ </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 9176fb3fc0d..e8cf5fdf675 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
@@ -65,4 +65,10 @@
<sql-case id="explain_for_delete_statement" value="EXPLAIN PLAN SET
STATEMENT_ID = 'insert1' FOR DELETE FROM t_order WHERE t_order.x = 1"
db-types="Oracle" />
<sql-case id="explain_for_delete_into" value="EXPLAIN PLAN INTO t_order
FOR DELETE FROM t_order WHERE t_order.x = 1" db-types="Oracle" />
<sql-case id="explain_for_delete_into_dblink" value="EXPLAIN PLAN INTO
t_order@t_database.test_domain_name FOR DELETE FROM t_order WHERE t_order.x =
1" db-types="Oracle" />
+ <sql-case id="explain_set_statement_id_with_select" value="EXPLAIN PLAN
SET STATEMENT_ID = 'st1' INTO my_plan_table FOR SELECT last_name FROM
employees" db-types="Oracle" />
+ <sql-case id="explain_set_statement_id_with_into_select1" value="EXPLAIN
PLAN SET STATEMENT_ID = 'Test 2' INTO plan_table FOR (SELECT /*+
LEADING(E@SEL$2 D@SEL$2 T@SEL$1) */ * FROM t, v WHERE t.department_id =
v.department_id)" db-types="Oracle" />
+ <sql-case id="explain_set_statement_id_with_into_select2" value="EXPLAIN
PLAN SET STATEMENT_ID = 'Test 1' INTO plan_table FOR (SELECT /*+ LEADING(v.e
v.d t) */ * FROM t, v WHERE t.department_id = v.department_id)"
db-types="Oracle" />
+ <sql-case id="explain_set_statement_id_with_into_update" value="EXPLAIN
PLAN SET STATEMENT_ID = 'Raise in Tokyo' INTO plan_table FOR UPDATE employees
SET salary = salary * 1.10 WHERE department_id = (SELECT department_id FROM
departments WHERE location_id = 1700)" db-types="Oracle" />
+ <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-cases>
