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 <[email protected]>
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>
