This is an automated email from the ASF dual-hosted git repository.
zhaojinchao 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 27638ef7803 Add SubqueryNestedInBinaryOperationEncryptorChecker and
refactor encrypt sql rewrite (#38245)
27638ef7803 is described below
commit 27638ef780347c32387ffa56bfd90639fd93b4f4
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Fri Feb 27 18:57:38 2026 +0800
Add SubqueryNestedInBinaryOperationEncryptorChecker and refactor encrypt
sql rewrite (#38245)
* Add SubqueryNestedInBinaryOperationEncryptorChecker and refactor encrypt
sql rewrite
* Fix sql rewrite it
---
...eryNestedInBinaryOperationEncryptorChecker.java | 133 ++++++++++++++++++
.../encrypt/enums/EncryptDerivedColumnSuffix.java | 6 +-
.../EncryptProjectionTokenGenerator.java | 140 +++++++++++++++----
.../EncryptPredicateColumnTokenGeneratorTest.java | 2 +-
.../dml/expression/type/ColumnSegmentBinder.java | 48 ++++++-
.../dml/from/type/SubqueryTableSegmentBinder.java | 5 +-
.../pojo/generic/SubstitutableColumnNameToken.java | 11 +-
.../parser/statement/core/enums/SubqueryType.java | 2 +-
.../statement/core/extractor/ColumnExtractor.java | 45 +++++++
.../core/extractor/ExpressionExtractor.java | 148 +++++++++++++++++++++
.../core/extractor/SubqueryExtractor.java | 40 +++++-
.../generic/bound/ColumnSegmentBoundInfo.java | 5 +
.../query-with-cipher/dml/insert/insert-select.xml | 4 +-
.../dml/select/select-subquery.xml | 32 ++---
.../dml/select/select-subquery.xml | 16 +--
15 files changed, 571 insertions(+), 66 deletions(-)
diff --git
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/checker/cryptographic/SubqueryNestedInBinaryOperationEncryptorChecker.java
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/checker/cryptographic/SubqueryNestedInBinaryOperationEncryptorChecker.java
new file mode 100644
index 00000000000..1bbf54b91b9
--- /dev/null
+++
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/checker/cryptographic/SubqueryNestedInBinaryOperationEncryptorChecker.java
@@ -0,0 +1,133 @@
+/*
+ * 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.encrypt.checker.cryptographic;
+
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import
org.apache.shardingsphere.encrypt.rewrite.token.comparator.EncryptorComparator;
+import org.apache.shardingsphere.encrypt.rule.EncryptRule;
+import org.apache.shardingsphere.infra.exception.ShardingSpherePreconditions;
+import
org.apache.shardingsphere.infra.exception.generic.UnsupportedSQLOperationException;
+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.RowExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.subquery.SubqueryExpressionSegment;
+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.bound.ColumnSegmentBoundInfo;
+import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+
+/**
+ * Subquery nested in binary operation encryptor checker.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class SubqueryNestedInBinaryOperationEncryptorChecker {
+
+ /**
+ * Check whether binary operations with subquery use same encryptor.
+ *
+ * @param left left expression
+ * @param right right expression
+ * @param encryptRule encrypt rule
+ * @param scenario scenario
+ */
+ public static void checkIsSame(final ExpressionSegment left, final
ExpressionSegment right, final EncryptRule encryptRule, final String scenario) {
+ if (isNotColumnAndSubquery(left) || isNotColumnAndSubquery(right)) {
+ return;
+ }
+ if (left instanceof RowExpression) {
+ checkRowExpressionEncryptor((RowExpression) left, right,
encryptRule, scenario);
+ return;
+ }
+ if (right instanceof RowExpression) {
+ checkRowExpressionEncryptor((RowExpression) right, left,
encryptRule, scenario);
+ return;
+ }
+ ColumnSegmentBoundInfo leftColumnInfo = left instanceof ColumnSegment
? ((ColumnSegment) left).getColumnBoundInfo() :
getSubqueryColumnBoundInfo(left);
+ ColumnSegmentBoundInfo rightColumnInfo = right instanceof
ColumnSegment ? ((ColumnSegment) right).getColumnBoundInfo() :
getSubqueryColumnBoundInfo(right);
+ checkEncryptorIsSame(leftColumnInfo, rightColumnInfo, encryptRule,
scenario);
+ }
+
+ private static void checkRowExpressionEncryptor(final RowExpression
rowExpression, final ExpressionSegment otherExpression, final EncryptRule
encryptRule, final String scenario) {
+ List<ColumnSegmentBoundInfo> rowColumnInfos =
getRowExpressionColumnBoundInfos(rowExpression);
+ List<ColumnSegmentBoundInfo> otherColumnInfos =
getExpressionColumnBoundInfos(otherExpression, rowColumnInfos.size());
+ for (int i = 0; i < rowColumnInfos.size(); i++) {
+ checkEncryptorIsSame(rowColumnInfos.get(i),
otherColumnInfos.get(i), encryptRule, scenario);
+ }
+ }
+
+ private static List<ColumnSegmentBoundInfo>
getRowExpressionColumnBoundInfos(final RowExpression rowExpression) {
+ List<ColumnSegmentBoundInfo> result = new ArrayList<>();
+ for (ExpressionSegment each : rowExpression.getItems()) {
+ if (each instanceof ColumnSegment) {
+ result.add(((ColumnSegment) each).getColumnBoundInfo());
+ }
+ }
+ return result;
+ }
+
+ private static List<ColumnSegmentBoundInfo>
getExpressionColumnBoundInfos(final ExpressionSegment expression, final int
expectedSize) {
+ if (expression instanceof SubqueryExpressionSegment || expression
instanceof SubquerySegment) {
+ return getSubqueryColumnBoundInfos(expression, expectedSize);
+ }
+ throw new UnsupportedSQLOperationException("Row expression can only
compare with subquery");
+ }
+
+ private static List<ColumnSegmentBoundInfo>
getSubqueryColumnBoundInfos(final ExpressionSegment expression, final int
expectedSize) {
+ ShardingSpherePreconditions.checkState(expression instanceof
SubqueryExpressionSegment || expression instanceof SubquerySegment,
+ () -> new UnsupportedSQLOperationException(String.format("only
support subquery expression or subquery segment, but got %s",
expression.getClass().getName())));
+ SubquerySegment subquerySegment = expression instanceof
SubquerySegment ? (SubquerySegment) expression : ((SubqueryExpressionSegment)
expression).getSubquery();
+ Collection<ProjectionSegment> projections =
subquerySegment.getSelect().getProjections().getProjections();
+ ShardingSpherePreconditions.checkState(projections.size() ==
expectedSize,
+ () -> new
UnsupportedSQLOperationException(String.format("Subquery column count %d does
not match row expression column count %d", projections.size(), expectedSize)));
+ List<ColumnSegmentBoundInfo> result = new ArrayList<>();
+ for (ProjectionSegment each : projections) {
+ result.add(each instanceof ColumnProjectionSegment
+ ? ((ColumnProjectionSegment)
each).getColumn().getColumnBoundInfo()
+ : new ColumnSegmentBoundInfo(new
IdentifierValue(each.getColumnLabel())));
+ }
+ return result;
+ }
+
+ private static ColumnSegmentBoundInfo getSubqueryColumnBoundInfo(final
ExpressionSegment expression) {
+ ShardingSpherePreconditions.checkState(expression instanceof
SubqueryExpressionSegment || expression instanceof SubquerySegment,
+ () -> new UnsupportedSQLOperationException(String.format("only
support subquery expression or subquery segment, but got %s",
expression.getClass().getName())));
+ SubquerySegment subquerySegment = expression instanceof
SubquerySegment ? (SubquerySegment) expression : ((SubqueryExpressionSegment)
expression).getSubquery();
+ ProjectionSegment projection =
subquerySegment.getSelect().getProjections().getProjections().iterator().next();
+ return projection instanceof ColumnProjectionSegment
+ ? ((ColumnProjectionSegment)
projection).getColumn().getColumnBoundInfo()
+ : new ColumnSegmentBoundInfo(new
IdentifierValue(projection.getColumnLabel()));
+ }
+
+ private static boolean isNotColumnAndSubquery(final ExpressionSegment
expression) {
+ return !(expression instanceof ColumnSegment) && !(expression
instanceof RowExpression) && !(expression instanceof SubqueryExpressionSegment)
&& !(expression instanceof SubquerySegment);
+ }
+
+ private static void checkEncryptorIsSame(final ColumnSegmentBoundInfo
leftColumnInfo, final ColumnSegmentBoundInfo rightColumnInfo, final EncryptRule
encryptRule, final String scenario) {
+ if (EncryptorComparator.isSame(encryptRule, leftColumnInfo,
rightColumnInfo)) {
+ return;
+ }
+ String reason = "Can not use different encryptor for " +
leftColumnInfo + " and " + rightColumnInfo + " in " + scenario;
+ throw new UnsupportedSQLOperationException(reason);
+ }
+}
diff --git
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/enums/EncryptDerivedColumnSuffix.java
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/enums/EncryptDerivedColumnSuffix.java
index 742f0c4a222..7cf71277de5 100644
---
a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/enums/EncryptDerivedColumnSuffix.java
+++
b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/enums/EncryptDerivedColumnSuffix.java
@@ -28,9 +28,9 @@ import
org.apache.shardingsphere.database.connector.core.type.DatabaseTypeRegist
@RequiredArgsConstructor(access = AccessLevel.PRIVATE)
public enum EncryptDerivedColumnSuffix {
- CIPHER("_CIPHER"),
- ASSISTED_QUERY("_ASSISTED"),
- LIKE_QUERY("_LIKE");
+ CIPHER("_C"),
+ ASSISTED_QUERY("_A"),
+ LIKE_QUERY("_L");
private final String suffix;
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 deedc8ee6bf..10a998b551b 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
@@ -18,6 +18,11 @@
package org.apache.shardingsphere.encrypt.rewrite.token.generator.projection;
import lombok.RequiredArgsConstructor;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.QuoteCharacter;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.metadata.DialectDatabaseMetaData;
+import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
+import
org.apache.shardingsphere.database.connector.core.type.DatabaseTypeRegistry;
+import
org.apache.shardingsphere.encrypt.checker.cryptographic.SubqueryNestedInBinaryOperationEncryptorChecker;
import org.apache.shardingsphere.encrypt.enums.EncryptDerivedColumnSuffix;
import org.apache.shardingsphere.encrypt.rule.EncryptRule;
import org.apache.shardingsphere.encrypt.rule.column.EncryptColumn;
@@ -31,15 +36,20 @@ import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.
import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.impl.ColumnProjection;
import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.impl.ShorthandProjection;
import
org.apache.shardingsphere.infra.binder.context.statement.type.dml.SelectStatementContext;
-import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.QuoteCharacter;
-import
org.apache.shardingsphere.database.connector.core.metadata.database.metadata.DialectDatabaseMetaData;
-import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
-import
org.apache.shardingsphere.database.connector.core.type.DatabaseTypeRegistry;
import
org.apache.shardingsphere.infra.exception.generic.UnsupportedSQLOperationException;
import org.apache.shardingsphere.infra.rewrite.sql.token.common.pojo.SQLToken;
import
org.apache.shardingsphere.infra.rewrite.sql.token.common.pojo.generic.SubstitutableColumnNameToken;
import org.apache.shardingsphere.sql.parser.statement.core.enums.SubqueryType;
import
org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
+import
org.apache.shardingsphere.sql.parser.statement.core.extractor.ExpressionExtractor;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BetweenExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BinaryOperationExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ExistsSubqueryExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.InExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.QuantifySubqueryExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.subquery.SubqueryExpressionSegment;
+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.dml.item.ShorthandProjectionSegment;
@@ -49,8 +59,10 @@ import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.Iden
import java.util.Collection;
import java.util.Collections;
+import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
+import java.util.Map.Entry;
import java.util.Optional;
/**
@@ -82,35 +94,98 @@ public final class EncryptProjectionTokenGenerator {
* @return generated SQL tokens
*/
public Collection<SQLToken> generateSQLTokens(final SelectStatementContext
selectStatementContext) {
- Collection<SQLToken> result = new LinkedList<>();
- selectStatementContext.getSubqueryContexts().values().forEach(each ->
result.addAll(generateSQLTokens(each)));
- result.addAll(generateSelectSQLTokens(selectStatementContext));
+ return generateSQLTokens(selectStatementContext, "");
+ }
+
+ private Collection<SQLToken> generateSQLTokens(final
SelectStatementContext selectStatementContext, final String operator) {
+ Collection<SQLToken> result = new
LinkedList<>(generateSelectSQLTokens(selectStatementContext, operator));
+ Collection<Integer> processedSubqueryStartIndexes = new HashSet<>();
+ for (ExpressionSegment each :
ExpressionExtractor.getNestedSubqueryCompareExpressions(selectStatementContext.getSqlStatement()))
{
+ if (each instanceof BinaryOperationExpression) {
+ BinaryOperationExpression binaryExpression =
(BinaryOperationExpression) each;
+ checkBinaryOperationEncryptor(binaryExpression);
+ generateExpressionSQLTokens(binaryExpression.getLeft(),
selectStatementContext, processedSubqueryStartIndexes, result,
binaryExpression.getOperator());
+ generateExpressionSQLTokens(binaryExpression.getRight(),
selectStatementContext, processedSubqueryStartIndexes, result,
binaryExpression.getOperator());
+ } else if (each instanceof InExpression) {
+ InExpression inExpression = (InExpression) each;
+ checkInExpressionEncryptor(inExpression);
+ generateExpressionSQLTokens(inExpression.getLeft(),
selectStatementContext, processedSubqueryStartIndexes, result, "IN");
+ generateExpressionSQLTokens(inExpression.getRight(),
selectStatementContext, processedSubqueryStartIndexes, result, "IN");
+ } else if (each instanceof BetweenExpression) {
+ BetweenExpression betweenExpression = (BetweenExpression) each;
+ generateExpressionSQLTokens(betweenExpression.getLeft(),
selectStatementContext, processedSubqueryStartIndexes, result, "BETWEEN");
+
generateExpressionSQLTokens(betweenExpression.getBetweenExpr(),
selectStatementContext, processedSubqueryStartIndexes, result, "BETWEEN");
+ generateExpressionSQLTokens(betweenExpression.getAndExpr(),
selectStatementContext, processedSubqueryStartIndexes, result, "BETWEEN");
+ }
+ }
+ for (Entry<Integer, SelectStatementContext> entry :
selectStatementContext.getSubqueryContexts().entrySet()) {
+ if (!processedSubqueryStartIndexes.contains(entry.getKey())) {
+ result.addAll(generateSQLTokens(entry.getValue(), operator));
+ }
+ }
return result;
}
- private Collection<SQLToken> generateSelectSQLTokens(final
SelectStatementContext selectStatementContext) {
+ private void checkBinaryOperationEncryptor(final BinaryOperationExpression
binaryExpression) {
+
SubqueryNestedInBinaryOperationEncryptorChecker.checkIsSame(binaryExpression.getLeft(),
binaryExpression.getRight(), rule, "binary operation with subquery");
+ }
+
+ private void checkInExpressionEncryptor(final InExpression inExpression) {
+
SubqueryNestedInBinaryOperationEncryptorChecker.checkIsSame(inExpression.getLeft(),
inExpression.getRight(), rule, "IN expression with subquery");
+ }
+
+ private void generateExpressionSQLTokens(final ExpressionSegment
expressionSegment, final SelectStatementContext selectStatementContext, final
Collection<Integer> processedSubqueryStartIndexes,
+ final Collection<SQLToken>
result, final String operator) {
+ Integer subqueryStartIndex = getSubqueryStartIndex(expressionSegment);
+ if (null != subqueryStartIndex &&
processedSubqueryStartIndexes.add(subqueryStartIndex)) {
+ SelectStatementContext subqueryContext =
selectStatementContext.getSubqueryContexts().get(subqueryStartIndex);
+ if (null != subqueryContext) {
+ result.addAll(generateSQLTokens(subqueryContext, operator));
+ }
+ }
+ }
+
+ private Integer getSubqueryStartIndex(final ExpressionSegment
expressionSegment) {
+ if (expressionSegment instanceof SubqueryExpressionSegment) {
+ return ((SubqueryExpressionSegment)
expressionSegment).getSubquery().getStartIndex();
+ }
+ if (expressionSegment instanceof SubquerySegment) {
+ return expressionSegment.getStartIndex();
+ }
+ if (expressionSegment instanceof QuantifySubqueryExpression) {
+ return ((QuantifySubqueryExpression)
expressionSegment).getSubquery().getStartIndex();
+ }
+ if (expressionSegment instanceof ExistsSubqueryExpression) {
+ return ((ExistsSubqueryExpression)
expressionSegment).getSubquery().getStartIndex();
+ }
+ return null;
+ }
+
+ private Collection<SQLToken> generateSelectSQLTokens(final
SelectStatementContext selectStatementContext, final String operator) {
Collection<SQLToken> result = new LinkedList<>();
for (ProjectionSegment each :
selectStatementContext.getSqlStatement().getProjections().getProjections()) {
if (each instanceof ColumnProjectionSegment) {
- generateSQLToken(selectStatementContext,
(ColumnProjectionSegment) each).ifPresent(result::add);
+ generateSQLToken(selectStatementContext,
(ColumnProjectionSegment) each, operator).ifPresent(result::add);
} else if (each instanceof ShorthandProjectionSegment) {
ShorthandProjectionSegment shorthandSegment =
(ShorthandProjectionSegment) each;
Collection<Projection> actualColumns =
getShorthandProjection(shorthandSegment,
selectStatementContext.getProjectionsContext()).getActualColumns();
if (!actualColumns.isEmpty()) {
- result.add(generateSQLToken(shorthandSegment,
actualColumns, selectStatementContext.getSqlStatement().getDatabaseType(),
selectStatementContext.getSubqueryType()));
+ result.add(generateSQLToken(shorthandSegment,
actualColumns, selectStatementContext.getSqlStatement().getDatabaseType(),
+ selectStatementContext.getSubqueryType(),
selectStatementContext, operator));
}
}
}
return result;
}
- private Optional<SubstitutableColumnNameToken> generateSQLToken(final
SelectStatementContext selectStatementContext, final ColumnProjectionSegment
columnSegment) {
+ private Optional<SubstitutableColumnNameToken> generateSQLToken(final
SelectStatementContext selectStatementContext, final ColumnProjectionSegment
columnSegment,
+ final
String operator) {
ColumnProjection columnProjection =
buildColumnProjection(columnSegment);
String columnName = columnProjection.getOriginalColumn().getValue();
Optional<EncryptTable> encryptTable =
rule.findEncryptTable(columnProjection.getOriginalTable().getValue());
if (encryptTable.isPresent() &&
encryptTable.get().isEncryptColumn(columnName)) {
EncryptColumn encryptColumn =
encryptTable.get().getEncryptColumn(columnName);
- Collection<Projection> projections =
generateProjections(encryptColumn, columnProjection,
selectStatementContext.getSubqueryType());
+ Collection<Projection> projections =
generateProjections(encryptColumn, columnProjection,
selectStatementContext.getSubqueryType(), operator);
int startIndex = getStartIndex(columnSegment);
int stopIndex = getStopIndex(columnSegment);
previousSQLTokens.removeIf(each -> each.getStartIndex() ==
startIndex);
@@ -120,7 +195,7 @@ public final class EncryptProjectionTokenGenerator {
}
private SubstitutableColumnNameToken generateSQLToken(final
ShorthandProjectionSegment segment, final Collection<Projection> actualColumns,
final DatabaseType databaseType,
- final SubqueryType
subqueryType) {
+ final SubqueryType
subqueryType, final SelectStatementContext selectStatementContext, final String
operator) {
Collection<Projection> projections = new LinkedList<>();
for (Projection each : actualColumns) {
if (each instanceof ColumnProjection) {
@@ -128,7 +203,7 @@ public final class EncryptProjectionTokenGenerator {
Optional<EncryptTable> encryptTable =
rule.findEncryptTable(columnProjection.getOriginalTable().getValue());
if (encryptTable.isPresent() &&
encryptTable.get().isEncryptColumn(columnProjection.getOriginalColumn().getValue()))
{
EncryptColumn encryptColumn =
encryptTable.get().getEncryptColumn(columnProjection.getOriginalColumn().getValue());
- projections.addAll(generateProjections(encryptColumn,
columnProjection, subqueryType));
+ projections.addAll(generateProjections(encryptColumn,
columnProjection, subqueryType, operator));
continue;
}
}
@@ -160,7 +235,7 @@ public final class EncryptProjectionTokenGenerator {
segment.getColumn().getLeftParentheses().orElse(null),
segment.getColumn().getRightParentheses().orElse(null),
segment.getColumn().getColumnBoundInfo());
}
- private Collection<Projection> generateProjections(final EncryptColumn
encryptColumn, final ColumnProjection columnProjection, final SubqueryType
subqueryType) {
+ private Collection<Projection> generateProjections(final EncryptColumn
encryptColumn, final ColumnProjection columnProjection, final SubqueryType
subqueryType, final String subqueryOperator) {
if (null == subqueryType || SubqueryType.PROJECTION == subqueryType) {
return Collections.singleton(generateProjection(encryptColumn,
columnProjection));
}
@@ -168,9 +243,9 @@ public final class EncryptProjectionTokenGenerator {
return generateProjectionsInTableSegmentSubquery(encryptColumn,
columnProjection);
}
if (SubqueryType.PREDICATE == subqueryType) {
- return
Collections.singleton(generateProjectionInPredicateSubquery(encryptColumn,
columnProjection));
+ return
Collections.singleton(generateProjectionInPredicateSubquery(encryptColumn,
columnProjection, subqueryOperator));
}
- if (SubqueryType.INSERT_SELECT == subqueryType ||
SubqueryType.VIEW_DEFINITION == subqueryType) {
+ if (SubqueryType.INSERT_SELECT == subqueryType) {
return generateProjectionsInInsertSelectSubquery(encryptColumn,
columnProjection);
}
throw new UnsupportedSQLOperationException(
@@ -245,29 +320,42 @@ public final class EncryptProjectionTokenGenerator {
columnProjection.getRightParentheses().orElse(null)));
}
- private ColumnProjection generateProjectionInPredicateSubquery(final
EncryptColumn encryptColumn, final ColumnProjection columnProjection) {
+ private Projection generateProjectionInPredicateSubquery(final
EncryptColumn encryptColumn, final ColumnProjection columnProjection, final
String subqueryOperator) {
QuoteCharacter quoteCharacter =
columnProjection.getName().getQuoteCharacter();
ParenthesesSegment leftParentheses =
columnProjection.getLeftParentheses().orElse(null);
ParenthesesSegment rightParentheses =
columnProjection.getRightParentheses().orElse(null);
IdentifierValue owner = columnProjection.getOwner().orElse(null);
- return encryptColumn.getAssistedQuery()
- .map(optional -> new ColumnProjection(owner, new
IdentifierValue(optional.getName(), quoteCharacter), null, databaseType,
leftParentheses, rightParentheses))
- .orElseGet(() -> new ColumnProjection(owner, new
IdentifierValue(encryptColumn.getCipher().getName(), quoteCharacter),
columnProjection.getAlias().orElse(columnProjection.getName()),
- databaseType, leftParentheses, rightParentheses));
+ // SPEX CHANGED: BEGIN
+ Optional<String> derivedColumnName =
getDerivedColumnName(encryptColumn, columnProjection);
+ String columnProjectionName = derivedColumnName.orElseGet(() ->
columnProjection.getName().getValue());
+ if (!derivedColumnName.isPresent()) {
+ columnProjectionName =
encryptColumn.getAssistedQuery().map(AssistedQueryColumnItem::getName).orElse(encryptColumn.getCipher().getName());
+ }
+ return new ColumnProjection(owner, new
IdentifierValue(columnProjectionName, quoteCharacter),
+
columnProjection.getAlias().orElse(columnProjection.getName()), databaseType,
leftParentheses, rightParentheses);
+ }
+
+ private Optional<String> getDerivedColumnName(final EncryptColumn
encryptColumn, final ColumnProjection columnProjection) {
+ if (TableSourceType.TEMPORARY_TABLE ==
columnProjection.getColumnBoundInfo().getTableSourceType()) {
+ EncryptDerivedColumnSuffix derivedColumnSuffix =
encryptColumn.getAssistedQuery().map(optional ->
EncryptDerivedColumnSuffix.ASSISTED_QUERY).orElse(EncryptDerivedColumnSuffix.CIPHER);
+ return
Optional.of(derivedColumnSuffix.getDerivedColumnName(columnProjection.getName().getValue(),
databaseType));
+ }
+ return Optional.empty();
}
private Collection<Projection>
generateProjectionsInInsertSelectSubquery(final EncryptColumn encryptColumn,
final ColumnProjection columnProjection) {
- QuoteCharacter quoteCharacter =
columnProjection.getName().getQuoteCharacter();
- IdentifierValue columnName = new
IdentifierValue(encryptColumn.getCipher().getName(), quoteCharacter);
+ IdentifierValue columnName = new
IdentifierValue(encryptColumn.getCipher().getName(),
columnProjection.getName().getQuoteCharacter());
Collection<Projection> result = new LinkedList<>();
ParenthesesSegment leftParentheses =
columnProjection.getLeftParentheses().orElse(null);
ParenthesesSegment rightParentheses =
columnProjection.getRightParentheses().orElse(null);
result.add(new
ColumnProjection(columnProjection.getOwner().orElse(null), columnName, null,
databaseType, leftParentheses, rightParentheses));
IdentifierValue columOwner = columnProjection.getOwner().orElse(null);
encryptColumn.getAssistedQuery()
- .ifPresent(optional -> result.add(new
ColumnProjection(columOwner, new IdentifierValue(optional.getName(),
quoteCharacter), null, databaseType, leftParentheses, rightParentheses)));
+ .ifPresent(optional -> result.add(
+ new ColumnProjection(columOwner, new
IdentifierValue(optional.getName(),
dialectDatabaseMetaData.getQuoteCharacter()), null, databaseType,
leftParentheses, rightParentheses)));
encryptColumn.getLikeQuery()
- .ifPresent(optional -> result.add(new
ColumnProjection(columOwner, new IdentifierValue(optional.getName(),
quoteCharacter), null, databaseType, leftParentheses, rightParentheses)));
+ .ifPresent(optional -> result.add(
+ new ColumnProjection(columOwner, new
IdentifierValue(optional.getName(),
dialectDatabaseMetaData.getQuoteCharacter()), null, databaseType,
leftParentheses, rightParentheses)));
return result;
}
diff --git
a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/predicate/EncryptPredicateColumnTokenGeneratorTest.java
b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/predicate/EncryptPredicateColumnTokenGeneratorTest.java
index 5cf38f26dd3..96cccf283d3 100644
---
a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/predicate/EncryptPredicateColumnTokenGeneratorTest.java
+++
b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/predicate/EncryptPredicateColumnTokenGeneratorTest.java
@@ -47,6 +47,6 @@ class EncryptPredicateColumnTokenGeneratorTest {
void assertGenerateSQLTokenFromGenerateNewSQLToken() {
Collection<SQLToken> substitutableColumnNameTokens =
generator.generateSQLTokens(EncryptGeneratorFixtureBuilder.createUpdateStatementContext());
assertThat(substitutableColumnNameTokens.size(), is(1));
- assertThat(((SubstitutableColumnNameToken)
substitutableColumnNameTokens.iterator().next()).toString(null),
is("pwd_ASSISTED"));
+ assertThat(((SubstitutableColumnNameToken)
substitutableColumnNameTokens.iterator().next()).toString(null), is("pwd_A"));
}
}
diff --git
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java
index f44b163d477..498a06d7cec 100644
---
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java
+++
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java
@@ -36,8 +36,12 @@ import
org.apache.shardingsphere.infra.exception.kernel.metadata.ColumnNotFoundE
import
org.apache.shardingsphere.infra.exception.kernel.syntax.AmbiguousColumnException;
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.ExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.FunctionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ExpressionProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.SubqueryProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.ColumnSegmentBoundInfo;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.TableSegmentBoundInfo;
@@ -120,7 +124,7 @@ public final class ColumnSegmentBinder {
return getTableBinderContextByOwner(owner, tableBinderContexts,
outerTableBinderContexts, binderContext.getExternalTableBinderContexts());
}
if (!binderContext.getJoinTableProjectionSegments().isEmpty() &&
isNeedUseJoinTableProjectionBind(segment, parentSegmentType, binderContext)) {
- return Collections.singleton(new
SimpleTableSegmentBinderContext(binderContext.getJoinTableProjectionSegments(),
TableSourceType.TEMPORARY_TABLE));
+ return Collections.singleton(new
SimpleTableSegmentBinderContext(binderContext.getJoinTableProjectionSegments(),
TableSourceType.MIXED_TABLE));
}
return tableBinderContexts.values();
}
@@ -154,10 +158,12 @@ public final class ColumnSegmentBinder {
final
SQLStatementBinderContext binderContext) {
ColumnSegmentInfo result =
getInputInfoFromTableBinderContexts(tableBinderContexts, segment,
parentSegmentType);
if (!result.getInputColumnSegment().isPresent()) {
- result = new
ColumnSegmentInfo(findInputColumnSegmentFromOuterTable(segment,
outerTableBinderContexts).orElse(null), TableSourceType.TEMPORARY_TABLE);
+ ColumnSegment inputColumnSegment =
findInputColumnSegmentFromOuterTable(segment,
outerTableBinderContexts).orElse(null);
+ result = new ColumnSegmentInfo(inputColumnSegment, null ==
inputColumnSegment ? TableSourceType.TEMPORARY_TABLE :
inputColumnSegment.getColumnBoundInfo().getTableSourceType());
}
if (!result.getInputColumnSegment().isPresent()) {
- result = new
ColumnSegmentInfo(findInputColumnSegmentFromExternalTables(segment,
binderContext.getExternalTableBinderContexts()).orElse(null),
TableSourceType.TEMPORARY_TABLE);
+ ColumnSegment inputColumnSegment =
findInputColumnSegmentFromExternalTables(segment,
binderContext.getExternalTableBinderContexts()).orElse(null);
+ result = new ColumnSegmentInfo(inputColumnSegment, null ==
inputColumnSegment ? TableSourceType.TEMPORARY_TABLE :
inputColumnSegment.getColumnBoundInfo().getTableSourceType());
}
if (!result.getInputColumnSegment().isPresent()) {
result = new
ColumnSegmentInfo(findInputColumnSegmentByVariables(segment,
binderContext.getSqlStatement().getVariableNames()).orElse(null),
TableSourceType.TEMPORARY_TABLE);
@@ -184,6 +190,9 @@ public final class ColumnSegmentBinder {
() -> new
AmbiguousColumnException(segment.getExpression(),
SEGMENT_TYPE_MESSAGES.getOrDefault(parentSegmentType,
UNKNOWN_SEGMENT_TYPE_MESSAGE)));
}
inputColumnSegment = getColumnSegment(projectionSegment.get());
+ // SPEX ADDED: BEGIN
+ // NOTE: MIXED_TABLE 用于表示 JOIN
之后的字段，其中可能会包含部分物理表字段，以及派生的临时表字段，同层级查询的 ORDER BY, GROUP BY, HAVING 会引用 JOIN 之后的字段
+ // SPEX ADDED: END
tableSourceType = TableSourceType.MIXED_TABLE ==
each.getTableSourceType() ?
getTableSourceTypeFromInputColumn(inputColumnSegment) :
each.getTableSourceType();
if (each instanceof SimpleTableSegmentBinderContext &&
((SimpleTableSegmentBinderContext) each).isFromWithSegment()) {
break;
@@ -200,9 +209,38 @@ public final class ColumnSegmentBinder {
if (projectionSegment instanceof ColumnProjectionSegment) {
return ((ColumnProjectionSegment) projectionSegment).getColumn();
}
+ // SPEX ADDED: BEGIN
+ if (projectionSegment instanceof ExpressionProjectionSegment
+ && ((ExpressionProjectionSegment) projectionSegment).getExpr()
instanceof FunctionSegment) {
+ Optional<ColumnSegment> columnSegment =
getFirstColumnParameter(((FunctionSegment) ((ExpressionProjectionSegment)
projectionSegment).getExpr()).getParameters());
+ if (columnSegment.isPresent()) {
+ return columnSegment.get();
+ }
+ }
+ if (projectionSegment instanceof SubqueryProjectionSegment && 1 ==
((SubqueryProjectionSegment)
projectionSegment).getSubquery().getSelect().getProjections().getProjections().size())
{
+ return getColumnSegment(((SubqueryProjectionSegment)
projectionSegment).getSubquery().getSelect().getProjections().getProjections().iterator().next());
+ }
+ // SPEX ADDED: END
return new ColumnSegment(0, 0, new
IdentifierValue(projectionSegment.getColumnLabel()));
}
+ private static Optional<ColumnSegment> getFirstColumnParameter(final
Collection<ExpressionSegment> parameters) {
+ int count = 0;
+ ColumnSegment columnSegment = null;
+ // TODO consider how to bind multi parameters
+ for (ExpressionSegment each : parameters) {
+ if (each instanceof ColumnSegment) {
+ count++;
+ columnSegment = (ColumnSegment) each;
+ }
+ }
+ if (1 == count) {
+ return Optional.of(columnSegment);
+ } else {
+ return Optional.empty();
+ }
+ }
+
private static Optional<ColumnSegment>
findInputColumnSegmentByPivotColumns(final ColumnSegment segment, final
Collection<String> pivotColumnNames) {
return pivotColumnNames.isEmpty() ||
!pivotColumnNames.contains(segment.getIdentifier().getValue()) ?
Optional.empty() : Optional.of(new ColumnSegment(0, 0,
segment.getIdentifier()));
}
@@ -313,7 +351,9 @@ public final class ColumnSegmentBinder {
: segmentOriginalTable;
IdentifierValue segmentOriginalColumn =
segment.getColumnBoundInfo().getOriginalColumn();
IdentifierValue originalColumn =
Optional.ofNullable(inputColumnSegment).map(optional ->
optional.getColumnBoundInfo().getOriginalColumn()).orElse(segmentOriginalColumn);
- return new ColumnSegmentBoundInfo(new
TableSegmentBoundInfo(originalDatabase, originalSchema), originalTable,
originalColumn, tableSourceType);
+ ColumnSegmentBoundInfo result = new ColumnSegmentBoundInfo(new
TableSegmentBoundInfo(originalDatabase, originalSchema), originalTable,
originalColumn, tableSourceType);
+
Optional.ofNullable(inputColumnSegment).flatMap(ColumnSegment::getOwner).ifPresent(result::setOwner);
+ return result;
}
@RequiredArgsConstructor
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 12300a8fe04..db4b5ebff70 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
@@ -18,6 +18,7 @@
package org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type;
import com.cedarsoftware.util.CaseInsensitiveMap.CaseInsensitiveString;
+import com.google.common.collect.LinkedHashMultimap;
import com.google.common.collect.Multimap;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
@@ -62,7 +63,9 @@ public final class SubqueryTableSegmentBinder {
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);
+ Multimap<CaseInsensitiveString, TableSegmentBinderContext>
subqueryOuterTableBinderContexts =
LinkedHashMultimap.create(outerTableBinderContexts);
+ subqueryOuterTableBinderContexts.putAll(tableBinderContexts);
+ SelectStatement boundSubSelect = new
SelectStatementBinder(subqueryOuterTableBinderContexts).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(""));
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 ff664098d33..ecff4f7980b 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
@@ -24,6 +24,7 @@ import
org.apache.shardingsphere.database.connector.core.type.DatabaseType;
import
org.apache.shardingsphere.database.connector.core.type.DatabaseTypeRegistry;
import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.Projection;
import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.impl.ColumnProjection;
+import
org.apache.shardingsphere.infra.binder.context.segment.select.projection.impl.DerivedProjection;
import
org.apache.shardingsphere.infra.rewrite.sql.token.common.pojo.RouteUnitAware;
import org.apache.shardingsphere.infra.rewrite.sql.token.common.pojo.SQLToken;
import
org.apache.shardingsphere.infra.rewrite.sql.token.common.pojo.Substitutable;
@@ -63,8 +64,13 @@ public final class SubstitutableColumnNameToken extends
SQLToken implements Subs
@Override
public String toString(final RouteUnit routeUnit) {
- Map<String, String> logicAndActualTables =
getLogicAndActualTables(routeUnit);
StringBuilder result = new StringBuilder();
+ appendProjections(routeUnit, result);
+ return result.toString();
+ }
+
+ private void appendProjections(final RouteUnit routeUnit, final
StringBuilder result) {
+ Map<String, String> logicAndActualTables =
getLogicAndActualTables(routeUnit);
int index = 0;
for (Projection each : projections) {
if (index > 0) {
@@ -73,7 +79,6 @@ public final class SubstitutableColumnNameToken extends
SQLToken implements Subs
result.append(getColumnExpression(each, logicAndActualTables));
index++;
}
- return result.toString();
}
private Map<String, String> getLogicAndActualTables(final RouteUnit
routeUnit) {
@@ -91,6 +96,8 @@ public final class SubstitutableColumnNameToken extends
SQLToken implements Subs
StringBuilder builder = new StringBuilder();
if (projection instanceof ColumnProjection) {
appendColumnProjection((ColumnProjection) projection,
logicActualTableNames, builder);
+ } else if (projection instanceof DerivedProjection) {
+ builder.append(projection.getExpression());
} else {
builder.append(quoteCharacter.wrap(projection.getColumnLabel()));
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/enums/SubqueryType.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/enums/SubqueryType.java
index dfb1fd59ff5..78990134e31 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/enums/SubqueryType.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/enums/SubqueryType.java
@@ -22,5 +22,5 @@ package
org.apache.shardingsphere.sql.parser.statement.core.enums;
*/
public enum SubqueryType {
- PROJECTION, TABLE, JOIN, PREDICATE, INSERT_SELECT, EXISTS, WITH,
VIEW_DEFINITION, UPDATE_JOIN, DELETE_JOIN
+ PROJECTION, TABLE, JOIN, PREDICATE, INSERT_SELECT, EXISTS, WITH,
VIEW_DEFINITION, UPDATE_JOIN, DELETE_JOIN, GROUP_BY, ORDER_BY, FUNCTION
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ColumnExtractor.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ColumnExtractor.java
index 513684908eb..f6e927238fa 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ColumnExtractor.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ColumnExtractor.java
@@ -22,10 +22,14 @@ 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.BetweenExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BinaryOperationExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.CaseWhenExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.CollateExpression;
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.expr.InExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.NotExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.RowExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.UnaryOperationExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.complex.CommonTableExpressionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.AggregationProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
@@ -84,6 +88,45 @@ public final class ColumnExtractor {
if (expression instanceof FunctionSegment) {
extractColumnsInFunctionSegment((FunctionSegment) expression,
result);
}
+ if (expression instanceof CaseWhenExpression) {
+ extractColumnsInCaseWhenExpression((CaseWhenExpression)
expression, result);
+ }
+ if (expression instanceof NotExpression) {
+ result.addAll(extract(((NotExpression)
expression).getExpression()));
+ }
+ if (expression instanceof UnaryOperationExpression) {
+ result.addAll(extract(((UnaryOperationExpression)
expression).getExpression()));
+ }
+ if (expression instanceof CollateExpression) {
+ ((CollateExpression) expression).getExpr().ifPresent(optional ->
result.addAll(extract(optional)));
+ }
+ if (expression instanceof RowExpression) {
+ extractColumnsInRowExpression((RowExpression) expression, result);
+ }
+ return result;
+ }
+
+ private static void extractColumnsInCaseWhenExpression(final
CaseWhenExpression expression, final Collection<ColumnSegment> result) {
+ if (null != expression.getCaseExpr()) {
+
result.addAll(extractIncludeColumnSegment(expression.getCaseExpr()));
+ }
+ if (null != expression.getWhenExprs()) {
+
expression.getWhenExprs().stream().map(ColumnExtractor::extractIncludeColumnSegment).forEach(result::addAll);
+ }
+ if (null != expression.getThenExprs()) {
+
expression.getThenExprs().stream().map(ColumnExtractor::extractIncludeColumnSegment).forEach(result::addAll);
+ }
+ if (null != expression.getElseExpr()) {
+
result.addAll(extractIncludeColumnSegment(expression.getElseExpr()));
+ }
+ }
+
+ private static Collection<ColumnSegment> extractIncludeColumnSegment(final
ExpressionSegment expression) {
+ Collection<ColumnSegment> result = new LinkedList<>();
+ if (expression instanceof ColumnSegment) {
+ result.add((ColumnSegment) expression);
+ }
+ result.addAll(extract(expression));
return result;
}
@@ -136,6 +179,8 @@ public final class ColumnExtractor {
for (ExpressionSegment each : expression.getItems()) {
if (each instanceof ColumnSegment) {
result.add((ColumnSegment) each);
+ } else {
+ result.addAll(extract(each));
}
}
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ExpressionExtractor.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ExpressionExtractor.java
index 53bd982365c..254ed1388fd 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ExpressionExtractor.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/ExpressionExtractor.java
@@ -26,11 +26,13 @@ import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.datetime.
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BetweenExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BinaryOperationExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.CaseWhenExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.CollateExpression;
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.expr.InExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ListExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.NotExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.QuantifySubqueryExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.TypeCastExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ValuesExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
@@ -39,11 +41,14 @@ import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.subq
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.AggregationProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ExpressionProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.IntervalExpressionProjection;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.join.OuterJoinExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.multiset.MultisetExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.AndPredicate;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.WhereSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.match.MatchAgainstExpression;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.JoinTableSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.SelectStatement;
import java.util.ArrayList;
import java.util.Collection;
@@ -298,4 +303,147 @@ public final class ExpressionExtractor {
}
return result;
}
+
+ /**
+ * Get nested subquery compare expressions from select statement.
+ * Extract expressions where a subquery is connected by comparison
operators (=, !=, <>, >, <, >=, <=).
+ *
+ * @param selectStatement select statement
+ * @return collection of expressions containing subquery with comparison
operators
+ */
+ public static Collection<ExpressionSegment>
getNestedSubqueryCompareExpressions(final SelectStatement selectStatement) {
+ Collection<ExpressionSegment> result = new LinkedList<>();
+ extractSubqueryCompareExpressionsFromProjections(result,
selectStatement);
+ extractSubqueryCompareExpressionsFromWhere(result, selectStatement);
+ extractSubqueryCompareExpressionsFromHaving(result, selectStatement);
+ extractSubqueryCompareExpressionsFromJoin(result, selectStatement);
+ return result;
+ }
+
+ private static void extractSubqueryCompareExpressionsFromProjections(final
Collection<ExpressionSegment> result, final SelectStatement selectStatement) {
+ for (ProjectionSegment each :
selectStatement.getProjections().getProjections()) {
+ if (each instanceof ExpressionProjectionSegment) {
+ extractSubqueryCompareExpressionsFromExpression(result,
((ExpressionProjectionSegment) each).getExpr());
+ }
+ }
+ }
+
+ private static void extractSubqueryCompareExpressionsFromWhere(final
Collection<ExpressionSegment> result, final SelectStatement selectStatement) {
+ if (selectStatement.getWhere().isPresent()) {
+ extractSubqueryCompareExpressionsFromExpression(result,
selectStatement.getWhere().get().getExpr());
+ }
+ }
+
+ private static void extractSubqueryCompareExpressionsFromHaving(final
Collection<ExpressionSegment> result, final SelectStatement selectStatement) {
+ if (selectStatement.getHaving().isPresent()) {
+ extractSubqueryCompareExpressionsFromExpression(result,
selectStatement.getHaving().get().getExpr());
+ }
+ }
+
+ private static void extractSubqueryCompareExpressionsFromJoin(final
Collection<ExpressionSegment> result, final SelectStatement selectStatement) {
+ if (selectStatement.getFrom().isPresent() &&
selectStatement.getFrom().get() instanceof JoinTableSegment) {
+ extractSubqueryCompareExpressionsFromJoinTableSegment(result,
(JoinTableSegment) selectStatement.getFrom().get());
+ }
+ }
+
+ private static void
extractSubqueryCompareExpressionsFromJoinTableSegment(final
Collection<ExpressionSegment> result, final JoinTableSegment joinTableSegment) {
+ if (null != joinTableSegment.getCondition()) {
+ extractSubqueryCompareExpressionsFromExpression(result,
joinTableSegment.getCondition());
+ }
+ if (joinTableSegment.getLeft() instanceof JoinTableSegment) {
+ extractSubqueryCompareExpressionsFromJoinTableSegment(result,
(JoinTableSegment) joinTableSegment.getLeft());
+ }
+ if (joinTableSegment.getRight() instanceof JoinTableSegment) {
+ extractSubqueryCompareExpressionsFromJoinTableSegment(result,
(JoinTableSegment) joinTableSegment.getRight());
+ }
+ }
+
+ private static void extractSubqueryCompareExpressionsFromExpression(final
Collection<ExpressionSegment> result, final ExpressionSegment
expressionSegment) {
+ if (expressionSegment instanceof BinaryOperationExpression) {
+ BinaryOperationExpression binaryExpression =
(BinaryOperationExpression) expressionSegment;
+ if (isComparisonOperator(binaryExpression.getOperator()) &&
containsSubquery(binaryExpression)) {
+ result.add(binaryExpression);
+ }
+ extractSubqueryCompareExpressionsFromExpression(result,
binaryExpression.getLeft());
+ extractSubqueryCompareExpressionsFromExpression(result,
binaryExpression.getRight());
+ }
+ if (expressionSegment instanceof FunctionSegment) {
+ for (ExpressionSegment each : ((FunctionSegment)
expressionSegment).getParameters()) {
+ extractSubqueryCompareExpressionsFromExpression(result, each);
+ }
+ }
+ if (expressionSegment instanceof CaseWhenExpression) {
+ CaseWhenExpression caseWhenExpression = (CaseWhenExpression)
expressionSegment;
+ extractSubqueryCompareExpressionsFromExpression(result,
caseWhenExpression.getCaseExpr());
+ caseWhenExpression.getWhenExprs().forEach(each ->
extractSubqueryCompareExpressionsFromExpression(result, each));
+ caseWhenExpression.getThenExprs().forEach(each ->
extractSubqueryCompareExpressionsFromExpression(result, each));
+ extractSubqueryCompareExpressionsFromExpression(result,
caseWhenExpression.getElseExpr());
+ }
+ if (expressionSegment instanceof BetweenExpression) {
+ BetweenExpression betweenExpression = (BetweenExpression)
expressionSegment;
+ if (containsSubqueryInBetween(betweenExpression)) {
+ result.add(betweenExpression);
+ }
+ extractSubqueryCompareExpressionsFromExpression(result,
betweenExpression.getLeft());
+ extractSubqueryCompareExpressionsFromExpression(result,
betweenExpression.getBetweenExpr());
+ extractSubqueryCompareExpressionsFromExpression(result,
betweenExpression.getAndExpr());
+ }
+ if (expressionSegment instanceof InExpression) {
+ InExpression inExpression = (InExpression) expressionSegment;
+ if (containsSubqueryInIn(inExpression)) {
+ result.add(inExpression);
+ }
+ extractSubqueryCompareExpressionsFromExpression(result,
inExpression.getLeft());
+ extractSubqueryCompareExpressionsFromExpression(result,
inExpression.getRight());
+ }
+ if (expressionSegment instanceof NotExpression) {
+ extractSubqueryCompareExpressionsFromExpression(result,
((NotExpression) expressionSegment).getExpression());
+ }
+ if (expressionSegment instanceof ListExpression) {
+ for (ExpressionSegment each : ((ListExpression)
expressionSegment).getItems()) {
+ extractSubqueryCompareExpressionsFromExpression(result, each);
+ }
+ }
+ if (expressionSegment instanceof CollateExpression) {
+ ((CollateExpression)
expressionSegment).getExpr().ifPresent(optional ->
extractSubqueryCompareExpressionsFromExpression(result, optional));
+ }
+ if (expressionSegment instanceof TypeCastExpression) {
+ extractSubqueryCompareExpressionsFromExpression(result,
((TypeCastExpression) expressionSegment).getExpression());
+ }
+ }
+
+ private static boolean containsSubqueryInBetween(final BetweenExpression
betweenExpression) {
+ return isSubqueryExpression(betweenExpression.getLeft())
+ || isSubqueryExpression(betweenExpression.getBetweenExpr())
+ || isSubqueryExpression(betweenExpression.getAndExpr());
+ }
+
+ private static boolean containsSubqueryInIn(final InExpression
inExpression) {
+ return isSubqueryExpression(inExpression.getLeft()) ||
isSubqueryExpression(inExpression.getRight());
+ }
+
+ private static boolean isComparisonOperator(final String operator) {
+ if (null == operator) {
+ return false;
+ }
+ String upperOperator = operator.toUpperCase();
+ return "=".equals(upperOperator) || "!=".equals(upperOperator) ||
"<>".equals(upperOperator)
+ || ">".equals(upperOperator) || "<".equals(upperOperator)
+ || ">=".equals(upperOperator) || "<=".equals(upperOperator);
+ }
+
+ private static boolean containsSubquery(final BinaryOperationExpression
binaryExpression) {
+ return isSubqueryExpression(binaryExpression.getLeft()) ||
isSubqueryExpression(binaryExpression.getRight());
+ }
+
+ private static boolean isSubqueryExpression(final ExpressionSegment
expressionSegment) {
+ if (expressionSegment instanceof SubqueryExpressionSegment ||
expressionSegment instanceof SubquerySegment
+ || expressionSegment instanceof QuantifySubqueryExpression) {
+ return true;
+ }
+ if (expressionSegment instanceof ListExpression) {
+ return ((ListExpression)
expressionSegment).getItems().stream().anyMatch(ExpressionExtractor::isSubqueryExpression);
+ }
+ return false;
+ }
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/SubqueryExtractor.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/SubqueryExtractor.java
index 79f5157e6b0..7d0ca5e5b75 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/SubqueryExtractor.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/SubqueryExtractor.java
@@ -41,6 +41,10 @@ import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.Expr
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionsSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.SubqueryProjectionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.GroupBySegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.OrderBySegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.ExpressionOrderByItemSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.OrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.match.MatchAgainstExpression;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.JoinTableSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SubqueryTableSegment;
@@ -83,6 +87,15 @@ public final class SubqueryExtractor {
if (selectStatement.getWith().isPresent()) {
extractSubquerySegmentsFromCTEs(result,
selectStatement.getWith().get().getCommonTableExpressions(), needRecursive);
}
+ if (selectStatement.getOrderBy().isPresent()) {
+ extractSubquerySegmentsFromOrderBy(result,
selectStatement.getOrderBy().get(), needRecursive);
+ }
+ if (selectStatement.getGroupBy().isPresent()) {
+ extractSubquerySegmentsFromGroupBy(result,
selectStatement.getGroupBy().get(), needRecursive);
+ }
+ if (selectStatement.getHaving().isPresent()) {
+ extractSubquerySegmentsFromWhere(result,
selectStatement.getHaving().get().getExpr(), needRecursive);
+ }
}
private static void extractSubquerySegmentsFromCTEs(final
List<SubquerySegment> result, final Collection<CommonTableExpressionSegment>
withSegment, final boolean needRecursive) {
@@ -93,9 +106,31 @@ public final class SubqueryExtractor {
}
}
+ private static void extractSubquerySegmentsFromOrderBy(final
List<SubquerySegment> result, final OrderBySegment orderBySegment, final
boolean needRecursive) {
+ for (OrderByItemSegment each : orderBySegment.getOrderByItems()) {
+ if (each instanceof ExpressionOrderByItemSegment) {
+ extractSubquerySegmentsFromExpression(result,
((ExpressionOrderByItemSegment) each).getExpr(), SubqueryType.ORDER_BY,
needRecursive);
+ }
+ }
+ }
+
+ private static void extractSubquerySegmentsFromGroupBy(final
List<SubquerySegment> result, final GroupBySegment groupBySegment, final
boolean needRecursive) {
+ for (OrderByItemSegment each : groupBySegment.getGroupByItems()) {
+ if (each instanceof ExpressionOrderByItemSegment) {
+ extractSubquerySegmentsFromExpression(result,
((ExpressionOrderByItemSegment) each).getExpr(), SubqueryType.GROUP_BY,
needRecursive);
+ }
+ }
+ }
+
private static void extractRecursive(final boolean needRecursive, final
List<SubquerySegment> result, final SelectStatement select, final SubqueryType
parentSubqueryType) {
if (needRecursive) {
extractSubquerySegments(result, select, true, parentSubqueryType);
+ if (!select.getSubqueryType().isPresent() && parentSubqueryType !=
null) {
+ select.setSubqueryType(parentSubqueryType);
+ }
+ if (parentSubqueryType == SubqueryType.WITH) {
+ select.setSubqueryType(SubqueryType.WITH);
+ }
}
}
@@ -122,6 +157,7 @@ public final class SubqueryExtractor {
if (tableSegment instanceof JoinTableSegment) {
extractSubquerySegmentsFromJoinTableSegment(result,
((JoinTableSegment) tableSegment).getLeft(), needRecursive);
extractSubquerySegmentsFromJoinTableSegment(result,
((JoinTableSegment) tableSegment).getRight(), needRecursive);
+ extractSubquerySegmentsFromExpression(result, ((JoinTableSegment)
tableSegment).getCondition(), SubqueryType.PREDICATE, needRecursive);
}
}
@@ -187,13 +223,13 @@ public final class SubqueryExtractor {
extractSubquerySegmentsFromExpression(result, ((NotExpression)
expressionSegment).getExpression(), subqueryType, needRecursive);
}
if (expressionSegment instanceof FunctionSegment) {
- ((FunctionSegment) expressionSegment).getParameters().forEach(each
-> extractSubquerySegmentsFromExpression(result, each, subqueryType,
needRecursive));
+ ((FunctionSegment) expressionSegment).getParameters().forEach(each
-> extractSubquerySegmentsFromExpression(result, each, SubqueryType.FUNCTION,
needRecursive));
}
if (expressionSegment instanceof MatchAgainstExpression) {
extractSubquerySegmentsFromExpression(result,
((MatchAgainstExpression) expressionSegment).getExpr(), subqueryType,
needRecursive);
}
if (expressionSegment instanceof CaseWhenExpression) {
- extractSubquerySegmentsFromCaseWhenExpression(result,
(CaseWhenExpression) expressionSegment, subqueryType, needRecursive);
+ extractSubquerySegmentsFromCaseWhenExpression(result,
(CaseWhenExpression) expressionSegment, SubqueryType.FUNCTION, needRecursive);
}
if (expressionSegment instanceof CollateExpression) {
extractSubquerySegmentsFromExpression(result, ((CollateExpression)
expressionSegment).getCollateName(), subqueryType, needRecursive);
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/bound/ColumnSegmentBoundInfo.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/bound/ColumnSegmentBoundInfo.java
index dba37e23024..9750ab2b72c 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/bound/ColumnSegmentBoundInfo.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/generic/bound/ColumnSegmentBoundInfo.java
@@ -18,7 +18,9 @@
package
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound;
import lombok.Getter;
+import lombok.Setter;
import
org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
/**
@@ -35,6 +37,9 @@ public final class ColumnSegmentBoundInfo {
private final TableSourceType tableSourceType;
+ @Setter
+ private OwnerSegment owner;
+
public ColumnSegmentBoundInfo(final IdentifierValue originalColumn) {
this(null, null, originalColumn, TableSourceType.PHYSICAL_TABLE);
}
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-select.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-select.xml
index bb9aa775e61..9254646792c 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-select.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-select.xml
@@ -19,12 +19,12 @@
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
<rewrite-assertion id="insert_select_account_bak_for_parameters"
db-types="MySQL">
<input sql="INSERT INTO t_account_detail (account_id,
certificate_number, password, amount, status) SELECT 40, certificate_number,
password, amount, status FROM t_account WHERE certificate_number = ?"
parameters="cert1" />
- <output sql="INSERT INTO t_account_detail (account_id,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, status) SELECT 40,
cipher_certificate_number, assisted_query_certificate_number,
like_query_certificate_number, cipher_password, assisted_query_password,
like_query_password, cipher_amount, status FROM t_account WHERE
`assisted_query_certificat [...]
+ <output sql="INSERT INTO t_account_detail (account_id,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, status) SELECT 40,
cipher_certificate_number, `assisted_query_certificate_number`,
`like_query_certificate_number`, cipher_password, `assisted_query_password`,
`like_query_password`, cipher_amount, status FROM t_account WHERE
`assisted_query_ce [...]
</rewrite-assertion>
<rewrite-assertion id="insert_select_account_bak_for_literal"
db-types="MySQL">
<input sql="INSERT INTO t_account_detail (account_id,
certificate_number, password, amount, status) SELECT 40, certificate_number,
password, amount, status FROM t_account WHERE certificate_number = 'cert1'" />
- <output sql="INSERT INTO t_account_detail (account_id,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, status) SELECT 40,
cipher_certificate_number, assisted_query_certificate_number,
like_query_certificate_number, cipher_password, assisted_query_password,
like_query_password, cipher_amount, status FROM t_account WHERE
`assisted_query_certificat [...]
+ <output sql="INSERT INTO t_account_detail (account_id,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, status) SELECT 40,
cipher_certificate_number, `assisted_query_certificate_number`,
`like_query_certificate_number`, cipher_password, `assisted_query_password`,
`like_query_password`, cipher_amount, status FROM t_account WHERE
`assisted_query_ce [...]
</rewrite-assertion>
<rewrite-assertion id="insert_select_account_bak_for_literal_and_quote"
db-types="MySQL">
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 541ec7a0801..2377c0ab510 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,62 +24,62 @@
<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_CIPHER AS certificate_number FROM (SELECT
cipher_certificate_number AS certificate_number_CIPHER,
assisted_query_certificate_number AS certificate_number_ASSISTED,
like_query_certificate_number AS certificate_number_LIKE FROM t_account) o,
t_account u WHERE
o.certificate_number_ASSISTED=u.`assisted_query_certificate_number` AND
u.`assisted_query_password`=?" parameters="assisted [...]
+ <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_CIPHER AS certificate_number FROM (SELECT
cipher_certificate_number AS certificate_number_CIPHER,
assisted_query_certificate_number AS certificate_number_ASSISTED,
like_query_certificate_number AS certificate_number_LIKE FROM t_account_bak) o,
t_account u WHERE
o.certificate_number_ASSISTED=u.`assisted_query_certificate_number` AND
u.`assisted_query_password`=?" parameters="assi [...]
+ <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_CIPHER AS certificate_number
FROM (SELECT a.cipher_certificate_number AS certificate_number_CIPHER,
a.assisted_query_certificate_number AS certificate_number_ASSISTED,
a.like_query_certificate_number AS certificate_number_LIKE 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">
<input sql="SELECT u.amount, u.password, o.certificate_number FROM
(SELECT a.* FROM t_account a) 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_CIPHER AS certificate_number FROM (SELECT
a.`account_id`, a.`cipher_certificate_number` AS `certificate_number_CIPHER`,
a.`assisted_query_certificate_number` AS `certificate_number_ASSISTED`,
a.`like_query_certificate_number` AS `certificate_number_LIKE`,
a.`cipher_password` AS `password_CIPHER`, a.`assisted_query_password` AS
`password_ASSISTED`, a.`like_query_password` AS `pas [...]
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
a.`account_id`, 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_password` AS `password_C`, a.`assisted_query_password` AS
`password_A`, a.`like_query_password` AS `password_L`, a.`cipher_amount` AS `
[...]
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_table_segment_with_shorthand_project_alias_quote"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, o.certificate_number FROM
(SELECT a.* FROM t_account `a`) 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_CIPHER AS certificate_number FROM (SELECT
`a`.`account_id`, `a`.`cipher_certificate_number` AS
`certificate_number_CIPHER`, `a`.`assisted_query_certificate_number` AS
`certificate_number_ASSISTED`, `a`.`like_query_certificate_number` AS
`certificate_number_LIKE`, `a`.`cipher_password` AS `password_CIPHER`,
`a`.`assisted_query_password` AS `password_ASSISTED`, `a`.`like_query_pas [...]
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
`a`.`account_id`, `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_password` AS `password_C`, `a`.`assisted_query_password` AS
`password_A`, `a`.`like_query_password` AS `password_L`, `a`.`cip [...]
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_table_segment_with_shorthand_project"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, o.certificate_number FROM
(SELECT * 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_CIPHER AS certificate_number FROM (SELECT
t_account.`account_id`, t_account.`cipher_certificate_number` AS
`certificate_number_CIPHER`, t_account.`assisted_query_certificate_number` AS
`certificate_number_ASSISTED`, t_account.`like_query_certificate_number` AS
`certificate_number_LIKE`, t_account.`cipher_password` AS `password_CIPHER`,
t_account.`assisted_query_password` AS `pas [...]
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, o.certificate_number_C AS certificate_number FROM (SELECT
t_account.`account_id`, t_account.`cipher_certificate_number` AS
`certificate_number_C`, t_account.`assisted_query_certificate_number` AS
`certificate_number_A`, t_account.`like_query_certificate_number` AS
`certificate_number_L`, t_account.`cipher_password` AS `password_C`,
t_account.`assisted_query_password` AS `password_A`, t_account.`like [...]
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_predicate_right_equal_condition"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u, t_account c WHERE u.certificate_number=(SELECT
certificate_number FROM t_account WHERE password=?) AND u.password=?"
parameters="1, 2" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE
u.`assisted_query_certificate_number`=(SELECT assisted_query_certificate_number
FROM t_account WHERE `assisted_query_password`=?) AND
u.`assisted_query_password`=?" parameters="assisted_query_1, assisted_query_2"
/>
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE
u.`assisted_query_certificate_number`=(SELECT assisted_query_certificate_number
AS certificate_number FROM t_account WHERE `assisted_query_password`=?) AND
u.`assisted_query_password`=?" parameters="assisted_query_1, assisted_query_2"
/>
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_predicate_left_equal_condition"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT certificate_number FROM t_account
WHERE password=?)=u.certificate_number AND u.password=?" parameters="1, 2" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT assisted_query_certificate_number
FROM t_account WHERE
`assisted_query_password`=?)=u.`assisted_query_certificate_number` AND
u.`assisted_query_password`=?" parameters="assisted_query_1, assisted_query_2"
/>
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT assisted_query_certificate_number AS
certificate_number FROM t_account WHERE
`assisted_query_password`=?)=u.`assisted_query_certificate_number` AND
u.`assisted_query_password`=?" parameters="assisted_query_1, assisted_query_2"
/>
</rewrite-assertion>
<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_CIPHER,
ab.assisted_query_certificate_number AS certificate_number_ASSISTED,
ab.like_query_certificate_number AS certificate_number_LIKE 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">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT certificate_number FROM t_account
WHERE password=?)=(SELECT certificate_number FROM t_account_bak WHERE
password=?) AND u.password=?" parameters="1, 2, 3" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT assisted_query_certificate_number
FROM t_account WHERE `assisted_query_password`=?)=(SELECT
assisted_query_certificate_number FROM t_account_bak WHERE
`assisted_query_password`=?) AND u.`assisted_query_password`=?"
parameters="assisted_query_1, assisted_query_2, assisted_query_3" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE (SELECT assisted_query_certificate_number AS
certificate_number FROM t_account WHERE `assisted_query_password`=?)=(SELECT
assisted_query_certificate_number AS certificate_number FROM t_account_bak
WHERE `assisted_query_password`=?) AND u.`assisted_query_password`=?"
parameters="assisted_query_1, assisted_quer [...]
</rewrite-assertion>
<rewrite-assertion id="select_not_nested_subquery_in_predicate_exists"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u WHERE EXISTS(SELECT b.certificate_number from t_account b where
b.certificate_number=u.certificate_number)" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u WHERE EXISTS(SELECT b.assisted_query_certificate_number from
t_account b where
b.`assisted_query_certificate_number`=u.`assisted_query_certificate_number`)" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u WHERE EXISTS(SELECT b.assisted_query_certificate_number AS
certificate_number from t_account b where
b.`assisted_query_certificate_number`=u.`assisted_query_certificate_number`)" />
</rewrite-assertion>
<rewrite-assertion id="select_not_nested_subquery_in_predicate_not_exists"
db-types="MySQL">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u WHERE NOT EXISTS(SELECT b.certificate_number from t_account b
where b.certificate_number=u.certificate_number)" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u WHERE NOT EXISTS(SELECT b.assisted_query_certificate_number
from t_account b where
b.`assisted_query_certificate_number`=u.`assisted_query_certificate_number`)" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u WHERE NOT EXISTS(SELECT b.assisted_query_certificate_number AS
certificate_number from t_account b where
b.`assisted_query_certificate_number`=u.`assisted_query_certificate_number`)" />
</rewrite-assertion>
<rewrite-assertion id="select_not_nested_subquery_in_predicate_not_exists"
db-types="Oracle">
@@ -89,12 +89,12 @@
<rewrite-assertion
id="select_not_nested_subquery_in_predicate_in_condition" db-types="MySQL">
<input sql="SELECT u.amount, u.password, u.certificate_number FROM
t_account_bak u, t_account c WHERE u.certificate_number IN (SELECT
certificate_number FROM t_account WHERE password=?) AND u.password=?"
parameters="1, 2" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE u.`assisted_query_certificate_number` IN
(SELECT assisted_query_certificate_number FROM t_account WHERE
`assisted_query_password`=?) AND u.`assisted_query_password`=?"
parameters="assisted_query_1, assisted_query_2" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password, u.`cipher_certificate_number` AS certificate_number FROM
t_account_bak u, t_account c WHERE u.`assisted_query_certificate_number` IN
(SELECT assisted_query_certificate_number AS certificate_number FROM t_account
WHERE `assisted_query_password`=?) AND u.`assisted_query_password`=?"
parameters="assisted_query_1, assisted_query_2" />
</rewrite-assertion>
<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_CIPHER AS certificate_number,
b.amount_CIPHER AS amount FROM (SELECT a.cipher_certificate_number AS
certificate_number_CIPHER, a.assisted_query_certificate_number AS
certificate_number_ASSISTED, a.like_query_certificate_number AS
certificate_number_LIKE, a.cipher_amount AS amount_CIPHER 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_CIPHER 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_CIPHER,
t.password_ASSISTED, t.password_LIKE FROM (SELECT cipher_password AS
password_CIPHER, assisted_query_password AS password_ASSISTED,
like_query_password AS password_LIKE 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 7426785d90e..6abdbf6d71b 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,25 +19,25 @@
<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_CIPHER, ab.assisted_query_password AS password_ASSISTED FROM
t_account_0 ab) X" />
- <output sql="SELECT count(*) as cnt FROM (SELECT ab.cipher_password AS
password_CIPHER, ab.assisted_query_password AS password_ASSISTED 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">
<input sql="SELECT u.amount, u.password FROM (SELECT a.* FROM
t_account a) o, t_account u WHERE u.password=?" parameters="1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT a.`account_id`, a.`cipher_password` AS
`password_CIPHER`, a.`assisted_query_password` AS `password_ASSISTED`,
a.`cipher_amount` AS `amount_CIPHER` FROM t_account_0 a) o, t_account_0 u WHERE
u.`assisted_query_password`=?" parameters="assisted_query_1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT a.`account_id`, a.`cipher_password` AS
`password_CIPHER`, a.`assisted_query_password` AS `password_ASSISTED`,
a.`cipher_amount` AS `amount_CIPHER` FROM t_account_1 a) o, t_account_1 u WHERE
u.`assisted_query_password`=?" parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT a.`account_id`, a.`cipher_password` AS `password_C`,
a.`assisted_query_password` AS `password_A`, a.`cipher_amount` AS `amount_C`
FROM t_account_0 a) o, t_account_0 u WHERE u.`assisted_query_password`=?"
parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT a.`account_id`, a.`cipher_password` AS `password_C`,
a.`assisted_query_password` AS `password_A`, a.`cipher_amount` AS `amount_C`
FROM t_account_1 a) o, t_account_1 u WHERE u.`assisted_query_password`=?"
parameters="assisted_query_1" />
</rewrite-assertion>
<rewrite-assertion
id="select_not_nested_subquery_in_table_segment_with_shorthand_project_alias_quote"
db-types="MySQL">
<input sql="SELECT u.amount, u.password FROM (SELECT `a`.* FROM
t_account `a`) o, t_account u WHERE u.password=?" parameters="1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password_CIPHER`, `a`.`assisted_query_password` AS `password_ASSISTED`,
`a`.`cipher_amount` AS `amount_CIPHER` FROM t_account_0 `a`) o, t_account_0 u
WHERE u.`assisted_query_password`=?" parameters="assisted_query_1" />
- <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password_CIPHER`, `a`.`assisted_query_password` AS `password_ASSISTED`,
`a`.`cipher_amount` AS `amount_CIPHER` FROM t_account_1 `a`) o, t_account_1 u
WHERE u.`assisted_query_password`=?" parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password_C`, `a`.`assisted_query_password` AS `password_A`,
`a`.`cipher_amount` AS `amount_C` FROM t_account_0 `a`) o, t_account_0 u WHERE
u.`assisted_query_password`=?" parameters="assisted_query_1" />
+ <output sql="SELECT u.`cipher_amount` AS amount, u.`cipher_password`
AS password FROM (SELECT `a`.`account_id`, `a`.`cipher_password` AS
`password_C`, `a`.`assisted_query_password` AS `password_A`,
`a`.`cipher_amount` AS `amount_C` FROM t_account_1 `a`) o, t_account_1 u WHERE
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.password FROM (SELECT a.password FROM t_account
a) o" />
- <output sql="SELECT o.password_CIPHER AS password FROM (SELECT
a.cipher_password AS password_CIPHER, a.assisted_query_password AS
password_ASSISTED FROM t_account_0 a) o" />
- <output sql="SELECT o.password_CIPHER AS password FROM (SELECT
a.cipher_password AS password_CIPHER, a.assisted_query_password AS
password_ASSISTED 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>
