You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2023/06/08 03:18:39 UTC
[shardingsphere] branch master updated: Support decrypt shorthand projection which expand from subquery column projection (#26104)
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 3520394eb8b Support decrypt shorthand projection which expand from subquery column projection (#26104)
3520394eb8b is described below
commit 3520394eb8b426c1e20fd23621a328bbb6c37afc
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Jun 8 11:18:29 2023 +0800
Support decrypt shorthand projection which expand from subquery column projection (#26104)
* Support decrypt shorthand projection which expand from subquery column projection
* remove public modifier for unit test
* fix unit test
* add more integration test case
---
.../merge/dql/EncryptAlgorithmMetaData.java | 9 ++++++++-
.../generator/EncryptProjectionTokenGenerator.java | 2 +-
.../merge/dql/EncryptAlgorithmMetaDataTest.java | 17 ++++++++++++++++
.../select/projection/engine/ProjectionEngine.java | 8 +++++---
.../select/projection/impl/SubqueryProjection.java | 18 +++++++++++++++--
.../statement/dml/SelectStatementContextTest.java | 23 +++++++++-------------
.../generic/SubstitutableColumnNameTokenTest.java | 6 ++++--
.../cases/dql/dql-integration-select-sub-query.xml | 10 ++++++++++
8 files changed, 70 insertions(+), 23 deletions(-)
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaData.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaData.java
index b44fd039987..e910b7537b4 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaData.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaData.java
@@ -24,6 +24,7 @@ import org.apache.shardingsphere.encrypt.rule.EncryptRule;
import org.apache.shardingsphere.encrypt.api.context.EncryptContext;
import org.apache.shardingsphere.infra.binder.segment.select.projection.Projection;
import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.ColumnProjection;
+import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.SubqueryProjection;
import org.apache.shardingsphere.infra.binder.segment.table.TablesContext;
import org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.database.type.DatabaseTypeEngine;
@@ -83,7 +84,13 @@ public final class EncryptAlgorithmMetaData {
return Optional.empty();
}
Projection projection = expandProjections.get(columnIndex - 1);
- return projection instanceof ColumnProjection ? Optional.of((ColumnProjection) projection) : Optional.empty();
+ if (projection instanceof ColumnProjection) {
+ return Optional.of((ColumnProjection) projection);
+ }
+ if (projection instanceof SubqueryProjection && ((SubqueryProjection) projection).getProjection() instanceof ColumnProjection) {
+ return Optional.of((ColumnProjection) ((SubqueryProjection) projection).getProjection());
+ }
+ return Optional.empty();
}
private Optional<String> findTableName(final ColumnProjection columnProjection, final Map<String, String> columnTableNames) {
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptProjectionTokenGenerator.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptProjectionTokenGenerator.java
index 90824199be2..14c015bed3a 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptProjectionTokenGenerator.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptProjectionTokenGenerator.java
@@ -116,7 +116,7 @@ public final class EncryptProjectionTokenGenerator implements CollectionSQLToken
for (Projection each : actualColumns) {
String tableName = columnTableNames.get(each.getExpression());
if (null == tableName || !encryptRule.findStandardEncryptor(tableName, each.getColumnLabel()).isPresent()) {
- projections.add(each);
+ projections.add(each.getAlias().map(optional -> (Projection) new ColumnProjection(null, optional, null)).orElse(each));
} else if (each instanceof ColumnProjection) {
projections.addAll(generateProjections(tableName, (ColumnProjection) each, subqueryType, true, segment));
}
diff --git a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaDataTest.java b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaDataTest.java
index f6edd85e328..873ae5b50cd 100644
--- a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaDataTest.java
+++ b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptAlgorithmMetaDataTest.java
@@ -24,6 +24,7 @@ import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm;
import org.apache.shardingsphere.infra.binder.segment.select.projection.ProjectionsContext;
import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.ColumnProjection;
import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.DerivedProjection;
+import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.SubqueryProjection;
import org.apache.shardingsphere.infra.binder.segment.table.TablesContext;
import org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.database.DefaultDatabase;
@@ -108,6 +109,22 @@ class EncryptAlgorithmMetaDataTest {
assertThat(actual.get().getColumnName(), is("id"));
}
+ @Test
+ void assertFindEncryptContextWhenSubqueryContainsEncryptColumn() {
+ ColumnProjection columnProjection = new ColumnProjection(null, "user_name", null);
+ Map<String, String> columnTableNames = new HashMap<>();
+ columnTableNames.put(columnProjection.getExpression(), "t_user");
+ when(projectionsContext.getExpandProjections())
+ .thenReturn(Collections.singletonList(new SubqueryProjection("(SELECT user_name FROM t_user)", columnProjection, null, new MySQLDatabaseType())));
+ when(tablesContext.findTableNamesByColumnProjection(Collections.singletonList(columnProjection), schema)).thenReturn(columnTableNames);
+ EncryptAlgorithmMetaData encryptAlgorithmMetaData = new EncryptAlgorithmMetaData(database, encryptRule, selectStatementContext);
+ Optional<EncryptContext> actual = encryptAlgorithmMetaData.findEncryptContext(1);
+ assertTrue(actual.isPresent());
+ assertThat(actual.get().getDatabaseName(), is(DefaultDatabase.LOGIC_NAME));
+ assertThat(actual.get().getTableName(), is("t_user"));
+ assertThat(actual.get().getColumnName(), is("user_name"));
+ }
+
@Test
void assertFindEncryptContextByStatementContext() {
when(tablesContext.findTableNamesByColumnProjection(Collections.singletonList(columnProjection), schema)).thenReturn(Collections.emptyMap());
diff --git a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
index 2f1eb8fe1d1..db43f0d67d2 100644
--- a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
+++ b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/engine/ProjectionEngine.java
@@ -105,7 +105,7 @@ public final class ProjectionEngine {
return Optional.of(createProjection((AggregationProjectionSegment) projectionSegment));
}
if (projectionSegment instanceof SubqueryProjectionSegment) {
- return Optional.of(createProjection((SubqueryProjectionSegment) projectionSegment));
+ return Optional.of(createProjection(table, (SubqueryProjectionSegment) projectionSegment));
}
if (projectionSegment instanceof ParameterMarkerExpressionSegment) {
return Optional.of(createProjection((ParameterMarkerExpressionSegment) projectionSegment));
@@ -117,8 +117,10 @@ public final class ProjectionEngine {
return new ParameterMarkerProjection(projectionSegment.getParameterMarkerIndex(), projectionSegment.getParameterMarkerType(), projectionSegment.getAliasName().orElse(null));
}
- private SubqueryProjection createProjection(final SubqueryProjectionSegment projectionSegment) {
- return new SubqueryProjection(projectionSegment.getText(), projectionSegment.getAliasName().orElse(null));
+ private SubqueryProjection createProjection(final TableSegment table, final SubqueryProjectionSegment projectionSegment) {
+ Projection subqueryProjection = createProjection(table, projectionSegment.getSubquery().getSelect().getProjections().getProjections().iterator().next())
+ .orElseThrow(() -> new IllegalArgumentException("Subquery projection must have at least one projection column."));
+ return new SubqueryProjection(projectionSegment.getText(), subqueryProjection, projectionSegment.getAliasName().orElse(null), databaseType);
}
private ShorthandProjection createProjection(final TableSegment table, final ShorthandProjectionSegment projectionSegment) {
diff --git a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/impl/SubqueryProjection.java b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/impl/SubqueryProjection.java
index 2c88e929214..b456eb08b61 100644
--- a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/impl/SubqueryProjection.java
+++ b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/projection/impl/SubqueryProjection.java
@@ -17,11 +17,14 @@
package org.apache.shardingsphere.infra.binder.segment.select.projection.impl;
+import com.google.common.base.Strings;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.ToString;
import org.apache.shardingsphere.infra.binder.segment.select.projection.Projection;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
+import org.apache.shardingsphere.infra.database.type.dialect.OracleDatabaseType;
import org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import java.util.Optional;
@@ -37,11 +40,22 @@ public final class SubqueryProjection implements Projection {
private final String expression;
+ private final Projection projection;
+
private final String alias;
+ private final DatabaseType databaseType;
+
@Override
public Optional<String> getAlias() {
- return Optional.ofNullable(alias);
+ return Strings.isNullOrEmpty(alias) ? buildDefaultAlias(databaseType) : Optional.of(alias);
+ }
+
+ private Optional<String> buildDefaultAlias(final DatabaseType databaseType) {
+ if (databaseType instanceof OracleDatabaseType) {
+ return Optional.of(expression.replace(" ", "").toUpperCase());
+ }
+ return Optional.of(expression);
}
@Override
@@ -51,6 +65,6 @@ public final class SubqueryProjection implements Projection {
@Override
public Projection cloneWithOwner(final IdentifierValue ownerIdentifier) {
- return new SubqueryProjection(expression, alias);
+ return new SubqueryProjection(expression, projection, alias, databaseType);
}
}
diff --git a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContextTest.java b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContextTest.java
index d8a88584dd5..03473aecbdd 100644
--- a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContextTest.java
+++ b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContextTest.java
@@ -60,7 +60,6 @@ import org.junit.jupiter.api.Test;
import java.util.Arrays;
import java.util.Collections;
-import java.util.Optional;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
@@ -423,21 +422,15 @@ class SelectStatementContextTest {
}
private void assertContainsSubquery(final SelectStatement selectStatement, final SelectStatement subSelectStatement) {
- SubqueryProjectionSegment projectionSegment = mock(SubqueryProjectionSegment.class);
- SubquerySegment subquery = mock(SubquerySegment.class);
- when(projectionSegment.getSubquery()).thenReturn(subquery);
- SelectStatement select = mock(SelectStatement.class);
- when(subquery.getSelect()).thenReturn(select);
- WhereSegment subWhere = mock(WhereSegment.class);
- when(select.getWhere()).thenReturn(Optional.of(subWhere));
- when(projectionSegment.getSubquery().getSelect().getWhere()).thenReturn(Optional.of(mock(WhereSegment.class)));
WhereSegment whereSegment = new WhereSegment(0, 0, null);
subSelectStatement.setWhere(whereSegment);
- subSelectStatement.setProjections(new ProjectionsSegment(0, 0));
- SubquerySegment subquerySegment = new SubquerySegment(0, 0, subSelectStatement);
- when(projectionSegment.getSubquery()).thenReturn(subquerySegment);
+ ProjectionsSegment subqueryProjections = new ProjectionsSegment(0, 0);
+ subqueryProjections.getProjections().add(new ColumnProjectionSegment(new ColumnSegment(0, 0, new IdentifierValue("order_id"))));
+ subSelectStatement.setProjections(subqueryProjections);
ProjectionsSegment projectionsSegment = new ProjectionsSegment(0, 0);
- projectionsSegment.getProjections().add(projectionSegment);
+ SubquerySegment subquerySegment = new SubquerySegment(0, 0, subSelectStatement);
+ SubqueryProjectionSegment subqueryProjectionSegment = new SubqueryProjectionSegment(subquerySegment, "");
+ projectionsSegment.getProjections().add(subqueryProjectionSegment);
selectStatement.setProjections(projectionsSegment);
ShardingSphereDatabase database = mock(ShardingSphereDatabase.class);
assertTrue(new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(), selectStatement, DefaultDatabase.LOGIC_NAME).isContainsSubquery());
@@ -474,7 +467,9 @@ class SelectStatementContextTest {
BinaryOperationExpression expression = new BinaryOperationExpression(0, 0, left, right, "=", null);
WhereSegment subWhereSegment = new WhereSegment(0, 0, expression);
subSelectStatement.setWhere(subWhereSegment);
- subSelectStatement.setProjections(new ProjectionsSegment(0, 0));
+ ProjectionsSegment subqueryProjections = new ProjectionsSegment(0, 0);
+ subqueryProjections.getProjections().add(new ColumnProjectionSegment(new ColumnSegment(0, 0, new IdentifierValue("order_id"))));
+ subSelectStatement.setProjections(subqueryProjections);
SubqueryExpressionSegment subqueryExpressionSegment = new SubqueryExpressionSegment(new SubquerySegment(0, 0, subSelectStatement));
SubqueryProjectionSegment projectionSegment = mock(SubqueryProjectionSegment.class);
WhereSegment whereSegment = new WhereSegment(0, 0, subqueryExpressionSegment);
diff --git a/infra/rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/SubstitutableColumnNameTokenTest.java b/infra/rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/SubstitutableColumnNameTokenTest.java
index a3d85de7a4f..b0697dc2643 100644
--- a/infra/rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/SubstitutableColumnNameTokenTest.java
+++ b/infra/rewrite/src/test/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/SubstitutableColumnNameTokenTest.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.infra.rewrite.sql.token.pojo.generic;
import org.apache.shardingsphere.infra.binder.segment.select.projection.Projection;
import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.ColumnProjection;
import org.apache.shardingsphere.infra.binder.segment.select.projection.impl.SubqueryProjection;
+import org.apache.shardingsphere.infra.database.type.dialect.OracleDatabaseType;
import org.apache.shardingsphere.infra.route.context.RouteUnit;
import org.apache.shardingsphere.sql.parser.sql.common.enums.QuoteCharacter;
import org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
@@ -60,8 +61,9 @@ class SubstitutableColumnNameTokenTest {
@Test
void assertToStringWithSubqueryProjection() {
- Collection<Projection> projections = Arrays.asList(new ColumnProjection(new IdentifierValue("temp", QuoteCharacter.BACK_QUOTE), new IdentifierValue("id", QuoteCharacter.BACK_QUOTE),
- new IdentifierValue("id", QuoteCharacter.BACK_QUOTE)), new SubqueryProjection("(SELECT name FROM t_order)", "name"));
+ Collection<Projection> projections = Arrays.asList(new ColumnProjection(new IdentifierValue("temp", QuoteCharacter.BACK_QUOTE),
+ new IdentifierValue("id", QuoteCharacter.BACK_QUOTE), new IdentifierValue("id", QuoteCharacter.BACK_QUOTE)),
+ new SubqueryProjection("(SELECT name FROM t_order)", new ColumnProjection(null, "name", null), "name", new OracleDatabaseType()));
assertThat(new SubstitutableColumnNameToken(0, 1, projections, QuoteCharacter.BACK_QUOTE).toString(mock(RouteUnit.class)),
is("`temp`.`id` AS `id`, `name`"));
}
diff --git a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
index 14f569faa2b..f9213ddb6cd 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
@@ -83,4 +83,14 @@
scenario-comments="Test single table's LIKE operator underscore wildcard in subquery select statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in subquery select statement when use encrypt feature.">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT business_code, telephone, (SELECT password FROM t_user LIMIT 1) AS password FROM t_merchant" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
+ scenario-comments="Test subquery projection contains encrypt column and config alias when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT business_code, telephone, (SELECT password FROM t_user LIMIT 1) AS password FROM t_merchant) AS temp;" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
+ scenario-comments="Test shorthand expansion contains subquery projection and subquery projection contains encrypt column and config alias when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</integration-test-cases>