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 c0ce9627ef8 Support sql federation cte (#28888)
c0ce9627ef8 is described below
commit c0ce9627ef8754d09a9cfe9fcac4488db2a576ad
Author: niu niu <[email protected]>
AuthorDate: Sun Oct 29 17:07:35 2023 +0800
Support sql federation cte (#28888)
* Support mysql cte sql parse
* Add mysql cte sql parse test
* Refactor sql federation WithConverter
* Support sql federation SelectStatement with convert
* Add sql federation cte execution plan test
* Format test sql
* Format parse code
* Change SelectStatementHandler mysql test
---
.../converter/segment/with/WithConverter.java | 32 +--
.../statement/select/SelectStatementConverter.java | 8 +-
.../resources/cases/federation-query-sql-cases.xml | 12 +
.../visitor/statement/MySQLStatementVisitor.java | 29 ++-
.../handler/dml/SelectStatementHandler.java | 6 +
.../statement/mysql/dml/MySQLSelectStatement.java | 12 +
.../handler/dml/SelectStatementHandlerTest.java | 10 +
.../src/test/resources/converter/delete.xml | 4 +-
.../src/test/resources/converter/select-with.xml | 24 ++
.../src/main/resources/case/dml/select-with.xml | 279 +++++++++++++++++++++
.../resources/sql/supported/dml/select-with.xml | 4 +
11 files changed, 400 insertions(+), 20 deletions(-)
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/with/WithConverter.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/with/WithConverter.java
index c27b9d0949b..138a5a80ce5 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/with/WithConverter.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/with/WithConverter.java
@@ -26,8 +26,9 @@ import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
import org.apache.calcite.sql.parser.SqlParserPos;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonTableExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
-import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.ExpressionConverter;
+import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.impl.ColumnConverter;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select.SelectStatementConverter;
import java.util.Collection;
@@ -48,21 +49,20 @@ public final class WithConverter {
* @return sql node list
*/
public static Optional<SqlNode> convert(final WithSegment withSegment,
final SqlNode sqlNode) {
- SqlIdentifier name = new
SqlIdentifier(withSegment.getCommonTableExpressions().iterator().next().getIdentifier().getValue(),
SqlParserPos.ZERO);
- SqlNode selectSubquery = new
SelectStatementConverter().convert(withSegment.getCommonTableExpressions().iterator().next().getSubquery().getSelect());
- Collection<ColumnSegment> collectionColumns =
withSegment.getCommonTableExpressions().iterator().next().getColumns();
- Collection<SqlNode> convertedColumns;
- SqlNodeList columns = null;
- if (!collectionColumns.isEmpty()) {
- convertedColumns =
collectionColumns.stream().map(ExpressionConverter::convert).filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
- columns = new SqlNodeList(convertedColumns, SqlParserPos.ZERO);
- }
- SqlWithItem sqlWithItem = new SqlWithItem(SqlParserPos.ZERO, name,
columns, selectSubquery);
- SqlNodeList sqlWithItems = new SqlNodeList(SqlParserPos.ZERO);
- sqlWithItems.add(sqlWithItem);
- SqlWith sqlWith = new SqlWith(SqlParserPos.ZERO, sqlWithItems,
sqlNode);
+ return Optional.of(new SqlWith(SqlParserPos.ZERO,
convertWithItem(withSegment.getCommonTableExpressions()), sqlNode));
+ }
+
+ private static SqlNodeList convertWithItem(final
Collection<CommonTableExpressionSegment> commonTableExpressionSegments) {
SqlNodeList result = new SqlNodeList(SqlParserPos.ZERO);
- result.add(sqlWith);
- return Optional.of(result);
+ for (CommonTableExpressionSegment each :
commonTableExpressionSegments) {
+ SqlIdentifier name = new
SqlIdentifier(each.getIdentifier().getValue(), SqlParserPos.ZERO);
+ SqlNodeList columns = each.getColumns().isEmpty() ? null :
convertColumns(each.getColumns());
+ result.add(new SqlWithItem(SqlParserPos.ZERO, name, columns, new
SelectStatementConverter().convert(each.getSubquery().getSelect())));
+ }
+ return result;
+ }
+
+ private static SqlNodeList convertColumns(final Collection<ColumnSegment>
columnSegments) {
+ return new SqlNodeList(columnSegments.stream().map(each ->
ColumnConverter.convert(each).orElseThrow(IllegalStateException::new)).collect(Collectors.toList()),
SqlParserPos.ZERO);
}
}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
index a96dc7428e8..a975d0a0232 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
@@ -36,6 +36,7 @@ import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.proje
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.projection.ProjectionsConverter;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.where.WhereConverter;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.window.WindowConverter;
+import
org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.with.WithConverter;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.SQLStatementConverter;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.type.CombineOperatorConverter;
@@ -50,7 +51,8 @@ public final class SelectStatementConverter implements
SQLStatementConverter<Sel
@Override
public SqlNode convert(final SelectStatement selectStatement) {
SqlSelect sqlSelect = convertSelect(selectStatement);
- SqlNode sqlCombine = convertCombine(sqlSelect, selectStatement);
+ SqlNode sqlWith = convertWith(sqlSelect, selectStatement);
+ SqlNode sqlCombine = convertCombine(null != sqlWith ? sqlWith :
sqlSelect, selectStatement);
SqlNodeList orderBy =
selectStatement.getOrderBy().flatMap(OrderByConverter::convert).orElse(SqlNodeList.EMPTY);
Optional<LimitSegment> limit =
SelectStatementHandler.getLimitSegment(selectStatement);
if (limit.isPresent()) {
@@ -61,6 +63,10 @@ public final class SelectStatementConverter implements
SQLStatementConverter<Sel
return orderBy.isEmpty() ? sqlCombine : new
SqlOrderBy(SqlParserPos.ZERO, sqlCombine, orderBy, null, null);
}
+ private SqlNode convertWith(final SqlNode sqlSelect, final SelectStatement
selectStatement) {
+ return
SelectStatementHandler.getWithSegment(selectStatement).flatMap(segment ->
WithConverter.convert(segment, sqlSelect)).orElse(null);
+ }
+
private SqlSelect convertSelect(final SelectStatement selectStatement) {
SqlNodeList distinct =
DistinctConverter.convert(selectStatement.getProjections()).orElse(null);
SqlNodeList projection =
ProjectionsConverter.convert(selectStatement.getProjections()).orElseThrow(IllegalStateException::new);
diff --git
a/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
b/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
index 0584a210361..fbfc970006c 100644
---
a/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
+++
b/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
@@ -436,4 +436,16 @@
<test-case sql="SELECT * FROM multi_types_first first JOIN
multi_types_second second ON first.id = second.id WHERE
second.long_varchar_column = '1'">
<assertion expected-result="EnumerableHashJoin(condition=[=($0, $22)],
joinType=[inner]) EnumerableScan(table=[[federate_jdbc, multi_types_first]],
sql=[SELECT * FROM `federate_jdbc`.`multi_types_first`],
dynamicParameters=[null]) EnumerableScan(table=[[federate_jdbc,
multi_types_second]], sql=[SELECT * FROM `federate_jdbc`.`multi_types_second`
WHERE `long_varchar_column` = '1'], dynamicParameters=[null]) " />
</test-case>
+
+ <test-case sql="WITH cte AS (SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT
3, 4) SELECT col1, col2 FROM cte">
+ <assertion expected-result="EnumerableUnion(all=[true])
EnumerableValues(tuples=[[{ 1, 2 }]]) EnumerableValues(tuples=[[{ 3, 4 }]]) "
/>
+ </test-case>
+
+ <test-case sql="WITH cte1(col1, col2, col3) AS (SELECT id, bit_column,
tiny_int_column FROM multi_types_first), cte2(col1, col2, col3) AS (SELECT id,
bit_column, tiny_int_column FROM multi_types_second) SELECT * FROM cte1 inner
join cte2 on cte1.col1 = cte2.col1">
+ <assertion expected-result="EnumerableHashJoin(condition=[=($0, $3)],
joinType=[inner]) EnumerableScan(table=[[federate_jdbc, multi_types_first]],
sql=[SELECT `id`, `bit_column`, `tiny_int_column` FROM
`federate_jdbc`.`multi_types_first`], dynamicParameters=[null])
EnumerableScan(table=[[federate_jdbc, multi_types_second]], sql=[SELECT `id`,
`bit_column`, `tiny_int_column` FROM `federate_jdbc`.`multi_types_second`],
dynamicParameters=[null]) " />
+ </test-case>
+
+ <test-case sql="WITH cte1(col1, col2, col3) AS (SELECT 1, 2, 3 UNION ALL
SELECT 4, 5, 6), cte2(col1, col2, col3) AS (SELECT 1, 2, 3 UNION ALL SELECT 4,
5, 6) SELECT cte1.* FROM cte1 inner join cte2 on cte1.col1 = cte2.col1 WHERE
cte1.col1 = 1">
+ <assertion expected-result="EnumerableCalc(expr#0..3=[{inputs}],
proj#0..2=[{exprs}]) EnumerableHashJoin(condition=[=($0, $3)],
joinType=[inner]) EnumerableUnion(all=[true])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[=($t0, $t3)],
proj#0..2=[{exprs}], $condition=[$t4]) EnumerableValues(tuples=[[{ 1,
2, 3 }]]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[=($t0,
$t3)], proj#0..2=[{exprs}], $condition=[$t4]) EnumerableValue [...]
+ </test-case>
</test-cases>
diff --git
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
index 9789712de37..821b8c117dc 100644
---
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
+++
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -49,6 +49,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.Complet
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ConstraintNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ConvertFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.CurrentUserFunctionContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.CteClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DataTypeContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DeleteContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DuplicateSpecificationContext;
@@ -147,6 +148,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WindowC
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WindowFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WindowItemContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WindowSpecificationContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WithClauseContext;
import org.apache.shardingsphere.sql.parser.sql.common.enums.AggregationType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
@@ -181,6 +183,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.RowExpre
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.UnaryOperationExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ValuesExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonTableExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.SimpleExpressionSegment;
@@ -220,6 +223,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.Sim
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableNameSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.util.SQLUtils;
import
org.apache.shardingsphere.sql.parser.sql.common.value.collection.CollectionValue;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
@@ -712,6 +716,9 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
if (null != ctx.limitClause()) {
result.setLimit((LimitSegment) visit(ctx.limitClause()));
}
+ if (null != result && null != ctx.withClause()) {
+ result.setWithSegment((WithSegment) visit(ctx.withClause()));
+ }
return result;
}
@@ -727,6 +734,27 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
return result;
}
+ @Override
+ public ASTNode visitWithClause(final WithClauseContext ctx) {
+ Collection<CommonTableExpressionSegment> commonTableExpressions = new
LinkedList<>();
+ for (CteClauseContext each : ctx.cteClause()) {
+ commonTableExpressions.add((CommonTableExpressionSegment)
visit(each));
+ }
+ return new WithSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), commonTableExpressions);
+ }
+
+ @SuppressWarnings("unchecked")
+ @Override
+ public ASTNode visitCteClause(final CteClauseContext ctx) {
+ CommonTableExpressionSegment result = new
CommonTableExpressionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (IdentifierValue) visit(ctx.identifier()),
+ new SubquerySegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (MySQLSelectStatement) visit(ctx.subquery()),
getOriginalText(ctx.subquery())));
+ if (null != ctx.columnNames()) {
+ CollectionValue<ColumnSegment> columns =
(CollectionValue<ColumnSegment>) visit(ctx.columnNames());
+ result.getColumns().addAll(columns.getValue());
+ }
+ return result;
+ }
+
@Override
public ASTNode visitQueryExpressionBody(final QueryExpressionBodyContext
ctx) {
if (1 == ctx.getChildCount() && ctx.getChild(0) instanceof
QueryPrimaryContext) {
@@ -1592,7 +1620,6 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
@Override
public ASTNode visitSelect(final SelectContext ctx) {
- // TODO :Unsupported for withClause.
MySQLSelectStatement result;
if (null != ctx.queryExpression()) {
result = (MySQLSelectStatement) visit(ctx.queryExpression());
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
index daeeaeffcde..db1a92b0866 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
@@ -183,6 +183,9 @@ public final class SelectStatementHandler implements
SQLStatementHandler {
if (selectStatement instanceof SQLServerSelectStatement) {
return ((SQLServerSelectStatement)
selectStatement).getWithSegment();
}
+ if (selectStatement instanceof MySQLSelectStatement) {
+ return ((MySQLSelectStatement) selectStatement).getWithSegment();
+ }
return Optional.empty();
}
@@ -199,6 +202,9 @@ public final class SelectStatementHandler implements
SQLStatementHandler {
if (selectStatement instanceof SQLServerSelectStatement) {
((SQLServerSelectStatement)
selectStatement).setWithSegment(withSegment);
}
+ if (selectStatement instanceof MySQLSelectStatement) {
+ ((MySQLSelectStatement)
selectStatement).setWithSegment(withSegment);
+ }
}
/**
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/mysql/dml/MySQLSelectStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/mysql/dml/MySQLSelectStatement.java
index 018bd27736b..f6d35886c5b 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/mysql/dml/MySQLSelectStatement.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/mysql/dml/MySQLSelectStatement.java
@@ -21,6 +21,7 @@ import lombok.Setter;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WindowSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.MySQLStatement;
@@ -41,6 +42,8 @@ public final class MySQLSelectStatement extends
SelectStatement implements MySQL
private WindowSegment window;
+ private WithSegment withSegment;
+
/**
* Get order by segment.
*
@@ -76,4 +79,13 @@ public final class MySQLSelectStatement extends
SelectStatement implements MySQL
public Optional<SimpleTableSegment> getTable() {
return Optional.ofNullable(table);
}
+
+ /**
+ * Get with segment.
+ *
+ * @return with segment.
+ */
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
}
diff --git
a/parser/sql/statement/src/test/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandlerTest.java
b/parser/sql/statement/src/test/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandlerTest.java
index 2b8abdc2f74..f4b3ff013d0 100644
---
a/parser/sql/statement/src/test/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandlerTest.java
+++
b/parser/sql/statement/src/test/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandlerTest.java
@@ -187,6 +187,16 @@ class SelectStatementHandlerTest {
assertFalse(SelectStatementHandler.getWithSegment(new
OracleSelectStatement()).isPresent());
}
+ @Test
+ void assertGetWithSegmentForMysql() {
+ MySQLSelectStatement selectStatement = new MySQLSelectStatement();
+ selectStatement.setWithSegment(new WithSegment(0, 2, new
LinkedList<>()));
+ Optional<WithSegment> withSegment =
SelectStatementHandler.getWithSegment(selectStatement);
+ assertTrue(withSegment.isPresent());
+ assertThat(withSegment.get(),
is(selectStatement.getWithSegment().get()));
+ assertFalse(SelectStatementHandler.getWithSegment(new
MySQLSelectStatement()).isPresent());
+ }
+
@Test
void assertGetWithSegmentForSQLServer() {
SQLServerSelectStatement selectStatement = new
SQLServerSelectStatement();
diff --git a/test/it/optimizer/src/test/resources/converter/delete.xml
b/test/it/optimizer/src/test/resources/converter/delete.xml
index 1f320631d0b..331174393e6 100644
--- a/test/it/optimizer/src/test/resources/converter/delete.xml
+++ b/test/it/optimizer/src/test/resources/converter/delete.xml
@@ -40,6 +40,6 @@
<test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM
[t_order] AS [o] AS [o] WHERE [status] = 'init'" db-types="SQLServer"
sql-case-types="LITERAL" />
<test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM
`t_order` AS `o` AS `o` WHERE `status` = ?" db-types="MySQL"
sql-case-types="PLACEHOLDER" />
<test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM
[t_order] AS [o] AS [o] WHERE [status] = ?" db-types="SQLServer"
sql-case-types="PLACEHOLDER" />
- <test-cases sql-case-id="delete_with_with_clause" expected-sql="(WITH
[cte] ([order_id], [user_id]) AS (SELECT [order_id], [user_id] FROM [t_order])
DELETE FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id])"
db-types="SQLServer" />
- <test-cases sql-case-id="delete_without_columns_with_with_clause"
expected-sql="(WITH [cte] AS (SELECT [order_id], [user_id] FROM [t_order])
DELETE FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id])"
db-types="SQLServer" />
+ <test-cases sql-case-id="delete_with_with_clause" expected-sql="WITH [cte]
([order_id], [user_id]) AS (SELECT [order_id], [user_id] FROM [t_order]) DELETE
FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id]"
db-types="SQLServer" />
+ <test-cases sql-case-id="delete_without_columns_with_with_clause"
expected-sql="WITH [cte] AS (SELECT [order_id], [user_id] FROM [t_order])
DELETE FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id]"
db-types="SQLServer" />
</sql-node-converter-test-cases>
diff --git a/test/it/optimizer/src/test/resources/converter/select-with.xml
b/test/it/optimizer/src/test/resources/converter/select-with.xml
new file mode 100644
index 00000000000..646c41e1793
--- /dev/null
+++ b/test/it/optimizer/src/test/resources/converter/select-with.xml
@@ -0,0 +1,24 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ 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.
+ -->
+
+<sql-node-converter-test-cases>
+ <test-cases sql-case-id="select_with_single_subquery" expected-sql="WITH
`t` AS (SELECT `a` + 2 AS `c`, `b` FROM `t1`) SELECT `c`, `b` FROM `t`"
db-types="MySQL" />
+ <test-cases sql-case-id="select_with_multiple_subquery" expected-sql="WITH
`cte1` (`col1`, `col2`, `col3`) AS (SELECT `emp_no`, `first_name`, `last_name`
FROM `employees` WHERE `emp_no` = 10012), `cte2` (`col1`, `col2`, `col3`) AS
(SELECT `emp_no`, `first_name`, `last_name` FROM `employees` WHERE `emp_no` =
10012) SELECT `col1`, `col2`, `col3` FROM `cte1`" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_recursive_union_all1"
expected-sql="WITH `DirectoryCTE` AS (SELECT * FROM `table1` WHERE `id` = 1 AND
`project_id` = 2 UNION ALL SELECT * FROM `project_file_catalog` AS `t` INNER
JOIN `DirectoryCTE` AS `cte` ON `t`.`project_id` = `cte`.`project_id` AND
`t`.`parent_id` = `cte`.`id`) SELECT * FROM `DirectoryCTE` ORDER BY `level`"
db-types="MySQL" />
+ <test-cases sql-case-id="select_with_recursive_union_all2"
expected-sql="WITH `cte` AS (SELECT 1 AS `col1`, 2 AS `col2` UNION ALL SELECT
3, 4) SELECT `col1`, `col2` FROM `cte`" db-types="MySQL" />
+</sql-node-converter-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-with.xml
b/test/it/parser/src/main/resources/case/dml/select-with.xml
index 1d579c10569..fb1b2021e4a 100644
--- a/test/it/parser/src/main/resources/case/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-with.xml
@@ -364,4 +364,283 @@
<column-projection name="item_id" start-index="145"
stop-index="151" />
</projections>
</select>
+
+ <select sql-case-id="select_with_single_subquery">
+ <with start-index="0" stop-index="33">
+ <common-table-expression name="t" start-index="5" stop-index="33">
+ <subquery-expression start-index="5" stop-index="33">
+ <select>
+ <from>
+ <simple-table name="t1" start-index="31"
stop-index="32" />
+ </from>
+ <projections start-index="18" stop-index="24">
+ <expression-projection text="a+2" alias="c"
start-index="18" stop-index="22">
+ <expr>
+ <binary-operation-expression
start-index="18" stop-index="20">
+ <left>
+ <column name="a" start-index="18"
stop-index="18" />
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="2"
start-index="20" stop-index="20" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ <column-projection name="b" start-index="24"
stop-index="24" />
+ </projections>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="42" stop-index="44">
+ <column-projection name="c" start-index="42" stop-index="42" />
+ <column-projection name="b" start-index="44" stop-index="44" />
+ </projections>
+ <from>
+ <simple-table name="t" start-index="51" stop-index="51" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_multiple_subquery">
+ <with start-index="0" stop-index="198">
+ <common-table-expression name="cte1" start-index="5"
stop-index="100">
+ <subquery-expression start-index="5" stop-index="100">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="72"
stop-index="80" />
+ </from>
+ <projections start-index="39" stop-index="65">
+ <column-projection name="emp_no" start-index="39"
stop-index="44" />
+ <column-projection name="first_name"
start-index="46" stop-index="55" />
+ <column-projection name="last_name"
start-index="57" stop-index="65" />
+ </projections>
+ <where start-index="82" stop-index="99">
+ <expr>
+ <binary-operation-expression start-index="88"
stop-index="99">
+ <left>
+ <column name="emp_no" start-index="88"
stop-index="93" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="10012"
start-index="95" stop-index="99" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ <column name="col1" start-index="10" stop-index="13" />
+ <column name="col2" start-index="16" stop-index="19" />
+ <column name="col3" start-index="22" stop-index="25" />
+ </common-table-expression>
+ <common-table-expression name="cte2" start-index="103"
stop-index="198">
+ <subquery-expression start-index="103" stop-index="198">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="170"
stop-index="178" />
+ </from>
+ <projections start-index="137" stop-index="163">
+ <column-projection name="emp_no" start-index="137"
stop-index="142" />
+ <column-projection name="first_name"
start-index="144" stop-index="153" />
+ <column-projection name="last_name"
start-index="155" stop-index="163" />
+ </projections>
+ <where start-index="180" stop-index="197">
+ <expr>
+ <binary-operation-expression start-index="186"
stop-index="197">
+ <left>
+ <column name="emp_no"
start-index="186" stop-index="191" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="10012"
start-index="193" stop-index="197" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ <column name="col1" start-index="108" stop-index="111" />
+ <column name="col2" start-index="114" stop-index="117" />
+ <column name="col3" start-index="120" stop-index="123" />
+ </common-table-expression>
+ </with>
+ <projections start-index="207" stop-index="222">
+ <column-projection name="col1" start-index="207" stop-index="210"
/>
+ <column-projection name="col2" start-index="213" stop-index="216"
/>
+ <column-projection name="col3" start-index="219" stop-index="222"
/>
+ </projections>
+ <from>
+ <simple-table name="cte1" start-index="229" stop-index="232" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_recursive_union_all1">
+ <with start-index="0" stop-index="217">
+ <common-table-expression name="DirectoryCTE" start-index="15"
stop-index="217">
+ <subquery-expression start-index="15" stop-index="217">
+ <select>
+ <from>
+ <simple-table name="table1" start-index="46"
stop-index="51" />
+ </from>
+ <projections start-index="39" stop-index="39">
+ <shorthand-projection start-index="39"
stop-index="39" />
+ </projections>
+ <combine combine-type="UNION_ALL" start-index="85"
stop-index="216">
+ <left>
+ <projections start-index="39" stop-index="39">
+ <shorthand-projection start-index="39"
stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="table1"
start-index="46" stop-index="51" />
+ </from>
+ <where start-index="53" stop-index="83">
+ <expr>
+ <binary-operation-expression
start-index="59" stop-index="83">
+ <left>
+ <binary-operation-expression
start-index="59" stop-index="64">
+ <left>
+ <column name="id"
start-index="59" stop-index="60" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression
value="1" start-index="64" stop-index="64" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <binary-operation-expression
start-index="70" stop-index="83">
+ <left>
+ <column
name="project_id" start-index="70" stop-index="79" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression
value="2" start-index="83" stop-index="83" />
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </left>
+ <right>
+ <from>
+ <join-table join-type="INNER">
+ <left>
+ <simple-table
name="project_file_catalog" alias="t" start-index="109" stop-index="130" />
+ </left>
+ <right>
+ <simple-table name="DirectoryCTE"
alias="cte" start-index="143" stop-index="158" />
+ </right>
+ <on-condition>
+ <binary-operation-expression
start-index="163" stop-index="216">
+ <left>
+
<binary-operation-expression start-index="163" stop-index="191">
+ <left>
+ <column
name="project_id" start-index="163" stop-index="174">
+ <owner
name="t" start-index="163" stop-index="163" />
+ </column>
+ </left>
+ <right>
+ <column
name="project_id" start-index="178" stop-index="191">
+ <owner
name="cte" start-index="178" stop-index="180" />
+ </column>
+ </right>
+ <operator>=</operator>
+
</binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+
<binary-operation-expression start-index="197" stop-index="216">
+ <left>
+ <column
name="parent_id" start-index="197" stop-index="207">
+ <owner
name="t" start-index="197" stop-index="197" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="id"
start-index="211" stop-index="216">
+ <owner
name="cte" start-index="211" stop-index="213" />
+ </column>
+ </right>
+
</binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ <projections start-index="102"
stop-index="102">
+ <shorthand-projection start-index="102"
stop-index="102" />
+ </projections>
+ </right>
+ </combine>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="226" stop-index="226">
+ <shorthand-projection start-index="226" stop-index="226" />
+ </projections>
+ <from>
+ <simple-table name="DirectoryCTE" start-index="233"
stop-index="244" />
+ </from>
+ <order-by>
+ <column-item name="level" order-direction="ASC" start-index="255"
stop-index="259" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_recursive_union_all2">
+ <with start-index="0" stop-index="62">
+ <common-table-expression name="cte" start-index="5"
stop-index="62">
+ <subquery-expression start-index="5" stop-index="62">
+ <select>
+ <projections start-index="20" stop-index="39">
+ <expression-projection text="1" alias="col1"
start-index="20" stop-index="28">
+ <expr>
+ <literal-expression value="1"
start-index="20" stop-index="20" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="2" alias="col2"
start-index="31" stop-index="39">
+ <literal-expression value="2" start-index="31"
stop-index="31" />
+ </expression-projection>
+ </projections>
+ <combine combine-type="UNION_ALL" start-index="41"
stop-index="61">
+ <left>
+ <projections start-index="20" stop-index="39">
+ <expression-projection text="1"
alias="col1" start-index="20" stop-index="28">
+ <expr>
+ <literal-expression value="1"
start-index="20" stop-index="20" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="2"
alias="col2" start-index="31" stop-index="39">
+ <literal-expression value="2"
start-index="31" stop-index="31" />
+ </expression-projection>
+ </projections>
+ </left>
+ <right>
+ <projections start-index="58" stop-index="61">
+ <expression-projection text="3"
start-index="58" stop-index="58">
+ <expr>
+ <literal-expression value="3"
start-index="58" stop-index="58" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="4"
start-index="61" stop-index="61">
+ <literal-expression value="4"
start-index="61" stop-index="61" />
+ </expression-projection>
+ </projections>
+ </right>
+ </combine>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="71" stop-index="80">
+ <column-projection name="col1" start-index="71" stop-index="74" />
+ <column-projection name="col2" start-index="77" stop-index="80" />
+ </projections>
+ <from>
+ <simple-table name="cte" start-index="87" stop-index="89" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
index 75a1f7cdd3e..d6d7e917c3a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
@@ -23,4 +23,8 @@
<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-case id="select_with_single_subquery" value="WITH t AS (SELECT a+2
c,b FROM t1) SELECT c,b FROM t" db-types="MySQL" />
+ <sql-case id="select_with_multiple_subquery" value="WITH cte1(col1, col2,
col3) as (SELECT emp_no,first_name,last_name FROM employees WHERE
emp_no=10012), cte2(col1, col2, col3) as (SELECT emp_no,first_name,last_name
from employees WHERE emp_no=10012) SELECT col1, col2, col3 FROM cte1"
db-types="MySQL" />
+ <sql-case id="select_with_recursive_union_all1" value="WITH RECURSIVE
DirectoryCTE as (SELECT * FROM table1 WHERE id = 1 AND project_id = 2 UNION ALL
SELECT * FROM project_file_catalog t INNER JOIN DirectoryCTE cte ON
t.project_id = cte.project_id AND t.parent_id = cte.id) SELECT * FROM
DirectoryCTE ORDER BY level" db-types="MySQL" />
+ <sql-case id="select_with_recursive_union_all2" value="WITH cte AS (SELECT
1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4) SELECT col1, col2 FROM cte"
db-types="MySQL" />
</sql-cases>
