You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/01/11 03:29:44 UTC
[shardingsphere] branch master updated: support not nested subquery rewrite on exists clause (#14307)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 9fd7489 support not nested subquery rewrite on exists clause (#14307)
9fd7489 is described below
commit 9fd74898ee6e35845b906b183276a9d0da2057f6
Author: cheese8 <yi...@163.com>
AuthorDate: Tue Jan 11 11:29:01 2022 +0800
support not nested subquery rewrite on exists clause (#14307)
* fixbug: not nested subquery on exists clause
* fix ut
* fix checkstyle
* recover sharding ut
* recover sharding ut
* recover sharding ut
* finish
* fix
* fix
---
.../generator/impl/EncryptProjectionTokenGenerator.java | 8 ++++++++
.../sql/parser/sql/common/constant/SubqueryType.java | 2 +-
.../sql/parser/sql/common/util/SubqueryExtractUtil.java | 12 ++++++------
.../scenario/encrypt/case/select_for_query_with_cipher.xml | 10 ++++++++++
4 files changed, 25 insertions(+), 7 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptProjectionTokenGenerator.java b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptProjectionTokenGenerator.java
index 1d2ab84..b911cb6 100644
--- a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptProjectionTokenGenerator.java
+++ b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptProjectionTokenGenerator.java
@@ -150,6 +150,8 @@ public final class EncryptProjectionTokenGenerator extends BaseEncryptSQLTokenGe
result.add(distinctOwner(generatePredicateSubqueryProjection(tableName, column), shorthand));
} else if (SubqueryType.TABLE_SUBQUERY.equals(subqueryType)) {
result.addAll(generateTableSubqueryProjections(tableName, column, shorthand));
+ } else if (SubqueryType.EXISTS_SUBQUERY.equals(subqueryType)) {
+ result.addAll(generateExistsSubqueryProjections(tableName, column, shorthand));
} else {
result.add(distinctOwner(generateCommonProjection(tableName, column, segment), shorthand));
}
@@ -189,6 +191,12 @@ public final class EncryptProjectionTokenGenerator extends BaseEncryptSQLTokenGe
return result;
}
+ private Collection<ColumnProjection> generateExistsSubqueryProjections(final String tableName, final ColumnProjection column, final boolean shorthand) {
+ Collection<ColumnProjection> result = new LinkedList<>();
+ result.add(distinctOwner(new ColumnProjection(column.getOwner(), getEncryptRule().getCipherColumn(tableName, column.getName()), null), shorthand));
+ return result;
+ }
+
private ColumnProjection generateCommonProjection(final String tableName, final ColumnProjection column, final ShorthandProjectionSegment segment) {
String encryptColumnName = getEncryptColumnName(tableName, column.getName());
String owner = (segment != null && segment.getOwner().isPresent()) ? segment.getOwner().get().getIdentifier().getValue() : column.getOwner();
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/constant/SubqueryType.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/constant/SubqueryType.java
index eb60dc0..a2ce0c7 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/constant/SubqueryType.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/constant/SubqueryType.java
@@ -22,5 +22,5 @@ package org.apache.shardingsphere.sql.parser.sql.common.constant;
*/
public enum SubqueryType {
- PROJECTION_SUBQUERY, TABLE_SUBQUERY, PREDICATE_SUBQUERY, INSERT_SELECT_SUBQUERY;
+ PROJECTION_SUBQUERY, TABLE_SUBQUERY, PREDICATE_SUBQUERY, INSERT_SELECT_SUBQUERY, EXISTS_SUBQUERY;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SubqueryExtractUtil.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SubqueryExtractUtil.java
index 20014f3..bde0a6e 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SubqueryExtractUtil.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SubqueryExtractUtil.java
@@ -105,12 +105,6 @@ public final class SubqueryExtractUtil {
result.add(subquery);
result.addAll(getSubquerySegments(subquery.getSelect()));
}
- if (expressionSegment instanceof ExistsSubqueryExpression) {
- SubquerySegment subquery = ((ExistsSubqueryExpression) expressionSegment).getSubquery();
- subquery.setSubqueryType(SubqueryType.PREDICATE_SUBQUERY);
- result.add(subquery);
- result.addAll(getSubquerySegments(subquery.getSelect()));
- }
if (expressionSegment instanceof ListExpression) {
for (ExpressionSegment each : ((ListExpression) expressionSegment).getItems()) {
result.addAll(getSubquerySegmentsFromExpression(each));
@@ -128,6 +122,12 @@ public final class SubqueryExtractUtil {
result.addAll(getSubquerySegmentsFromExpression(((BetweenExpression) expressionSegment).getBetweenExpr()));
result.addAll(getSubquerySegmentsFromExpression(((BetweenExpression) expressionSegment).getAndExpr()));
}
+ if (expressionSegment instanceof ExistsSubqueryExpression) {
+ SubquerySegment subquery = ((ExistsSubqueryExpression) expressionSegment).getSubquery();
+ subquery.setSubqueryType(SubqueryType.EXISTS_SUBQUERY);
+ result.add(subquery);
+ result.addAll(getSubquerySegments(subquery.getSelect()));
+ }
return result;
}
}
diff --git a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/encrypt/case/select_for_query_with_cipher.xml b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/encrypt/case/select_for_query_with_cipher.xml
index 969494d..f30844e 100644
--- a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/encrypt/case/select_for_query_with_cipher.xml
+++ b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/encrypt/case/select_for_query_with_cipher.xml
@@ -164,6 +164,16 @@
<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" />
</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.cipher_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.cipher_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_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" />