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 3f28c79ea12 Support parsing Oracle EXPLAIN PLAN sql(27146) (#29689)
3f28c79ea12 is described below
commit 3f28c79ea12a715b8eed2663195c702eb4336fab
Author: kanha gupta <[email protected]>
AuthorDate: Wed Jan 10 10:25:06 2024 +0530
Support parsing Oracle EXPLAIN PLAN sql(27146) (#29689)
---
.../segment/expression/ExpressionAssert.java | 23 ++++
.../jaxb/segment/impl/expr/ExpectedExpression.java | 4 +
.../ExpectedXmlQueryAndExistsFunctionSegment.java | 46 ++++++++
.../parser/src/main/resources/case/dal/explain.xml | 118 +++++++++++++++++++++
.../main/resources/sql/supported/dal/explain.xml | 4 +
5 files changed, 195 insertions(+)
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/expression/ExpressionAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/expression/ExpressionAssert.java
index a202de5d4cd..44873376b17 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/expression/ExpressionAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/expression/ExpressionAssert.java
@@ -51,6 +51,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.Aggregat
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ExpressionProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.IntervalExpressionProjection;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.DataTypeSegment;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.segment.oracle.xml.XmlQueryAndExistsFunctionSegment;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.SQLCaseAssertContext;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.SQLSegmentAssert;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.column.ColumnAssert;
@@ -85,6 +86,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.simple.ExpectedParameterMarkerExpression;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.simple.ExpectedSubquery;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.function.ExpectedFunction;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.xmlquery.ExpectedXmlQueryAndExistsFunctionSegment;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.type.SQLCaseType;
import java.util.Iterator;
@@ -563,6 +565,25 @@ public final class ExpressionAssert {
}
}
+ /**
+ * Assert xml query and exists function segment.
+ *
+ * @param assertContext assert context
+ * @param actual actual xml query and exists function segment
+ * @param expected expected xml query and exists function segment
+ */
+ private static void assertXmlQueryAndExistsFunctionSegment(final
SQLCaseAssertContext assertContext, final XmlQueryAndExistsFunctionSegment
actual,
+ final
ExpectedXmlQueryAndExistsFunctionSegment expected) {
+ assertThat(assertContext.getText("function name assertion error"),
actual.getFunctionName(), is(expected.getFunctionName()));
+ assertThat(assertContext.getText("xquery string assertion error"),
actual.getXQueryString(), is(expected.getXQueryString()));
+ assertThat(assertContext.getText("parameter size assertion error: "),
actual.getParameters().size(), is(expected.getParameters().size()));
+ Iterator<ExpectedExpression> expectedIterator =
expected.getParameters().iterator();
+ Iterator<ExpressionSegment> actualIterator =
actual.getParameters().iterator();
+ while (expectedIterator.hasNext()) {
+ ExpressionAssert.assertExpression(assertContext,
actualIterator.next(), expectedIterator.next());
+ }
+ }
+
/**
* Assert expression by actual expression segment class type.
*
@@ -632,6 +653,8 @@ public final class ExpressionAssert {
assertRowExpression(assertContext, (RowExpression) actual,
expected.getRowExpression());
} else if (actual instanceof UnaryOperationExpression) {
assertUnaryOperationExpression(assertContext,
(UnaryOperationExpression) actual, expected.getUnaryOperationExpression());
+ } else if (actual instanceof XmlQueryAndExistsFunctionSegment) {
+ assertXmlQueryAndExistsFunctionSegment(assertContext,
(XmlQueryAndExistsFunctionSegment) actual,
expected.getExpectedXmlQueryAndExistsFunctionSegment());
} else {
throw new UnsupportedOperationException(String.format("Unsupported
expression: %s", actual.getClass().getName()));
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/expr/ExpectedExpression.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/expr/ExpectedExpression.java
index 0ef0b0408a2..d8e3ed2eb2f 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/expr/ExpectedExpression.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/expr/ExpectedExpression.java
@@ -29,6 +29,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.generic.ExpectedDataType;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.projection.impl.aggregation.ExpectedAggregationProjection;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.projection.impl.expression.ExpectedExpressionProjection;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.xmlquery.ExpectedXmlQueryAndExistsFunctionSegment;
import javax.xml.bind.annotation.XmlElement;
@@ -119,4 +120,7 @@ public final class ExpectedExpression extends
AbstractExpectedSQLSegment {
@XmlElement(name = "unary-operation-expression")
private ExpectedUnaryOperationExpression unaryOperationExpression;
+
+ @XmlElement(name = "xmlquery-projection")
+ private ExpectedXmlQueryAndExistsFunctionSegment
expectedXmlQueryAndExistsFunctionSegment;
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/xmlquery/ExpectedXmlQueryAndExistsFunctionSegment.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/xmlquery/ExpectedXmlQueryAndExistsFunctionSegment.java
new file mode 100644
index 00000000000..75df92ec5a1
--- /dev/null
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/xmlquery/ExpectedXmlQueryAndExistsFunctionSegment.java
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.xmlquery;
+
+import lombok.Getter;
+import lombok.Setter;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.AbstractExpectedSQLSegment;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.ExpectedExpression;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.projection.ExpectedProjection;
+
+import javax.xml.bind.annotation.XmlAttribute;
+import javax.xml.bind.annotation.XmlElement;
+import java.util.Collection;
+import java.util.LinkedList;
+
+/**
+ * Expected xml query and exists function segment.
+ */
+@Getter
+@Setter
+public class ExpectedXmlQueryAndExistsFunctionSegment extends
AbstractExpectedSQLSegment implements ExpectedProjection {
+
+ @XmlAttribute(name = "function-name")
+ private String functionName;
+
+ @XmlAttribute(name = "xquerystring")
+ private String xQueryString;
+
+ @XmlElement(name = "parameter")
+ private final Collection<ExpectedExpression> parameters = new
LinkedList<>();
+}
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 e8aa2b6cc6d..a07aa935c45 100644
--- a/test/it/parser/src/main/resources/case/dal/explain.xml
+++ b/test/it/parser/src/main/resources/case/dal/explain.xml
@@ -1263,4 +1263,122 @@
</from>
</select>
</describe>
+
+ <describe sql-case-id="explain_for_select_emp_comp_where">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24" />
+ </projections>
+ <from>
+ <simple-table name="emp_comp" start-index="31" stop-index="38"
/>
+ </from>
+ <where start-index="40" stop-index="63">
+ <expr>
+ <binary-operation-expression start-index="46"
stop-index="63">
+ <left>
+ <column name="department_id" start-index="46"
stop-index="58" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="20" start-index="62"
stop-index="63" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_emp_range_where">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24" />
+ </projections>
+ <from>
+ <simple-table name="emp_range" start-index="31"
stop-index="39" />
+ </from>
+ <where start-index="41" stop-index="93" >
+ <expr>
+ <binary-operation-expression start-index="47"
stop-index="93">
+ <left>
+ <column name="hire_date" start-index="47"
stop-index="55"/>
+ </left>
+ <operator><</operator>
+ <right>
+ <function function-name="TO_DATE"
text="TO_DATE('1-JAN-1992','DD-MON-YYYY')" start-index="59" stop-index="93" >
+ <parameter>
+ <literal-expression value="'1-JAN-1992'"
start-index="67" stop-index="78" />
+ </parameter>
+ <parameter>
+ <literal-expression value="'DD-MON-YYYY'"
start-index="80" stop-index="92"/>
+ </parameter>
+ </function>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_where_and_function">
+ <select>
+ <projections start-index="24" stop-index="24">
+ <shorthand-projection start-index="24" stop-index="24" />
+ </projections>
+ <from>
+ <simple-table name="emp_range" start-index="31"
stop-index="39" />
+ </from>
+ <where start-index="41" stop-index="94" >
+ <expr>
+ <binary-operation-expression start-index="47"
stop-index="94">
+ <left>
+ <column name="hire_date" start-index="47"
stop-index="55"/>
+ </left>
+ <operator>>=</operator>
+ <right>
+ <function function-name="TO_DATE"
text="TO_DATE('1-JAN-1996','DD-MON-YYYY')" start-index="60" stop-index="94" >
+ <parameter>
+ <literal-expression value="'1-JAN-1996'"
start-index="68" stop-index="79" />
+ </parameter>
+ <parameter>
+ <literal-expression value="'DD-MON-YYYY'"
start-index="81" stop-index="93"/>
+ </parameter>
+ </function>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </describe>
+
+ <describe sql-case-id="explain_for_select_xml_query">
+ <select>
+ <projections start-index="24" stop-index="107">
+ <expression-projection
text="XMLQUERY('/PurchaseOrder/LineItems/LineItem')" start-index="24"
stop-index="107" />
+ </projections>
+ <from>
+ <simple-table name="po_clob" start-index="114"
stop-index="120" />
+ </from>
+ <where start-index="122" stop-index="303">
+ <expr>
+ <binary-operation-expression start-index="128"
stop-index="303">
+ <left>
+ <xmlquery-projection function-name="XMLExists"
xquerystring="'/PurchaseOrder/LineItems/LineItem [ora:contains(Description,
"Picnic") > 0]'" start-index="128" stop-index="236">
+ <parameter>
+ <column name="OBJECT_VALUE"
start-index="224" stop-index="235"/>
+ </parameter>
+ </xmlquery-projection>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <xmlquery-projection function-name="XMLExists"
xquerystring="'/PurchaseOrder[User="SBELL"]'" start-index="242"
stop-index="303">
+ <parameter>
+ <column name="OBJECT_VALUE"
start-index="291" stop-index="302"/>
+ </parameter>
+ </xmlquery-projection>
+ </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 5871e69b441..e906310eddf 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
@@ -82,4 +82,8 @@
<sql-case id="explain_for_select_emp_comp" value="EXPLAIN PLAN FOR SELECT
* FROM emp_comp;" db-types="Oracle"/>
<sql-case id="explain_for_select_with_comments" value="EXPLAIN PLAN FOR
select /*+ result_cache */ deptno, avg(sal) from emp group by deptno;"
db-types="Oracle"/>
<sql-case id="explain_for_select_job_history" value="EXPLAIN PLAN FOR
SELECT * FROM v_emp_job_history;" db-types="Oracle" />
+ <sql-case id="explain_for_select_emp_comp_where" value="EXPLAIN PLAN FOR
SELECT * FROM emp_comp WHERE department_id = 20;" db-types="Oracle" />
+ <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-cases>
