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 5c6745e7c3f Issue 31437 with keyword query error mysql (#34163)
5c6745e7c3f is described below
commit 5c6745e7c3f318300b42e9f29abb2e7ce8cbd873
Author: Yash-cor <yash.tiw...@coriolis.co.in>
AuthorDate: Thu Jan 9 07:58:37 2025 +0530
Issue 31437 with keyword query error mysql (#34163)
* Added the With Segment Binder which improves the working of With Query
and resolved the CheckTableExist error during With keyword Query execution
* Resolves Merge Conflicts
* Added unique Alias Exception while using Common Table Expression
* Fix Spotless error
* Added Release Notes
* Added SQL bind test case for WithSegmentBinder
* Added Release Notes
* Resolve Merge Conflict
* Added Release Notes
* Solved Spotless and Checkstyle error
* Changed the working for Unique Alias Name Exception handling for CTE's
* Update Release Notes
* Changed Release Notes to resolve merge conflict
* Sync With Master Branch
* Removed the Changes for with Clause in DeleteStatement will raise it with
a different PR.
* Added SQLBinderIT test case for with clause.
* Updated documentation for the new Exception created
* Added e2e test case for with clause in e2e\sql\resources\cases\dql
* Corrected e2e test case for with clause
* Refactor to sync master branch
* Updates for e2e test case failure
* Changes made in e2e test case for db and tbl
* Changes in e2e test case
---------
Co-authored-by: Zhengqiang Duan <duanzhengqi...@apache.org>
---
.../user-manual/error-code/sql-error-code.cn.md | 1 +
.../user-manual/error-code/sql-error-code.en.md | 1 +
.../segment/dml/combine/CombineSegmentBinder.java | 2 +
.../dml/expression/type/SubquerySegmentBinder.java | 2 +
.../dml/from/type/SimpleTableSegmentBinder.java | 3 +
.../dml/from/type/SubqueryTableSegmentBinder.java | 2 +
.../with/CommonTableExpressionSegmentBinder.java | 7 +
.../statement/SQLStatementBinderContext.java | 2 +
.../infra/binder/with/WithSegmentBinderTest.java | 120 ++++++++++++++
...plicateCommonTableExpressionAliasException.java | 33 ++++
.../dql/e2e-dql-select-using-with-clause.xml} | 12 +-
.../binder/src/test/resources/cases/dml/select.xml | 184 +++++++++++++++++++++
.../binder/src/test/resources/sqls/dml/select.xml | 1 +
13 files changed, 363 insertions(+), 7 deletions(-)
diff --git a/docs/document/content/user-manual/error-code/sql-error-code.cn.md
b/docs/document/content/user-manual/error-code/sql-error-code.cn.md
index a09db6bb79f..7c411a7986e 100644
--- a/docs/document/content/user-manual/error-code/sql-error-code.cn.md
+++ b/docs/document/content/user-manual/error-code/sql-error-code.cn.md
@@ -70,6 +70,7 @@ SQL 错误码以标准的 SQL State，Vendor Code 和详细错误信息提供，
| 12101 | 42000 | Can not accept SQL type '%s'. |
| 12200 | 42000 | Hint data source '%s' does not exist. |
| 12300 | 0A000 | DROP TABLE ... CASCADE is not supported. |
+| 12500 | 42000 | Not unique table/alias: '%s' |
### 连接
diff --git a/docs/document/content/user-manual/error-code/sql-error-code.en.md
b/docs/document/content/user-manual/error-code/sql-error-code.en.md
index 282e53f2ea8..cde5dbb6cf5 100644
--- a/docs/document/content/user-manual/error-code/sql-error-code.en.md
+++ b/docs/document/content/user-manual/error-code/sql-error-code.en.md
@@ -70,6 +70,7 @@ SQL error codes provide by standard `SQL State`, `Vendor
Code` and `Reason`, whi
| 12101 | 42000 | Can not accept SQL type '%s'. |
| 12200 | 42000 | Hint data source '%s' does not exist. |
| 12300 | 0A000 | DROP TABLE ... CASCADE is not supported. |
+| 12500 | 42000 | Not unique table/alias: '%s' |
### Connection
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/combine/CombineSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/combine/CombineSegmentBinder.java
index 73c70fd7fd6..8074355d8d6 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/combine/CombineSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/combine/CombineSegmentBinder.java
@@ -54,7 +54,9 @@ public final class CombineSegmentBinder {
SQLStatementBinderContext subqueryBinderContext =
new SQLStatementBinderContext(binderContext.getMetaData(),
binderContext.getCurrentDatabaseName(), binderContext.getHintValueContext(),
segment.getSelect());
subqueryBinderContext.getExternalTableBinderContexts().putAll(binderContext.getExternalTableBinderContexts());
+
subqueryBinderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(binderContext.getCommonTableExpressionsSegmentsUniqueAliases());
result.setSelect(new
SelectStatementBinder(outerTableBinderContexts).bind(segment.getSelect(),
subqueryBinderContext));
+
binderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(subqueryBinderContext.getCommonTableExpressionsSegmentsUniqueAliases());
return result;
}
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/SubquerySegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/SubquerySegmentBinder.java
index e12eafd2e21..4e468206f33 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/SubquerySegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/SubquerySegmentBinder.java
@@ -46,7 +46,9 @@ public final class SubquerySegmentBinder {
SQLStatementBinderContext selectBinderContext =
new SQLStatementBinderContext(binderContext.getMetaData(),
binderContext.getCurrentDatabaseName(), binderContext.getHintValueContext(),
segment.getSelect());
selectBinderContext.getExternalTableBinderContexts().putAll(binderContext.getExternalTableBinderContexts());
+
selectBinderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(binderContext.getCommonTableExpressionsSegmentsUniqueAliases());
SelectStatement boundSelectStatement = new
SelectStatementBinder(outerTableBinderContexts).bind(segment.getSelect(),
selectBinderContext);
+
binderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(selectBinderContext.getCommonTableExpressionsSegmentsUniqueAliases());
return new SubquerySegment(segment.getStartIndex(),
segment.getStopIndex(), boundSelectStatement, segment.getText());
}
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
index ebe77511e21..20d5a615b22 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
@@ -172,6 +172,9 @@ public final class SimpleTableSegmentBinder {
if (binderContext.getExternalTableBinderContexts().containsKey(new
CaseInsensitiveString(tableName))) {
return;
}
+ if
(binderContext.getCommonTableExpressionsSegmentsUniqueAliases().contains(tableName))
{
+ return;
+ }
ShardingSpherePreconditions.checkState(schema.containsTable(tableName), () ->
new TableNotFoundException(tableName));
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
index e4c94bd49e4..c9b646cec11 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
@@ -54,7 +54,9 @@ public final class SubqueryTableSegmentBinder {
SQLStatementBinderContext subqueryBinderContext =
new SQLStatementBinderContext(binderContext.getMetaData(),
binderContext.getCurrentDatabaseName(), binderContext.getHintValueContext(),
segment.getSubquery().getSelect());
subqueryBinderContext.getExternalTableBinderContexts().putAll(binderContext.getExternalTableBinderContexts());
+
subqueryBinderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(binderContext.getCommonTableExpressionsSegmentsUniqueAliases());
SelectStatement boundSubSelect = new
SelectStatementBinder(outerTableBinderContexts).bind(segment.getSubquery().getSelect(),
subqueryBinderContext);
+
binderContext.getCommonTableExpressionsSegmentsUniqueAliases().addAll(subqueryBinderContext.getCommonTableExpressionsSegmentsUniqueAliases());
SubquerySegment boundSubquerySegment = new
SubquerySegment(segment.getSubquery().getStartIndex(),
segment.getSubquery().getStopIndex(), boundSubSelect,
segment.getSubquery().getText());
IdentifierValue subqueryTableName =
segment.getAliasSegment().map(AliasSegment::getIdentifier).orElseGet(() -> new
IdentifierValue(""));
SubqueryTableSegment result = new
SubqueryTableSegment(segment.getStartIndex(), segment.getStopIndex(),
boundSubquerySegment);
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/with/CommonTableExpressionSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/with/CommonTableExpressionSegmentBinder.java
index 6f5d3cbe651..ed15495ddf8 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/with/CommonTableExpressionSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/with/CommonTableExpressionSegmentBinder.java
@@ -24,6 +24,8 @@ import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context.type.SimpleTableSegmentBinderContext;
import
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SubqueryTableSegmentBinder;
import
org.apache.shardingsphere.infra.binder.engine.statement.SQLStatementBinderContext;
+import
org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions;
+import
org.apache.shardingsphere.infra.exception.kernel.syntax.DuplicateCommonTableExpressionAliasException;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.complex.CommonTableExpressionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SubqueryTableSegment;
@@ -45,6 +47,11 @@ public final class CommonTableExpressionSegmentBinder {
* @return bound common table expression segment
*/
public static CommonTableExpressionSegment bind(final
CommonTableExpressionSegment segment, final SQLStatementBinderContext
binderContext, final boolean recursive) {
+ if (segment.getAliasName().isPresent()) {
+
ShardingSpherePreconditions.checkState(!binderContext.getCommonTableExpressionsSegmentsUniqueAliases().contains(segment.getAliasName().get()),
+ () -> new
DuplicateCommonTableExpressionAliasException(segment.getAliasName().get()));
+
binderContext.getCommonTableExpressionsSegmentsUniqueAliases().add(segment.getAliasName().get());
+ }
if (recursive && segment.getAliasName().isPresent()) {
binderContext.getExternalTableBinderContexts().put(new
CaseInsensitiveString(segment.getAliasName().get()),
new
SimpleTableSegmentBinderContext(segment.getColumns().stream().map(ColumnProjectionSegment::new).collect(Collectors.toList())));
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/SQLStatementBinderContext.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/SQLStatementBinderContext.java
index d21f286a08c..a289e20bbcb 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/SQLStatementBinderContext.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/SQLStatementBinderContext.java
@@ -47,6 +47,8 @@ public final class SQLStatementBinderContext {
private final SQLStatement sqlStatement;
+ private final Collection<String>
commonTableExpressionsSegmentsUniqueAliases = new CaseInsensitiveSet<>();
+
private final Collection<String> usingColumnNames = new
CaseInsensitiveSet<>();
private final Collection<ProjectionSegment> joinTableProjectionSegments =
new LinkedList<>();
diff --git
a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/with/WithSegmentBinderTest.java
b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/with/WithSegmentBinderTest.java
new file mode 100644
index 00000000000..808c42c7bb6
--- /dev/null
+++
b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/with/WithSegmentBinderTest.java
@@ -0,0 +1,120 @@
+/*
+ * 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.infra.binder.with;
+
+import com.cedarsoftware.util.CaseInsensitiveMap.CaseInsensitiveString;
+import
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context.type.SimpleTableSegmentBinderContext;
+import
org.apache.shardingsphere.infra.binder.engine.segment.dml.with.WithSegmentBinder;
+import
org.apache.shardingsphere.infra.binder.engine.statement.SQLStatementBinderContext;
+import org.apache.shardingsphere.infra.hint.HintValueContext;
+import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
+import
org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereColumn;
+import
org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereSchema;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.complex.CommonTableExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.subquery.SubquerySegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionsSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ShorthandProjectionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.WithSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SimpleTableSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.TableNameSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
+import
org.apache.shardingsphere.sql.parser.statement.mysql.dml.MySQLSelectStatement;
+import org.junit.jupiter.api.Test;
+
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.LinkedList;
+
+import static org.hamcrest.CoreMatchers.is;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
+
+public class WithSegmentBinderTest {
+
+ @Test
+ void assertBind() {
+
+ MySQLSelectStatement mySQLSelectStatement = new MySQLSelectStatement();
+ ProjectionsSegment projectionSegment = new ProjectionsSegment(42, 48);
+ ColumnSegment columnSegment = new ColumnSegment(42, 48, new
IdentifierValue("user_id"));
+ projectionSegment.getProjections().add(new
ColumnProjectionSegment(columnSegment));
+ mySQLSelectStatement.setProjections(projectionSegment);
+ mySQLSelectStatement.setFrom(new SimpleTableSegment(new
TableNameSegment(55, 57, new IdentifierValue("cte"))));
+
+ MySQLSelectStatement subqueryMySQLSelectStatement = new
MySQLSelectStatement();
+ ProjectionsSegment subqueryProjectionSegment = new
ProjectionsSegment(20, 20);
+ ShorthandProjectionSegment shorthandProjectionSegment = new
ShorthandProjectionSegment(20, 20);
+
subqueryProjectionSegment.getProjections().add(shorthandProjectionSegment);
+ subqueryMySQLSelectStatement.setProjections(subqueryProjectionSegment);
+ subqueryMySQLSelectStatement.setFrom(new SimpleTableSegment(new
TableNameSegment(27, 32, new IdentifierValue("t_user"))));
+ SubquerySegment subquerySegment = new SubquerySegment(5, 33,
subqueryMySQLSelectStatement, "(SELECT * FROM t_user)");
+ Collection<CommonTableExpressionSegment> commonTableExpressionSegments
= new LinkedList<>();
+ commonTableExpressionSegments.add(new CommonTableExpressionSegment(5,
33, new AliasSegment(5, 7, new IdentifierValue("cte")), subquerySegment));
+ WithSegment withSegment = new WithSegment(0, 33,
commonTableExpressionSegments);
+ mySQLSelectStatement.setWithSegment(withSegment);
+
+ SQLStatementBinderContext binderContext = new
SQLStatementBinderContext(createMetaData(), "foo_db", new HintValueContext(),
mySQLSelectStatement);
+ WithSegment actual = WithSegmentBinder.bind(withSegment,
binderContext, binderContext.getExternalTableBinderContexts());
+
+ assertThat(binderContext.getExternalTableBinderContexts().size(),
is(1));
+
assertThat(binderContext.getCommonTableExpressionsSegmentsUniqueAliases().size(),
is(1));
+ assertThat(actual.getStartIndex(), is(0));
+
assertTrue(binderContext.getExternalTableBinderContexts().containsKey(new
CaseInsensitiveString("cte")));
+
assertTrue(actual.getCommonTableExpressions().iterator().next().getAliasName().isPresent());
+
assertThat(actual.getCommonTableExpressions().iterator().next().getAliasName().get(),
is("cte"));
+
assertThat(binderContext.getCommonTableExpressionsSegmentsUniqueAliases().iterator().next(),
is("cte"));
+
+ SimpleTableSegmentBinderContext simpleTableSegment =
(SimpleTableSegmentBinderContext)
binderContext.getExternalTableBinderContexts().get(new
CaseInsensitiveString("cte")).iterator().next();
+ ArrayList<ColumnProjectionSegment> columnProjectionSegments = new
ArrayList<>();
+ simpleTableSegment.getProjectionSegments().forEach(each ->
columnProjectionSegments.add((ColumnProjectionSegment) each));
+
+
assertThat(columnProjectionSegments.get(0).getColumn().getIdentifier().getValue(),
is("user_id"));
+
assertThat(columnProjectionSegments.get(1).getColumn().getIdentifier().getValue(),
is("name"));
+
assertTrue(columnProjectionSegments.get(1).getColumn().getOwner().isPresent());
+
assertThat(columnProjectionSegments.get(1).getColumn().getOwner().get().getIdentifier().getValue(),
is("t_user"));
+
assertTrue(columnProjectionSegments.get(0).getColumn().getOwner().isPresent());
+
assertThat(columnProjectionSegments.get(0).getColumn().getOwner().get().getIdentifier().getValue(),
is("t_user"));
+
assertThat(columnProjectionSegments.get(0).getColumn().getIdentifier().getValue(),
is("user_id"));
+
assertThat(columnProjectionSegments.get(1).getColumn().getColumnBoundInfo().getOriginalSchema().getValue(),
is("foo_db"));
+
assertThat(columnProjectionSegments.get(0).getColumn().getColumnBoundInfo().getOriginalSchema().getValue(),
is("foo_db"));
+
assertThat(actual.getCommonTableExpressions().iterator().next().getSubquery().getText(),
is("(SELECT * FROM t_user)"));
+ }
+
+ private ShardingSphereMetaData createMetaData() {
+ ShardingSphereSchema schema = mock(ShardingSphereSchema.class,
RETURNS_DEEP_STUBS);
+
when(schema.getTable("t_user").getAllColumns()).thenReturn(Arrays.asList(
+ new ShardingSphereColumn("user_id", Types.INTEGER, true,
false, false, true, false, false),
+ new ShardingSphereColumn("name", Types.VARCHAR, false, false,
false, true, false, false)));
+
+ ShardingSphereMetaData result = mock(ShardingSphereMetaData.class,
RETURNS_DEEP_STUBS);
+
when(result.getDatabase("foo_db").getSchema("foo_db")).thenReturn(schema);
+ when(result.containsDatabase("foo_db")).thenReturn(true);
+
when(result.getDatabase("foo_db").containsSchema("foo_db")).thenReturn(true);
+
when(result.getDatabase("foo_db").getSchema("foo_db").containsTable("t_user")).thenReturn(true);
+ return result;
+ }
+
+}
diff --git
a/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/syntax/DuplicateCommonTableExpressionAliasException.java
b/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/syntax/DuplicateCommonTableExpressionAliasException.java
new file mode 100644
index 00000000000..d74481b264e
--- /dev/null
+++
b/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/syntax/DuplicateCommonTableExpressionAliasException.java
@@ -0,0 +1,33 @@
+/*
+ * 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.infra.exception.kernel.syntax;
+
+import
org.apache.shardingsphere.infra.exception.core.external.sql.sqlstate.XOpenSQLState;
+import
org.apache.shardingsphere.infra.exception.core.external.sql.type.kernel.category.SyntaxSQLException;
+
+/**
+ * Duplicate common table expression alias exception.
+ */
+public final class DuplicateCommonTableExpressionAliasException extends
SyntaxSQLException {
+
+ private static final long serialVersionUID = -8206891094419297634L;
+
+ public DuplicateCommonTableExpressionAliasException(final String alias) {
+ super(XOpenSQLState.SYNTAX_ERROR, 500, "Not unique table/alias: '%s'",
alias);
+ }
+}
diff --git a/test/it/binder/src/test/resources/sqls/dml/select.xml
b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
similarity index 55%
copy from test/it/binder/src/test/resources/sqls/dml/select.xml
copy to
test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
index ced2c64e8b2..abcb45b553d 100644
--- a/test/it/binder/src/test/resources/sqls/dml/select.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
@@ -1,4 +1,3 @@
-<?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
@@ -16,9 +15,8 @@
~ limitations under the License.
-->
-<sql-cases>
- <sql-case id="select_with_shorthand_projection" value="SELECT * FROM
t_order o" db-types="MySQL"/>
- <sql-case id="select_with_group_by_order_by" value="SELECT order_id,
COUNT(1) count FROM t_order o GROUP BY order_id HAVING count > 1 ORDER BY
order_id" db-types="MySQL"/>
- <sql-case id="select_with_with_clause" value="WITH t_order_tmp AS (SELECT
* FROM t_order o) SELECT * FROM t_order_tmp" db-types="MySQL"/>
- <sql-case id="select_with_current_select_projection_reference"
value="SELECT order_id AS orderId, (SELECT orderId) AS tempOrderId FROM
t_order" db-types="MySQL"/>
-</sql-cases>
+<e2e-test-cases>
+ <test-case sql="WITH products AS (SELECT * FROM t_product) SELECT * FROM
products" db-types="MySQL" scenario-types="db">
+ <assertion />
+ </test-case>
+</e2e-test-cases>
\ No newline at end of file
diff --git a/test/it/binder/src/test/resources/cases/dml/select.xml
b/test/it/binder/src/test/resources/cases/dml/select.xml
index da40c0135de..9d800befa4f 100644
--- a/test/it/binder/src/test/resources/cases/dml/select.xml
+++ b/test/it/binder/src/test/resources/cases/dml/select.xml
@@ -294,6 +294,190 @@
</from>
</select>
+ <select sql-case-id="select_with_clause_with_multiple_cte_definitions">
+ <with start-index="0" stop-index="141">
+ <common-table-expression name="cte1" start-index="5"
stop-index="68">
+ <subquery-expression start-index="5" stop-index="68">
+ <select>
+ <from>
+ <simple-table name="t_order" start-index="59"
stop-index="67" alias="a">
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </from>
+ <projections start-index="38" stop-index="52">
+ <column-projection name="status" start-index="38"
stop-index="43" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="status"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ <column-projection name="user_id" start-index="46"
stop-index="52" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ </projections>
+ </select>
+ </subquery-expression>
+ <column name="status" start-index="10" stop-index="15" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="status" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column>
+ <column name="user_id" start-index="18" stop-index="24" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="user_id" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column>
+ </common-table-expression>
+ <common-table-expression name="cte2" start-index="71"
stop-index="141">
+ <subquery-expression start-index="71" stop-index="141">
+ <select>
+ <from>
+ <simple-table name="t_order_item"
start-index="127" stop-index="140" alias="b">
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </from>
+ <projections start-index="105" stop-index="120">
+ <column-projection name="user_id"
start-index="105" stop-index="111" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ <column-projection name="item_id"
start-index="114" stop-index="120" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="item_id"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ </projections>
+ </select>
+ </subquery-expression>
+ <column name="user_id" start-index="76" stop-index="82" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="user_id" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column>
+ <column name="item_id" start-index="85" stop-index="91" >
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="item_id" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column>
+ </common-table-expression>
+ </with>
+ <from start-index="180" stop-index="230">
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="cte1" start-index="180"
stop-index="183" >
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </left>
+ <right>
+ <simple-table name="cte2" start-index="196"
stop-index="199" >
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="204"
stop-index="230">
+ <left>
+ <column name="user_id" start-index="204"
stop-index="215">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ <owner name="cte1" start-index="204"
stop-index="207" >
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </owner>
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="user_id" start-index="219"
stop-index="230">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ </column-bound>
+ <owner name="cte2" start-index="219"
stop-index="222" >
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </owner>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ <projections start-index="150" stop-index="173">
+ <column-projection name="status" start-index="150"
stop-index="155">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order" />
+ <original-column name="status" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ <column-projection name="user_id" start-index="158"
stop-index="164">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="user_id" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ <column-projection name="item_id" start-index="167"
stop-index="173">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_order_item" />
+ <original-column name="item_id" start-delimiter="`"
end-delimiter="`" />
+ </column-bound>
+ </column-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_with_current_select_projection_reference">
<projections start-index="7" stop-index="58">
<column-projection name="order_id" start-index="7" stop-index="25"
alias="orderId">
diff --git a/test/it/binder/src/test/resources/sqls/dml/select.xml
b/test/it/binder/src/test/resources/sqls/dml/select.xml
index ced2c64e8b2..c7e09f622ae 100644
--- a/test/it/binder/src/test/resources/sqls/dml/select.xml
+++ b/test/it/binder/src/test/resources/sqls/dml/select.xml
@@ -20,5 +20,6 @@
<sql-case id="select_with_shorthand_projection" value="SELECT * FROM
t_order o" db-types="MySQL"/>
<sql-case id="select_with_group_by_order_by" value="SELECT order_id,
COUNT(1) count FROM t_order o GROUP BY order_id HAVING count > 1 ORDER BY
order_id" db-types="MySQL"/>
<sql-case id="select_with_with_clause" value="WITH t_order_tmp AS (SELECT
* FROM t_order o) SELECT * FROM t_order_tmp" db-types="MySQL"/>
+ <sql-case id="select_with_clause_with_multiple_cte_definitions"
value="WITH cte1(status, user_id) AS (SELECT status, user_id FROM t_order a),
cte2(user_id, item_id) AS (SELECT user_id, item_id FROM t_order_item b) SELECT
status, user_id, item_id FROM cte1 INNER JOIN cte2 ON cte1.user_id =
cte2.user_id" db-types="MySQL" />
<sql-case id="select_with_current_select_projection_reference"
value="SELECT order_id AS orderId, (SELECT orderId) AS tempOrderId FROM
t_order" db-types="MySQL"/>
</sql-cases>
