You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/05/05 00:39:32 UTC
[shardingsphere] branch master updated: Support WITH Common Table Expression of SQLServer (#17321)
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 0741447a46f Support WITH Common Table Expression of SQLServer (#17321)
0741447a46f is described below
commit 0741447a46f204fc383b9ada9e9b83698f2a3a75
Author: Thanoshan MV <48...@users.noreply.github.com>
AuthorDate: Thu May 5 06:09:16 2022 +0530
Support WITH Common Table Expression of SQLServer (#17321)
* Add Initial SQLServer CTE Expression
* Add SQLServer CTE Clause
---
.../main/antlr4/imports/sqlserver/DMLStatement.g4 | 8 ++-
.../impl/SQLServerStatementSQLVisitor.java | 41 +++++++------
.../sql/common/statement/dml/SelectStatement.java | 12 ----
.../handler/dml/SelectStatementHandler.java | 5 +-
.../oracle/dml/OracleSelectStatement.java | 12 ++++
.../sqlserver/dml/SQLServerSelectStatement.java | 12 ++++
.../src/main/resources/case/dml/select-with.xml | 67 ++++++++++++++++++++++
.../resources/sql/supported/dml/select-with.xml | 5 +-
8 files changed, 129 insertions(+), 33 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
index 6d11977c8d1..e868949cba9 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
@@ -154,7 +154,11 @@ subquery
;
withClause
- : WITH cteClause (COMMA_ cteClause)*
+ : WITH cteClauseSet
+ ;
+
+cteClauseSet
+ : cteClause (COMMA_ cteClause)*
;
cteClause
@@ -248,7 +252,7 @@ forJsonClause
;
selectWithClause
- : WITH (xmlNamespacesClause COMMA_?)?
+ : WITH (xmlNamespacesClause COMMA_?)? cteClauseSet?
;
xmlNamespacesClause
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
index 7a23cc1bbea..0981dcec708 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
@@ -44,6 +44,7 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Col
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ConstraintNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTableAsSelectClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CteClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CteClauseSetContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DataTypeContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DataTypeLengthContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DataTypeNameContext;
@@ -672,7 +673,6 @@ public abstract class SQLServerStatementSQLVisitor extends SQLServerStatementBas
@Override
public ASTNode visitSelect(final SelectContext ctx) {
- // TODO :Unsupported for withClause.
SQLServerSelectStatement result = (SQLServerSelectStatement) visit(ctx.aggregationClause());
result.setParameterCount(getCurrentParameterIndex());
result.getParameterMarkerSegments().addAll(getParameterMarkerSegments());
@@ -689,6 +689,11 @@ public abstract class SQLServerStatementSQLVisitor extends SQLServerStatementBas
public ASTNode visitSelectClause(final SelectClauseContext ctx) {
SQLServerSelectStatement result = new SQLServerSelectStatement();
result.setProjections((ProjectionsSegment) visit(ctx.projections()));
+ if (null != ctx.selectWithClause() && null != ctx.selectWithClause().cteClauseSet()) {
+ Collection<CommonTableExpressionSegment> commonTableExpressionSegments = getCommonTableExpressionSegmentsUsingCteClauseSet(ctx.selectWithClause().cteClauseSet());
+ WithSegment withSegment = new WithSegment(ctx.selectWithClause().start.getStartIndex(), ctx.selectWithClause().stop.getStopIndex(), commonTableExpressionSegments);
+ result.setWithSegment(withSegment);
+ }
if (null != ctx.duplicateSpecification()) {
result.getProjections().setDistinctRow(isDistinct(ctx));
}
@@ -711,6 +716,23 @@ public abstract class SQLServerStatementSQLVisitor extends SQLServerStatementBas
return result;
}
+ private Collection<CommonTableExpressionSegment> getCommonTableExpressionSegmentsUsingCteClauseSet(final CteClauseSetContext ctx) {
+ Collection<CommonTableExpressionSegment> result = new LinkedList<>();
+ for (CteClauseContext each : ctx.cteClause()) {
+ SubquerySegment subquery = new SubquerySegment(each.subquery().aggregationClause().start.getStartIndex(),
+ each.subquery().aggregationClause().stop.getStopIndex(), (SQLServerSelectStatement) visit(each.subquery()));
+ IdentifierValue identifier = (IdentifierValue) visit(each.identifier());
+ CommonTableExpressionSegment commonTableExpression = new CommonTableExpressionSegment(each.start.getStartIndex(), each.stop.getStopIndex(), identifier, subquery);
+ if (null != each.columnNames()) {
+ ColumnNamesContext columnNames = each.columnNames();
+ CollectionValue<ColumnSegment> columns = (CollectionValue<ColumnSegment>) visit(columnNames);
+ commonTableExpression.getColumns().addAll(columns.getValue());
+ }
+ result.add(commonTableExpression);
+ }
+ return result;
+ }
+
@Override
public ASTNode visitHavingClause(final HavingClauseContext ctx) {
ExpressionSegment expr = (ExpressionSegment) visit(ctx.expr());
@@ -924,21 +946,8 @@ public abstract class SQLServerStatementSQLVisitor extends SQLServerStatementBas
@Override
public ASTNode visitWithClause(final WithClauseContext ctx) {
- List<CteClauseContext> cteClauses = ctx.cteClause();
- Collection<CommonTableExpressionSegment> commonTableExpressions = new LinkedList<>();
- for (CteClauseContext each : cteClauses) {
- SubquerySegment subquery = new SubquerySegment(each.subquery().aggregationClause().start.getStartIndex(),
- each.subquery().aggregationClause().stop.getStopIndex(), (SQLServerSelectStatement) visit(each.subquery()));
- IdentifierValue identifier = (IdentifierValue) visit(each.identifier());
- CommonTableExpressionSegment commonTableExpression = new CommonTableExpressionSegment(each.start.getStartIndex(), each.stop.getStopIndex(), identifier, subquery);
- if (null != each.columnNames()) {
- ColumnNamesContext columnNames = each.columnNames();
- CollectionValue<ColumnSegment> columns = (CollectionValue<ColumnSegment>) visit(columnNames);
- commonTableExpression.getColumns().addAll(columns.getValue());
- }
- commonTableExpressions.add(commonTableExpression);
- }
- return new WithSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), commonTableExpressions);
+ Collection<CommonTableExpressionSegment> commonTableExpressionSegments = getCommonTableExpressionSegmentsUsingCteClauseSet(ctx.cteClauseSet());
+ return new WithSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), commonTableExpressionSegments);
}
@Override
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
index 427fa36a4ba..ed5c2bfc612 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
@@ -26,7 +26,6 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBy
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.HavingSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.union.UnionSegment;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.AbstractSQLStatement;
@@ -54,8 +53,6 @@ public abstract class SelectStatement extends AbstractSQLStatement implements DM
private OrderBySegment orderBy;
- private WithSegment withSegment;
-
private Collection<UnionSegment> unionSegments = new LinkedList<>();
/**
@@ -93,13 +90,4 @@ public abstract class SelectStatement extends AbstractSQLStatement implements DM
public Optional<OrderBySegment> getOrderBy() {
return Optional.ofNullable(orderBy);
}
-
- /**
- * Get with segment.
- *
- * @return with segment.
- */
- public Optional<WithSegment> getWithSegment() {
- return Optional.ofNullable(withSegment);
- }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
index cfb3c67fba2..bce4c3d0de6 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
@@ -121,7 +121,10 @@ public final class SelectStatementHandler implements SQLStatementHandler {
*/
public static Optional<WithSegment> getWithSegment(final SelectStatement selectStatement) {
if (selectStatement instanceof OracleStatement) {
- return selectStatement.getWithSegment();
+ return ((OracleSelectStatement) selectStatement).getWithSegment();
+ }
+ if (selectStatement instanceof SQLServerStatement) {
+ return ((SQLServerSelectStatement) selectStatement).getWithSegment();
}
return Optional.empty();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleSelectStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleSelectStatement.java
index dbb21466994..f45d1262c3d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleSelectStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleSelectStatement.java
@@ -21,6 +21,7 @@ import lombok.Setter;
import lombok.ToString;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.ModelSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
@@ -37,6 +38,8 @@ public final class OracleSelectStatement extends SelectStatement implements Orac
private ModelSegment modelSegment;
+ private WithSegment withSegment;
+
/**
* Get lock segment.
*
@@ -54,4 +57,13 @@ public final class OracleSelectStatement extends SelectStatement implements Orac
public Optional<ModelSegment> getModelSegment() {
return Optional.ofNullable(modelSegment);
}
+
+ /**
+ * Get with segment.
+ *
+ * @return with segment.
+ */
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerSelectStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerSelectStatement.java
index 034352d5734..6fe076db3d7 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerSelectStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/sqlserver/dml/SQLServerSelectStatement.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.dml
import lombok.Setter;
import lombok.ToString;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.SQLServerStatement;
@@ -34,6 +35,8 @@ public final class SQLServerSelectStatement extends SelectStatement implements S
private LimitSegment limit;
+ private WithSegment withSegment;
+
/**
* Get order by segment.
*
@@ -42,4 +45,13 @@ public final class SQLServerSelectStatement extends SelectStatement implements S
public Optional<LimitSegment> getLimit() {
return Optional.ofNullable(limit);
}
+
+ /**
+ * Get with segment.
+ *
+ * @return with segment.
+ */
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
}
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-with.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-with.xml
index fd888e7d4dc..e22feb5a936 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-with.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-with.xml
@@ -307,4 +307,71 @@
<column-item name="emp_last" start-index="351" stop-index="358" />
</order-by>
</select>
+
+ <select sql-case-id="select_with_multiple_cte_definitions">
+ <with start-index="0" stop-index="119">
+ <common-table-expression name="cte1" start-index="5" stop-index="66">
+ <subquery-expression start-index="31" stop-index="65">
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="59" stop-index="65" />
+ </from>
+ <projections start-index="38" stop-index="52">
+ <column-projection name="status" start-index="38" stop-index="43" />
+ <column-projection name="user_id" start-index="46" stop-index="52" />
+ </projections>
+ </select>
+ </subquery-expression>
+ <columns start-index="10" stop-index="24">
+ <column name="status" start-index="10" stop-index="15" />
+ <column name="user_id" start-index="18" stop-index="24" />
+ </columns>
+ </common-table-expression>
+ <common-table-expression name="cte2" start-index="69" stop-index="119">
+ <subquery-expression start-index="87" stop-index="118">
+ <select>
+ <from>
+ <simple-table name="t_order_item" start-index="107" stop-index="118" />
+ </from>
+ <projections start-index="94" stop-index="100">
+ <column-projection name="item_id" start-index="94" stop-index="100" />
+ </projections>
+ </select>
+ </subquery-expression>
+ <columns start-index="74" stop-index="80">
+ <column name="item_id" start-index="74" stop-index="80" />
+ </columns>
+ </common-table-expression>
+ </with>
+ <from>
+ <join-table>
+ <left>
+ <simple-table name="cte1" start-index="158" stop-index="161" />
+ </left>
+ <right>
+ <simple-table name="cte2" start-index="174" stop-index="177" />
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="182" stop-index="208">
+ <left>
+ <column name="user_id" start-index="182" stop-index="193">
+ <owner name="cte1" start-index="182" stop-index="185" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="user_id" start-index="197" stop-index="208">
+ <owner name="cte2" start-index="197" stop-index="200" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ <projections start-index="128" stop-index="151">
+ <column-projection name="status" start-index="128" stop-index="133" />
+ <column-projection name="user_id" start-index="136" stop-index="142" />
+ <column-projection name="item_id" start-index="145" stop-index="151" />
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-with.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-with.xml
index dd2dd507b63..afeb4691e7e 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-with.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-with.xml
@@ -17,9 +17,10 @@
-->
<sql-cases>
- <sql-case id="select_with_subquery_factoring" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM departments d GROUP BY department_name) SELECT * FROM dept_costs WHERE dept_total > 304500 ORDER BY department_name" db-types="Oracle"/>
- <sql-case id="select_with_subquery_factoring_with_binding_tables_without_join" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name" db-types="Oracle"/>
+ <sql-case id="select_with_subquery_factoring" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM departments d GROUP BY department_name) SELECT * FROM dept_costs WHERE dept_total > 304500 ORDER BY department_name" db-types="Oracle, SQLServer"/>
+ <sql-case id="select_with_subquery_factoring_with_binding_tables_without_join" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name" db-types="Oracle, SQLServer"/>
<sql-case id="select_with_subquery_factoring_with_search_depth_first" value="WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, salary, job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_name, eid, mgr_id, salary, job_id FROM org_chart ORDER BY order1" db-types="Oracle"/>
<sql-case id="select_with_subquery_factoring_with_search_depth_first_with_cycle" value="WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, hire_date, job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY hire_date SET order1 CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle FROM dup_hiredate ORDER [...]
<sql-case id="select_with_subquery_factoring_with_search_depth_first_with_having" value="WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS (SELECT employee_id, last_name, manager_id, mgrLevel, salary, cnt_employees FROM employees) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid, mgr_id, salary FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING salary > 24000 ORDER BY mgr_id NULLS FIRST, emp_last" db-types="Oracle"/>
+ <sql-case id="select_with_multiple_cte_definitions" value="WITH cte1(status, user_id) AS (SELECT status, user_id FROM t_order), cte2(item_id) AS (SELECT item_id FROM t_order_item) SELECT status, user_id, item_id FROM cte1 INNER JOIN cte2 ON cte1.user_id = cte2.user_id" db-types="SQLServer"/>
</sql-cases>