This is an automated email from the ASF dual-hosted git repository.
chengzhang 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 2cfd34e0d31 Support mysql subquery table projection alias visit to sql
statement (#38320)
2cfd34e0d31 is described below
commit 2cfd34e0d3166394790c2ae1f4e553eb5339e9d6
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Tue Mar 3 18:52:06 2026 +0800
Support mysql subquery table projection alias visit to sql statement
(#38320)
* Support mysql subquery table projection alias visit to sql statement
* Fix sql rewrite it
---
RELEASE-NOTES.md | 5 +-
.../rewrite/token/EncryptTokenGenerateBuilder.java | 2 +-
.../EncryptProjectionTokenGenerator.java | 11 +-
.../dml/from/type/SubqueryTableSegmentBinder.java | 70 ++++++++++
.../from/type/SubqueryTableSegmentBinderTest.java | 53 +++++++-
.../pojo/generic/SubstitutableColumnNameToken.java | 6 +-
.../generic/SubstitutableColumnNameTokenTest.java | 2 +-
.../visitor/statement/MySQLStatementVisitor.java | 4 +
.../generic/table/SubqueryTableSegment.java | 5 +
.../binder/src/test/resources/cases/dml/select.xml | 150 +++++++++++++++++++++
.../binder/src/test/resources/sqls/dml/select.xml | 1 +
.../asserts/segment/table/TableAssert.java | 11 +-
.../segment/impl/table/ExpectedSubqueryTable.java | 6 +
.../main/resources/case/dml/select-sub-query.xml | 72 ++++++++++
.../sql/supported/dml/select-sub-query.xml | 1 +
.../dml/select/select-subquery.xml | 14 +-
.../dml/select/select-subquery.xml | 8 +-
17 files changed, 398 insertions(+), 23 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 7c9ca3c75d7..8da0efacb4a 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -8,8 +8,9 @@
### Enhancements
1. SQL Parser: Support MySQL Function statement parse -
[#38182](https://github.com/apache/shardingsphere/pull/38182)
[#38219](https://github.com/apache/shardingsphere/pull/38219)
-2. SQL Parser: Support parsing MySQL SELECT sql -
[#38233](https://github.com/apache/shardingsphere/pull/38233)
-3. SQL Parser: Support parsing MySQL CREATE sql -
[#38237](https://github.com/apache/shardingsphere/pull/38237)
+1. SQL Parser: Support parsing MySQL SELECT sql -
[#38233](https://github.com/apache/shardingsphere/pull/38233)
+1. SQL Parser: Support parsing MySQL CREATE sql -
[#38237](https://github.com/apache/shardingsphere/pull/38237)
+1. SQL Parser: Support mysql subquery table projection alias visit to sql
statement - [#38320](https://github.com/apache/shardingsphere/pull/38320)
## Release 5.5.3
diff --git
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/EncryptTokenGenerateBuilder.java
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/EncryptTokenGenerateBuilder.java
index 4c054052a96..f06a46f786a 100644
---
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/EncryptTokenGenerateBuilder.java
+++
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/EncryptTokenGenerateBuilder.java
@@ -63,9 +63,9 @@ public final class EncryptTokenGenerateBuilder implements
SQLTokenGeneratorBuild
@Override
public Collection<SQLTokenGenerator> getSQLTokenGenerators() {
Collection<SQLTokenGenerator> result = new LinkedList<>();
- ShardingSphereDatabase database = sqlRewriteContext.getDatabase();
addSQLTokenGenerator(result, new
EncryptSelectProjectionTokenGenerator(rule));
addSQLTokenGenerator(result, new
EncryptInsertSelectProjectionTokenGenerator(rule));
+ ShardingSphereDatabase database = sqlRewriteContext.getDatabase();
addSQLTokenGenerator(result, new
EncryptInsertAssignmentTokenGenerator(rule, database));
addSQLTokenGenerator(result, new
EncryptUpdateAssignmentTokenGenerator(rule, database));
addSQLTokenGenerator(result, new
EncryptPredicateColumnTokenGenerator(rule));
diff --git
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/projection/EncryptProjectionTokenGenerator.java
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/projection/EncryptProjectionTokenGenerator.java
index 10a998b551b..961b6646d6d 100644
---
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/projection/EncryptProjectionTokenGenerator.java
+++
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/projection/EncryptProjectionTokenGenerator.java
@@ -118,9 +118,10 @@ public final class EncryptProjectionTokenGenerator {
generateExpressionSQLTokens(betweenExpression.getAndExpr(),
selectStatementContext, processedSubqueryStartIndexes, result, "BETWEEN");
}
}
+ String subqueryOperator = selectStatementContext.isContainsCombine() ?
"COMBINE" : "";
for (Entry<Integer, SelectStatementContext> entry :
selectStatementContext.getSubqueryContexts().entrySet()) {
if (!processedSubqueryStartIndexes.contains(entry.getKey())) {
- result.addAll(generateSQLTokens(entry.getValue(), operator));
+ result.addAll(generateSQLTokens(entry.getValue(),
subqueryOperator));
}
}
return result;
@@ -283,10 +284,14 @@ public final class EncryptProjectionTokenGenerator {
IdentifierValue cipherColumnName = TableSourceType.TEMPORARY_TABLE ==
columnProjection.getColumnBoundInfo().getTableSourceType()
? new
IdentifierValue(EncryptDerivedColumnSuffix.CIPHER.getDerivedColumnName(columnProjection.getName().getValue(),
databaseType),
columnProjection.getName().getQuoteCharacter())
- : new IdentifierValue(encryptColumn.getCipher().getName(),
columnProjection.getName().getQuoteCharacter());
+ : new IdentifierValue(encryptColumn.getCipher().getName(),
dialectDatabaseMetaData.getQuoteCharacter());
IdentifierValue columnAlias =
columnProjection.getAlias().orElse(columnProjection.getName());
IdentifierValue cipherColumnAlias =
getEncryptColumnAliasInTableSegmentSubquery(columnProjection, columnAlias,
EncryptDerivedColumnSuffix.CIPHER);
- result.add(new
ColumnProjection(columnProjection.getOwner().orElse(null), cipherColumnName,
cipherColumnAlias, databaseType));
+ ParenthesesSegment leftParentheses =
columnProjection.getLeftParentheses().orElse(null);
+ ParenthesesSegment rightParentheses =
columnProjection.getRightParentheses().orElse(null);
+ ColumnProjection projection = new
ColumnProjection(columnProjection.getOwner().orElse(null), cipherColumnName,
cipherColumnAlias, databaseType, leftParentheses, rightParentheses,
+ columnProjection.getColumnBoundInfo());
+ result.add(projection);
encryptColumn.getAssistedQuery().ifPresent(optional ->
addAssistedQueryColumn(columnProjection, optional, columnAlias, result));
encryptColumn.getLikeQuery().ifPresent(optional ->
addLikeQueryColumn(columnProjection, optional, columnAlias, result));
return result;
diff --git
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
index db4b5ebff70..c02ca2b89a7 100644
---
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
+++
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinder.java
@@ -22,22 +22,33 @@ import com.google.common.collect.LinkedHashMultimap;
import com.google.common.collect.Multimap;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import
org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.ColumnSegmentBinder;
import
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context.TableSegmentBinderContext;
import
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context.type.SimpleTableSegmentBinderContext;
import
org.apache.shardingsphere.infra.binder.engine.segment.util.SubqueryTableBindUtils;
import
org.apache.shardingsphere.infra.binder.engine.statement.SQLStatementBinderContext;
import
org.apache.shardingsphere.infra.binder.engine.statement.dml.SelectStatementBinder;
+import org.apache.shardingsphere.infra.exception.ShardingSpherePreconditions;
+import
org.apache.shardingsphere.infra.exception.kernel.syntax.DifferenceInColumnCountOfSelectListAndColumnNameListException;
import
org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
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.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.PivotSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.ColumnSegmentBoundInfo;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.TableSegmentBoundInfo;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SubqueryTableSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
+import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
+import java.util.LinkedList;
+import java.util.List;
/**
* Subquery table segment binder.
@@ -73,9 +84,68 @@ public final class SubqueryTableSegmentBinder {
segment.getAliasSegment().ifPresent(result::setAlias);
Collection<ProjectionSegment> subqueryProjections =
SubqueryTableBindUtils.createSubqueryProjections(
boundSubSelect.getProjections().getProjections(),
subqueryTableName, binderContext.getSqlStatement().getDatabaseType(),
TableSourceType.TEMPORARY_TABLE);
+ Collection<ColumnSegment> boundSubqueryColumns =
bindSubqueryColumns(segment.getColumns(), new ArrayList<>(subqueryProjections));
+ result.getColumns().addAll(boundSubqueryColumns);
+ if (!boundSubqueryColumns.isEmpty()) {
+ subqueryProjections =
createBoundSubqueryColumnProjections(boundSubqueryColumns, subqueryTableName);
+ }
SimpleTableSegmentBinderContext tableBinderContext = new
SimpleTableSegmentBinderContext(subqueryProjections,
TableSourceType.TEMPORARY_TABLE);
tableBinderContext.setFromWithSegment(fromWithSegment);
tableBinderContexts.put(CaseInsensitiveString.of(subqueryTableName.getValue()),
tableBinderContext);
return result;
}
+
+ private static Collection<ProjectionSegment>
createBoundSubqueryColumnProjections(final Collection<ColumnSegment> columns,
final IdentifierValue subqueryTableName) {
+ Collection<ProjectionSegment> result = new LinkedList<>();
+ for (ColumnSegment each : columns) {
+ ColumnSegment projectionColumn = copy(each);
+ if (!"".equals(subqueryTableName.getValue())) {
+ projectionColumn.setOwner(new OwnerSegment(0, 0,
subqueryTableName));
+ }
+ result.add(new ColumnProjectionSegment(projectionColumn));
+ }
+ return result;
+ }
+
+ private static Collection<ColumnSegment> bindSubqueryColumns(final
Collection<ColumnSegment> columns, final List<ProjectionSegment>
projectionSegments) {
+ if (columns.isEmpty()) {
+ return Collections.emptyList();
+ }
+ ShardingSpherePreconditions.checkState(columns.size() ==
projectionSegments.size(),
DifferenceInColumnCountOfSelectListAndColumnNameListException::new);
+ int index = 0;
+ Collection<ColumnSegment> result = new LinkedList<>();
+ for (ColumnSegment each : columns) {
+ ColumnSegment boundColumnSegment = copy(each);
+ ProjectionSegment projectionSegment =
projectionSegments.get(index);
+ if (projectionSegment instanceof ColumnProjectionSegment) {
+ ColumnSegment projectionColumn = ((ColumnProjectionSegment)
projectionSegment).getColumn();
+
boundColumnSegment.setColumnBoundInfo(ColumnSegmentBinder.createColumnSegmentBoundInfo(each,
projectionColumn, TableSourceType.TEMPORARY_TABLE));
+ }
+ result.add(boundColumnSegment);
+ index++;
+ }
+ return result;
+ }
+
+ private static ColumnSegment copy(final ColumnSegment segment) {
+ ColumnSegment result = new ColumnSegment(segment.getStartIndex(),
segment.getStopIndex(), segment.getIdentifier());
+ result.setNestedObjectAttributes(segment.getNestedObjectAttributes());
+
result.setColumnBoundInfo(copyColumnSegmentBoundInfo(segment.getColumnBoundInfo()));
+
result.setOtherUsingColumnBoundInfo(copyColumnSegmentBoundInfo(segment.getOtherUsingColumnBoundInfo()));
+ segment.getOwner().ifPresent(result::setOwner);
+ result.setVariable(segment.isVariable());
+ segment.getLeftParentheses().ifPresent(result::setLeftParentheses);
+ segment.getRightParentheses().ifPresent(result::setRightParentheses);
+ return result;
+ }
+
+ private static ColumnSegmentBoundInfo copyColumnSegmentBoundInfo(final
ColumnSegmentBoundInfo columnBoundInfo) {
+ if (null == columnBoundInfo) {
+ return null;
+ }
+ TableSegmentBoundInfo tableBoundInfo = new
TableSegmentBoundInfo(columnBoundInfo.getOriginalDatabase(),
columnBoundInfo.getOriginalSchema());
+ ColumnSegmentBoundInfo result = new
ColumnSegmentBoundInfo(tableBoundInfo, columnBoundInfo.getOriginalTable(),
columnBoundInfo.getOriginalColumn(), columnBoundInfo.getTableSourceType());
+ result.setOwner(columnBoundInfo.getOwner());
+ return result;
+ }
}
diff --git
a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinderTest.java
b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinderTest.java
index 0e87e6da218..a7cc6a91bad 100644
---
a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinderTest.java
+++
b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SubqueryTableSegmentBinderTest.java
@@ -28,6 +28,7 @@ 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.infra.spi.type.typed.TypedSPILoader;
+import
org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
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;
@@ -48,8 +49,8 @@ import java.util.Arrays;
import java.util.List;
import java.util.Optional;
-import static org.hamcrest.Matchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
import static org.hamcrest.Matchers.isA;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
@@ -136,6 +137,56 @@ class SubqueryTableSegmentBinderTest {
assertTrue(tableBinderContexts.containsKey(CaseInsensitiveString.of("")));
}
+ @Test
+ void assertBindWithSubqueryTableColumns() {
+ SelectStatement selectStatement = mock(SelectStatement.class);
+ when(selectStatement.getDatabaseType()).thenReturn(databaseType);
+ when(selectStatement.getFrom()).thenReturn(Optional.of(new
SimpleTableSegment(new TableNameSegment(0, 0, new
IdentifierValue("t_order")))));
+ ProjectionsSegment projectionsSegment = new ProjectionsSegment(0, 0);
+ projectionsSegment.getProjections().add(new
ColumnProjectionSegment(new ColumnSegment(0, 0, new
IdentifierValue("user_id"))));
+ projectionsSegment.getProjections().add(new
ColumnProjectionSegment(new ColumnSegment(0, 0, new
IdentifierValue("status"))));
+ when(selectStatement.getProjections()).thenReturn(projectionsSegment);
+ SubqueryTableSegment subqueryTableSegment = new
SubqueryTableSegment(0, 0, new SubquerySegment(0, 0, selectStatement, ""));
+ subqueryTableSegment.setAlias(new AliasSegment(0, 0, new
IdentifierValue("combined")));
+ subqueryTableSegment.getColumns().add(new ColumnSegment(0, 0, new
IdentifierValue("id")));
+ subqueryTableSegment.getColumns().add(new ColumnSegment(0, 0, new
IdentifierValue("name")));
+ ShardingSphereMetaData metaData = createMetaData();
+ Multimap<CaseInsensitiveString, TableSegmentBinderContext>
tableBinderContexts = LinkedHashMultimap.create();
+ SubqueryTableSegment actual =
SubqueryTableSegmentBinder.bind(subqueryTableSegment,
+ new SQLStatementBinderContext(metaData, "foo_db", new
HintValueContext(), selectStatement),
+ tableBinderContexts, LinkedHashMultimap.create(), false);
+ assertThat(actual.getColumns().size(), is(2));
+ List<ColumnSegment> actualColumns = new
ArrayList<>(actual.getColumns());
+ assertFalse(actualColumns.get(0).getOwner().isPresent());
+
assertThat(actualColumns.get(0).getColumnBoundInfo().getOriginalTable().getValue(),
is("t_order"));
+
assertThat(actualColumns.get(0).getColumnBoundInfo().getOriginalColumn().getValue(),
is("user_id"));
+ assertFalse(actualColumns.get(1).getOwner().isPresent());
+
assertThat(actualColumns.get(1).getColumnBoundInfo().getOriginalTable().getValue(),
is("t_order"));
+
assertThat(actualColumns.get(1).getColumnBoundInfo().getOriginalColumn().getValue(),
is("status"));
+
assertTrue(tableBinderContexts.containsKey(CaseInsensitiveString.of("combined")));
+ TableSegmentBinderContext tableSegmentBinderContext =
tableBinderContexts.get(CaseInsensitiveString.of("combined")).iterator().next();
+ Optional<ProjectionSegment> idProjection =
tableSegmentBinderContext.findProjectionSegmentByColumnLabel("id");
+ assertTrue(idProjection.isPresent());
+ assertThat(idProjection.get(), isA(ColumnProjectionSegment.class));
+ ColumnProjectionSegment actualIdProjection = (ColumnProjectionSegment)
idProjection.get();
+ assertThat(actualIdProjection.getColumn().getIdentifier().getValue(),
is("id"));
+
assertThat(actualIdProjection.getColumn().getColumnBoundInfo().getOriginalDatabase().getValue(),
is("foo_db"));
+
assertThat(actualIdProjection.getColumn().getColumnBoundInfo().getOriginalSchema().getValue(),
is("foo_db"));
+
assertThat(actualIdProjection.getColumn().getColumnBoundInfo().getOriginalTable().getValue(),
is("t_order"));
+
assertThat(actualIdProjection.getColumn().getColumnBoundInfo().getOriginalColumn().getValue(),
is("user_id"));
+
assertThat(actualIdProjection.getColumn().getColumnBoundInfo().getTableSourceType(),
is(TableSourceType.TEMPORARY_TABLE));
+ Optional<ProjectionSegment> nameProjection =
tableSegmentBinderContext.findProjectionSegmentByColumnLabel("name");
+ assertTrue(nameProjection.isPresent());
+ assertThat(nameProjection.get(), isA(ColumnProjectionSegment.class));
+ ColumnProjectionSegment actualNameProjection =
(ColumnProjectionSegment) nameProjection.get();
+
assertThat(actualNameProjection.getColumn().getIdentifier().getValue(),
is("name"));
+
assertThat(actualNameProjection.getColumn().getColumnBoundInfo().getOriginalDatabase().getValue(),
is("foo_db"));
+
assertThat(actualNameProjection.getColumn().getColumnBoundInfo().getOriginalSchema().getValue(),
is("foo_db"));
+
assertThat(actualNameProjection.getColumn().getColumnBoundInfo().getOriginalTable().getValue(),
is("t_order"));
+
assertThat(actualNameProjection.getColumn().getColumnBoundInfo().getOriginalColumn().getValue(),
is("status"));
+
assertThat(actualNameProjection.getColumn().getColumnBoundInfo().getTableSourceType(),
is(TableSourceType.TEMPORARY_TABLE));
+ }
+
private ShardingSphereMetaData createMetaData() {
ShardingSphereSchema schema = mock(ShardingSphereSchema.class,
RETURNS_DEEP_STUBS);
when(schema.getTable("t_order").getAllColumns()).thenReturn(Arrays.asList(
diff --git
a/infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameToken.java
b/infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameToken.java
index ecff4f7980b..417ea3099e3 100644
---
a/infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameToken.java
+++
b/infra/rewrite/core/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameToken.java
@@ -101,6 +101,9 @@ public final class SubstitutableColumnNameToken extends
SQLToken implements Subs
} else {
builder.append(quoteCharacter.wrap(projection.getColumnLabel()));
}
+ if (projection.getAlias().isPresent()) {
+ builder.append(" AS
").append(getValueWithQuoteCharacters(projection.getAlias().get()));
+ }
return builder.toString();
}
@@ -113,9 +116,6 @@ public final class SubstitutableColumnNameToken extends
SQLToken implements Subs
}
builder.append(getValueWithQuoteCharacters(columnProjection.getName()));
columnProjection.getRightParentheses().ifPresent(optional ->
builder.append(")"));
- if (columnProjection.getAlias().isPresent()) {
- builder.append(" AS
").append(getValueWithQuoteCharacters(columnProjection.getAlias().get()));
- }
}
private String getValueWithQuoteCharacters(final IdentifierValue
identifierValue) {
diff --git
a/infra/rewrite/core/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameTokenTest.java
b/infra/rewrite/core/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameTokenTest.java
index a72d674ddf3..9e29d56e153 100644
---
a/infra/rewrite/core/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameTokenTest.java
+++
b/infra/rewrite/core/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/common/pojo/generic/SubstitutableColumnNameTokenTest.java
@@ -69,6 +69,6 @@ class SubstitutableColumnNameTokenTest {
new IdentifierValue("id", QuoteCharacter.BACK_QUOTE), new
IdentifierValue("id", QuoteCharacter.BACK_QUOTE), databaseType),
new SubqueryProjection(new SubqueryProjectionSegment(null,
"(SELECT name FROM t_order)"),
new ColumnProjection(null, "name", null,
databaseType), new IdentifierValue("name"), databaseType));
- assertThat(new SubstitutableColumnNameToken(0, 1, projections,
databaseType).toString(mock(RouteUnit.class)), is("temp.id AS id, name"));
+ assertThat(new SubstitutableColumnNameToken(0, 1, projections,
databaseType).toString(mock(RouteUnit.class)), is("temp.id AS id, name AS
name"));
}
}
diff --git
a/parser/sql/engine/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/engine/mysql/visitor/statement/MySQLStatementVisitor.java
b/parser/sql/engine/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/engine/mysql/visitor/statement/MySQLStatementVisitor.java
index b4fff545c77..89b7f97bef9 100644
---
a/parser/sql/engine/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/engine/mysql/visitor/statement/MySQLStatementVisitor.java
+++
b/parser/sql/engine/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/engine/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -2019,6 +2019,7 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
return result;
}
+ @SuppressWarnings("unchecked")
@Override
public ASTNode visitTableFactor(final TableFactorContext ctx) {
if (null != ctx.subquery()) {
@@ -2028,6 +2029,9 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
if (null != ctx.alias()) {
result.setAlias((AliasSegment) visit(ctx.alias()));
}
+ if (null != ctx.columnNames()) {
+ result.getColumns().addAll(((CollectionValue<ColumnSegment>)
visit(ctx.columnNames())).getValue());
+ }
return result;
}
if (null != ctx.tableName()) {
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/table/SubqueryTableSegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/table/SubqueryTableSegment.java
index 9b795f2bfb4..e15c074ca06 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/table/SubqueryTableSegment.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/table/SubqueryTableSegment.java
@@ -20,11 +20,14 @@ package
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.tabl
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.subquery.SubquerySegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.PivotSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
+import java.util.Collection;
+import java.util.LinkedList;
import java.util.Optional;
/**
@@ -40,6 +43,8 @@ public final class SubqueryTableSegment implements
TableSegment {
private final SubquerySegment subquery;
+ private final Collection<ColumnSegment> columns = new LinkedList<>();
+
@Setter
private AliasSegment alias;
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 a770e1414a3..4b1c32366e2 100644
--- a/test/it/binder/src/test/resources/cases/dml/select.xml
+++ b/test/it/binder/src/test/resources/cases/dml/select.xml
@@ -709,6 +709,156 @@
</from>
</select>
+ <select
sql-case-id="select_with_subquery_table_alias_columns_and_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7">
+ <actual-projections>
+ <column-projection name="id" start-index="165"
stop-index="166">
+ <owner name="combined" start-index="0" stop-index="0"
/>
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ <table-source-type name="TEMPORARY_TABLE"/>
+ </column-bound>
+ </column-projection>
+ <column-projection name="name" start-index="169"
stop-index="172">
+ <owner name="combined" start-index="0" stop-index="0"
/>
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_name"
start-delimiter="`" end-delimiter="`" />
+ <table-source-type name="TEMPORARY_TABLE"/>
+ </column-bound>
+ </column-projection>
+ </actual-projections>
+ </shorthand-projection>
+ </projections>
+ <from>
+ <subquery-table alias="combined" start-index="14" stop-index="163">
+ <column name="id" start-index="165" stop-index="166">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_id" start-delimiter="`"
end-delimiter="`" />
+ <table-source-type name="TEMPORARY_TABLE"/>
+ </column-bound>
+ </column>
+ <column name="name" start-index="169" stop-index="172">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_name" start-delimiter="`"
end-delimiter="`" />
+ <table-source-type name="TEMPORARY_TABLE"/>
+ </column-bound>
+ </column>
+ <subquery>
+ <select>
+ <projections start-index="22" stop-index="39">
+ <column-projection name="user_id" start-index="22"
stop-index="28">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_id"
start-delimiter="`" end-delimiter="`" />
+ <table-source-type name="PHYSICAL_TABLE"/>
+ </column-bound>
+ </column-projection>
+ <column-projection name="user_name"
start-index="31" stop-index="39">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_name"
start-delimiter="`" end-delimiter="`" />
+ <table-source-type name="PHYSICAL_TABLE"/>
+ </column-bound>
+ </column-projection>
+ </projections>
+ <from>
+ <simple-table name="t_user" start-index="46"
stop-index="51">
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </from>
+ <combine combine-type="UNION" start-index="15"
stop-index="150">
+ <left>
+ <projections start-index="22" stop-index="39">
+ <column-projection name="user_id"
start-index="22" stop-index="28" />
+ <column-projection name="user_name"
start-index="31" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="t_user"
start-index="46" stop-index="51">
+ <table-bound>
+ <original-database name="foo_db_1"
/>
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </from>
+ <where start-index="53" stop-index="71">
+ <expr>
+ <binary-operation-expression
start-index="59" stop-index="71">
+ <left>
+ <column name="user_id"
start-index="59" stop-index="65" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="10"
start-index="70" stop-index="71" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </left>
+ <right>
+ <projections start-index="86" stop-index="111">
+ <column-projection name="merchant_id"
start-index="86" stop-index="96" />
+ <column-projection name="merchant_name"
start-index="99" stop-index="111" />
+ </projections>
+ <from>
+ <simple-table name="t_merchant"
start-index="118" stop-index="127">
+ <table-bound>
+ <original-database name="foo_db_1"
/>
+ <original-schema name="foo_db_1" />
+ </table-bound>
+ </simple-table>
+ </from>
+ <where start-index="129" stop-index="150">
+ <expr>
+ <binary-operation-expression
start-index="135" stop-index="150">
+ <left>
+ <column name="merchant_id"
start-index="135" stop-index="145" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="5"
start-index="150" stop-index="150" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </right>
+ </combine>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ <order-by>
+ <column-item name="id" start-index="184" stop-index="185">
+ <column-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="foo_db_1" />
+ <original-table name="t_user" />
+ <original-column name="user_id" start-delimiter="`"
end-delimiter="`" />
+ <table-source-type name="TEMPORARY_TABLE"/>
+ </column-bound>
+ </column-item>
+ </order-by>
+ </select>
+
<select sql-case-id="select_with_outer_join_operator">
<from start-index="23" stop-index="47">
<join-table join-type="COMMA" start-index="23" stop-index="47">
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 ff5637d1280..8058cd40abd 100644
--- a/test/it/binder/src/test/resources/sqls/dml/select.xml
+++ b/test/it/binder/src/test/resources/sqls/dml/select.xml
@@ -23,5 +23,6 @@
<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_with_clause_with_column_definition" value="WITH
t_order_tmp (col1, col2, col3, col4, col5, col6) AS (SELECT * FROM t_order o)
SELECT col1 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-case id="select_with_subquery_table_alias_columns_and_order_by"
value="SELECT * FROM (SELECT user_id, user_name FROM t_user WHERE user_id <=
10 UNION SELECT merchant_id, merchant_name FROM t_merchant WHERE merchant_id
<= 5) AS combined(id, name) ORDER BY id" db-types="MySQL"/>
<sql-case id="select_with_outer_join_operator" value="SELECT o.order_id
FROM t_order o, t_order_item i WHERE o.order_id = i.order_id(+)"
db-types="Oracle"/>
</sql-cases>
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
index b2ae24f5c58..0e3b43ea74a 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
@@ -20,6 +20,7 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.ta
import com.google.common.base.Strings;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ExpressionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.FunctionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.xml.XmlTableFunctionSegment;
@@ -54,8 +55,8 @@ import java.util.Collection;
import java.util.Iterator;
import java.util.List;
-import static org.hamcrest.Matchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
@@ -186,6 +187,14 @@ public final class TableAssert {
MergeStatementAssert.assertIs(assertContext,
actual.getSubquery().getMerge(), expected.getSubquery().getMergeTestCases());
}
assertThat(assertContext.getText("Table alias assertion error: "),
actual.getAliasName().orElse(null), is(expected.getAlias()));
+ if (!expected.getColumns().isEmpty()) {
+ assertThat(assertContext.getText("Subquery table columns size
assertion error: "), actual.getColumns().size(),
is(expected.getColumns().size()));
+ int count = 0;
+ for (ColumnSegment each : actual.getColumns()) {
+ ColumnAssert.assertIs(assertContext, each,
expected.getColumns().get(count));
+ count++;
+ }
+ }
}
/**
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSubqueryTable.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSubqueryTable.java
index 4325d6e5323..65e475c79a6 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSubqueryTable.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSubqueryTable.java
@@ -20,10 +20,13 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.
import lombok.Getter;
import lombok.Setter;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.AbstractExpectedDelimiterSQLSegment;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.column.ExpectedColumn;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.simple.ExpectedSubquery;
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
+import java.util.LinkedList;
+import java.util.List;
/**
* Expected subquery table.
@@ -37,4 +40,7 @@ public final class ExpectedSubqueryTable extends
AbstractExpectedDelimiterSQLSeg
@XmlElement
private ExpectedSubquery subquery;
+
+ @XmlElement(name = "column")
+ private final List<ExpectedColumn> columns = new LinkedList<>();
}
diff --git a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
index c80468a6426..81dda434c5f 100644
--- a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
@@ -142,6 +142,78 @@
</subquery-table>
</from>
</select>
+
+ <select
sql-case-id="select_sub_query_with_table_alias_columns_and_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <subquery-table alias="combined" start-index="14" stop-index="163">
+ <column name="id" start-index="165" stop-index="166" />
+ <column name="name" start-index="169" stop-index="172" />
+ <subquery>
+ <select>
+ <projections start-index="22" stop-index="39">
+ <column-projection name="user_id" start-index="22"
stop-index="28" />
+ <column-projection name="user_name"
start-index="31" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="t_user" start-index="46"
stop-index="51" />
+ </from>
+ <combine combine-type="UNION" start-index="15"
stop-index="150">
+ <left>
+ <projections start-index="22" stop-index="39">
+ <column-projection name="user_id"
start-index="22" stop-index="28" />
+ <column-projection name="user_name"
start-index="31" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="t_user"
start-index="46" stop-index="51" />
+ </from>
+ <where start-index="53" stop-index="71">
+ <expr>
+ <binary-operation-expression
start-index="59" stop-index="71">
+ <left>
+ <column name="user_id"
start-index="59" stop-index="65" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="10"
start-index="70" stop-index="71" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </left>
+ <right>
+ <projections start-index="86" stop-index="111">
+ <column-projection name="merchant_id"
start-index="86" stop-index="96" />
+ <column-projection name="merchant_name"
start-index="99" stop-index="111" />
+ </projections>
+ <from>
+ <simple-table name="t_merchant"
start-index="118" stop-index="127" />
+ </from>
+ <where start-index="129" stop-index="150">
+ <expr>
+ <binary-operation-expression
start-index="135" stop-index="150">
+ <left>
+ <column name="merchant_id"
start-index="135" stop-index="145" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="5"
start-index="150" stop-index="150" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </right>
+ </combine>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ <order-by>
+ <column-item name="id" start-index="184" stop-index="185" />
+ </order-by>
+ </select>
<select sql-case-id="select_with_equal_subquery">
<from>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
index 81c96a2dbdf..9039b220696 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -20,6 +20,7 @@
<sql-case id="select_with_lateral" value="SELECT 1 FROM t1, LATERAL
(SELECT DISTINCT t1.x) AS dt1, LATERAL (SELECT DISTINCT dt1.x) AS dt2 WHERE
dt1.x = dt2.x" db-types="MySQL" />
<sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL,openGauss,
SQLServer,Firebird" />
<sql-case id="select_sub_query_with_table" value="SELECT t.* FROM (SELECT
* FROM t_order WHERE order_id IN (?, ?)) t" />
+ <sql-case id="select_sub_query_with_table_alias_columns_and_order_by"
value="SELECT * FROM (SELECT user_id, user_name FROM t_user WHERE user_id <=
10 UNION SELECT merchant_id, merchant_name FROM t_merchant WHERE merchant_id
<= 5) AS combined(id, name) ORDER BY id;" db-types="MySQL, H2" />
<sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL,openGauss,Firebird" />
<sql-case id="select_with_any_subquery" value="SELECT * FROM employees
WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30)
ORDER BY employee_id;" db-types="Oracle" />
<sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL,PostgreSQL,openGauss,Doris,Hive,Firebird" />
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-subquery.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-subquery.xml
index 2377c0ab510..9a5e334dead 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-subquery.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-subquery.xml
@@ -24,17 +24,17 @@
<rewrite-assertion id="select_not_nested_subquery_in_table_segment"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, o.certificate_number FROM
(SELECT certificate_number FROM t_account) o, t_account u WHERE
o.certificate_number=u.certificate_number AND u.password=?" parameters="1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
cipher_certificate_number AS certificate_number_C,
assisted_query_certificate_number AS certificate_number_A,
like_query_certificate_number AS certificate_number_L FROM t_account) o,
t_account u WHERE o.certificate_number_A=u.`assisted_query_certificate_number`
AND u.`assisted_query_password`=?" parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
`cipher_certificate_number` AS certificate_number_C,
assisted_query_certificate_number AS certificate_number_A,
like_query_certificate_number AS certificate_number_L FROM t_account) o,
t_account u WHERE o.certificate_number_A=u.`assisted_query_certificate_number`
AND u.`assisted_query_password`=?" parameters="assisted_query_1" />
</rewrite-assertion>
<rewrite-assertion id="select_not_nested_subquery_in_table_segment_refed"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, o.certificate_number FROM
(SELECT certificate_number FROM t_account_bak) o, t_account u WHERE
o.certificate_number=u.certificate_number AND u.password=?" parameters="1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
cipher_certificate_number AS certificate_number_C,
assisted_query_certificate_number AS certificate_number_A,
like_query_certificate_number AS certificate_number_L FROM t_account_bak) o,
t_account u WHERE o.certificate_number_A=u.`assisted_query_certificate_number`
AND u.`assisted_query_password`=?" parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
`cipher_certificate_number` AS certificate_number_C,
assisted_query_certificate_number AS certificate_number_A,
like_query_certificate_number AS certificate_number_L FROM t_account_bak) o,
t_account u WHERE o.certificate_number_A=u.`assisted_query_certificate_number`
AND u.`assisted_query_password`=?" parameters="assisted_query_1" />
</rewrite-assertion>
<rewrite-assertion id="select_not_nested_subquery_in_table_segment_alias"
db-types="MySQL">
<input sql="SELECT o.certificate_number FROM (SELECT
a.certificate_number FROM t_account a) o" />
- <output sql="SELECT o.certificate_number_C AS certificate_number FROM
(SELECT a.cipher_certificate_number AS certificate_number_C,
a.assisted_query_certificate_number AS certificate_number_A,
a.like_query_certificate_number AS certificate_number_L FROM t_account a) o" />
+ <output sql="SELECT o.certificate_number_C AS certificate_number FROM
(SELECT a.`cipher_certificate_number` AS certificate_number_C,
a.assisted_query_certificate_number AS certificate_number_A,
a.like_query_certificate_number AS certificate_number_L FROM t_account a) o" />
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_table_segment_with_shorthand_project_alias"
db-types="MySQL">
@@ -64,7 +64,7 @@
<rewrite-assertion id="select_not_nested_subquery_in_table_with_alias"
db-types="MySQL">
<input sql="SELECT count(*) as cnt FROM (SELECT ab.certificate_number
FROM t_account ab) X" />
- <output sql="SELECT count(*) as cnt FROM (SELECT
ab.cipher_certificate_number AS certificate_number_C,
ab.assisted_query_certificate_number AS certificate_number_A,
ab.like_query_certificate_number AS certificate_number_L FROM t_account ab) X"
/>
+ <output sql="SELECT count(*) as cnt FROM (SELECT
ab.`cipher_certificate_number` AS certificate_number_C,
ab.assisted_query_certificate_number AS certificate_number_A,
ab.like_query_certificate_number AS certificate_number_L FROM t_account ab) X"
/>
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_predicate_left_and_right_equal_condition"
db-types="MySQL">
@@ -94,7 +94,7 @@
<rewrite-assertion
id="select_not_nested_subquery_in_tablesegment_from_alias" db-types="MySQL">
<input sql="SELECT b.certificate_number, b.amount FROM (SELECT
a.certificate_number as certificate_number, a.amount FROM t_account a WHERE
a.amount = 1373) b" />
- <output sql="SELECT b.certificate_number_C AS certificate_number,
b.amount_C AS amount FROM (SELECT a.cipher_certificate_number AS
certificate_number_C, a.assisted_query_certificate_number AS
certificate_number_A, a.like_query_certificate_number AS certificate_number_L,
a.cipher_amount AS amount_C FROM t_account a WHERE a.`cipher_amount` =
'encrypt_1373') b" />
+ <output sql="SELECT b.certificate_number_C AS certificate_number,
b.amount_C AS amount FROM (SELECT a.`cipher_certificate_number` AS
certificate_number_C, a.assisted_query_certificate_number AS
certificate_number_A, a.like_query_certificate_number AS certificate_number_L,
a.`cipher_amount` AS amount_C FROM t_account a WHERE a.`cipher_amount` =
'encrypt_1373') b" />
</rewrite-assertion>
<rewrite-assertion id="select_with_exists_sub_query" db-types="MySQL">
@@ -104,11 +104,11 @@
<rewrite-assertion id="select_sub_query_with_group_by" db-types="MySQL">
<input sql="SELECT COUNT(1) AS cnt FROM (SELECT a.amount FROM
t_account a GROUP BY a.amount DESC ) AS tmp" />
- <output sql="SELECT COUNT(1) AS cnt FROM (SELECT a.cipher_amount AS
amount_C FROM t_account a GROUP BY a.`cipher_amount` DESC ) AS tmp" />
+ <output sql="SELECT COUNT(1) AS cnt FROM (SELECT a.`cipher_amount` AS
amount_C FROM t_account a GROUP BY a.`cipher_amount` DESC ) AS tmp" />
</rewrite-assertion>
<rewrite-assertion id="select_concat_in_subquery" db-types="MySQL">
<input sql="SELECT COUNT(*) FROM (SELECT * FROM (SELECT password FROM
t_account WHERE password like concat('%', 'zhangsan', '%')) t) TOTAL" />
- <output sql="SELECT COUNT(*) FROM (SELECT t.password_C, t.password_A,
t.password_L FROM (SELECT cipher_password AS password_C,
assisted_query_password AS password_A, like_query_password AS password_L FROM
t_account WHERE `like_query_password` like concat('like_query_%',
'like_query_zhangsan', 'like_query_%')) t) TOTAL" />
+ <output sql="SELECT COUNT(*) FROM (SELECT t.password_C, t.password_A,
t.password_L FROM (SELECT `cipher_password` AS password_C,
assisted_query_password AS password_A, like_query_password AS password_L FROM
t_account WHERE `like_query_password` like concat('like_query_%',
'like_query_zhangsan', 'like_query_%')) t) TOTAL" />
</rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/mix/case/query-with-cipher/dml/select/select-subquery.xml
b/test/it/rewriter/src/test/resources/scenario/mix/case/query-with-cipher/dml/select/select-subquery.xml
index 6abdbf6d71b..4a3facc39c2 100644
---
a/test/it/rewriter/src/test/resources/scenario/mix/case/query-with-cipher/dml/select/select-subquery.xml
+++
b/test/it/rewriter/src/test/resources/scenario/mix/case/query-with-cipher/dml/select/select-subquery.xml
@@ -19,8 +19,8 @@
<rewrite-assertions yaml-rule="scenario/mix/config/query-with-cipher.yaml">
<rewrite-assertion id="select_not_nested_subquery_in_table_alias"
db-types="MySQL">
<input sql="SELECT count(*) as cnt FROM (SELECT ab.password FROM
t_account ab) X" />
- <output sql="SELECT count(*) as cnt FROM (SELECT ab.cipher_password AS
password_C, ab.assisted_query_password AS password_A FROM t_account_0 ab) X" />
- <output sql="SELECT count(*) as cnt FROM (SELECT ab.cipher_password AS
password_C, ab.assisted_query_password AS password_A FROM t_account_1 ab) X" />
+ <output sql="SELECT count(*) as cnt FROM (SELECT ab.`cipher_password`
AS password_C, ab.assisted_query_password AS password_A FROM t_account_0 ab) X"
/>
+ <output sql="SELECT count(*) as cnt FROM (SELECT ab.`cipher_password`
AS password_C, ab.assisted_query_password AS password_A FROM t_account_1 ab) X"
/>
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_table_segment_with_shorthand_project_alias"
db-types="MySQL">
@@ -37,7 +37,7 @@
<rewrite-assertion id="select_not_nested_subquery_in_table_segment_alias"
db-types="MySQL">
<input sql="SELECT o.password FROM (SELECT a.password FROM t_account
a) o" />
- <output sql="SELECT o.password_C AS password FROM (SELECT
a.cipher_password AS password_C, a.assisted_query_password AS password_A FROM
t_account_0 a) o" />
- <output sql="SELECT o.password_C AS password FROM (SELECT
a.cipher_password AS password_C, a.assisted_query_password AS password_A FROM
t_account_1 a) o" />
+ <output sql="SELECT o.password_C AS password FROM (SELECT
a.`cipher_password` AS password_C, a.assisted_query_password AS password_A FROM
t_account_0 a) o" />
+ <output sql="SELECT o.password_C AS password FROM (SELECT
a.`cipher_password` AS password_C, a.assisted_query_password AS password_A FROM
t_account_1 a) o" />
</rewrite-assertion>
</rewrite-assertions>
