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 2026/06/22 16:47:24 UTC
(shardingsphere) branch master updated: Support SqlServer update statement for Specifying a table alias as the target object when use encrypt feature (#38733)
This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 729dc9ddee7 Support SqlServer update statement for Specifying a table alias as the target object when use encrypt feature (#38733)
729dc9ddee7 is described below
commit 729dc9ddee79d7ded9e25958718eacb7ceab0bfb
Author: Claire <cl...@163.com>
AuthorDate: Tue Jun 23 00:47:17 2026 +0800
Support SqlServer update statement for Specifying a table alias as the target object when use encrypt feature (#38733)
* support sqlserver update encrypt statement for Specifying a table alias as the target object
* update release-notes
* update according to review suggestion
* update version
* target-alias table drops the actual FROM table owner/schema
---
RELEASE-NOTES.md | 1 +
.../from/context/TableSegmentBinderContext.java | 20 ++++++++
.../type/SimpleTableSegmentBinderContext.java | 21 ++++++++
.../dml/from/type/SimpleTableSegmentBinder.java | 58 ++++++++++++++++++++--
.../statement/dml/UpdateStatementBinder.java | 14 ++++--
.../type/dml/UpdateStatementContextTest.java | 34 +++++++++++++
.../statement/dml/UpdateStatementBinderTest.java | 48 ++++++++++++++++++
.../statement/type/UpdateStatementConverter.java | 7 +++
.../type/UpdateStatementConverterTest.java | 31 ++++++++++++
.../statement/SQLServerStatementVisitor.java | 26 +++++++++-
.../statement/core/extractor/TableExtractor.java | 5 +-
.../core/statement/type/dml/UpdateStatement.java | 6 ++-
.../engine/scenario/EncryptSQLRewriterIT.java | 11 ++++
.../case/query-with-cipher/dml/update/update.xml | 10 ++++
.../scenario/encrypt/config/query-with-cipher.yaml | 6 +++
15 files changed, 288 insertions(+), 10 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 2dccc313152..317ca39fd12 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -52,6 +52,7 @@
1. SQL Binder: Support SQL bind when with temp table name is same with physical table - [#38411](https://github.com/apache/shardingsphere/pull/38411)
1. JDBC: Support setMaxRows and getMaxRows method in jdbc when not execute SQL - [#38337](https://github.com/apache/shardingsphere/pull/38337)
1. JDBC: Support safe close statement manager - [#38473](https://github.com/apache/shardingsphere/pull/38473)
+1. Encrypt: Support SqlServer update statement for Specifying a table alias as the target object when use encrypt feature - [#38733](https://github.com/apache/shardingsphere/pull/38733)
1. Sharding: Fix HASH_MOD routing mismatch for same negative numeric values across numeric Java types with compatibility switch `normalize-numeric-int-range` - [#38327](https://github.com/apache/shardingsphere/pull/38327)
1. Proxy: Support non column projection for MySQL prepared statement in Proxy - [#38507](https://github.com/apache/shardingsphere/pull/38507)
1. Proxy: Support driverClassName config in proxy storage unit to solve mysql and mariadb jdbc url conflict - [#38582](https://github.com/apache/shardingsphere/pull/38582)
diff --git a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/TableSegmentBinderContext.java b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/TableSegmentBinderContext.java
index 2be6b13986f..23ce5dc76f4 100644
--- a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/TableSegmentBinderContext.java
+++ b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/TableSegmentBinderContext.java
@@ -19,6 +19,8 @@ package org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context;
import org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
import java.util.Collection;
import java.util.Optional;
@@ -49,4 +51,22 @@ public interface TableSegmentBinderContext {
* @return table source type
*/
TableSourceType getTableSourceType();
+
+ /**
+ * Get original table name.
+ *
+ * @return original table name
+ */
+ default Optional<IdentifierValue> getOriginalTableName() {
+ return Optional.empty();
+ }
+
+ /**
+ * Get original owner.
+ *
+ * @return original owner segment
+ */
+ default Optional<OwnerSegment> getOriginalOwner() {
+ return Optional.empty();
+ }
}
diff --git a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/type/SimpleTableSegmentBinderContext.java b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/type/SimpleTableSegmentBinderContext.java
index d9aea4f05ca..9f8cc332e65 100644
--- a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/type/SimpleTableSegmentBinderContext.java
+++ b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/context/type/SimpleTableSegmentBinderContext.java
@@ -26,6 +26,8 @@ import org.apache.shardingsphere.infra.binder.engine.segment.dml.from.context.Ta
import org.apache.shardingsphere.sql.parser.statement.core.enums.TableSourceType;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ShorthandProjectionSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
import java.util.Collection;
import java.util.Map;
@@ -44,6 +46,10 @@ public final class SimpleTableSegmentBinderContext implements TableSegmentBinder
private final TableSourceType tableSourceType;
+ private IdentifierValue originalTableName;
+
+ private OwnerSegment originalOwner;
+
private boolean fromWithSegment;
private boolean containsDBLink;
@@ -54,6 +60,11 @@ public final class SimpleTableSegmentBinderContext implements TableSegmentBinder
this.tableSourceType = tableSourceType;
}
+ public SimpleTableSegmentBinderContext(final Collection<ProjectionSegment> projectionSegments, final TableSourceType tableSourceType, final IdentifierValue originalTableName) {
+ this(projectionSegments, tableSourceType);
+ this.originalTableName = originalTableName;
+ }
+
private void putColumnLabelProjectionSegments(final ProjectionSegment projectionSegment, final Map<String, ProjectionSegment> columnLabelProjectionSegments) {
if (projectionSegment instanceof ShorthandProjectionSegment) {
((ShorthandProjectionSegment) projectionSegment).getActualProjectionSegments().forEach(each -> columnLabelProjectionSegments.put(each.getColumnLabel(), each));
@@ -71,4 +82,14 @@ public final class SimpleTableSegmentBinderContext implements TableSegmentBinder
public Collection<ProjectionSegment> getProjectionSegments() {
return columnLabelProjectionSegments.values();
}
+
+ @Override
+ public Optional<IdentifierValue> getOriginalTableName() {
+ return Optional.ofNullable(originalTableName);
+ }
+
+ @Override
+ public Optional<OwnerSegment> getOriginalOwner() {
+ return Optional.ofNullable(originalOwner);
+ }
}
diff --git a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
index 429f37e852d..8b58d65f0c1 100644
--- a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
+++ b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
@@ -50,6 +50,7 @@ import org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.table.Ren
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.PivotSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.ColumnSegmentBoundInfo;
@@ -65,6 +66,7 @@ import org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.ta
import org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.AlterViewStatement;
import org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.CreateViewStatement;
import org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.DropViewStatement;
+import org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.UpdateStatement;
import org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
import java.util.Collection;
@@ -93,7 +95,10 @@ public final class SimpleTableSegmentBinder {
Optional<IdentifierValue> schemaName = getSchemaName(segment, binderContext, databaseName);
IdentifierValue tableName = segment.getTableName().getIdentifier();
Optional<ShardingSphereSchema> schema = schemaName.map(identifierValue -> binderContext.getMetaData().getDatabase(databaseName).getSchema(identifierValue));
- checkTableExists(binderContext, schema.orElse(null), tableName, segment);
+ if (isUpdateTargetTableAlias(binderContext, tableBinderContexts, tableName.getValue(), segment)) {
+ return bindUpdateTargetTableAlias(segment, binderContext, tableBinderContexts, databaseName, schemaName, tableName);
+ }
+ checkTableExists(binderContext, schema.orElse(null), tableName, segment, tableBinderContexts);
checkTableMetadata(binderContext, schema.orElse(null), schemaName.map(IdentifierValue::getValue).orElse(null), tableName);
String tableAliasOrName = segment.getAliasName().orElseGet(tableName::getValue);
Optional<SimpleTableSegmentBinderContext> tableBinderContext = createSimpleTableBinderContext(segment, schema.orElse(null), databaseName, schemaName.orElse(null), binderContext);
@@ -145,8 +150,53 @@ public final class SimpleTableSegmentBinder {
return Optional.ofNullable(database.getDefaultSchemaName()).map(IdentifierValue::new);
}
- private static void checkTableExists(final SQLStatementBinderContext binderContext, final ShardingSphereSchema schema, final IdentifierValue tableName, final SimpleTableSegment segment) {
+ private static boolean isUpdateTargetTableAlias(final SQLStatementBinderContext binderContext, final Multimap<CaseInsensitiveString, TableSegmentBinderContext> tableBinderContexts,
+ final String tableNameValue, final SimpleTableSegment segment) {
+ if (!(binderContext.getSqlStatement() instanceof UpdateStatement)) {
+ return false;
+ }
+ UpdateStatement updateStatement = (UpdateStatement) binderContext.getSqlStatement();
+ if (!updateStatement.getFrom().isPresent()) {
+ return false;
+ }
+ if (!updateStatement.isTargetTableIsFromAlias()) {
+ return false;
+ }
+ if (!(updateStatement.getTable() instanceof SimpleTableSegment)) {
+ return false;
+ }
+ if (!((SimpleTableSegment) updateStatement.getTable()).getTableName().getIdentifier().getValue().equalsIgnoreCase(tableNameValue)) {
+ return false;
+ }
+ if (segment.getAliasName().isPresent()) {
+ return false;
+ }
+ return tableBinderContexts.containsKey(CaseInsensitiveString.of(tableNameValue));
+ }
+
+ private static SimpleTableSegment bindUpdateTargetTableAlias(final SimpleTableSegment segment, final SQLStatementBinderContext binderContext,
+ final Multimap<CaseInsensitiveString, TableSegmentBinderContext> tableBinderContexts, final IdentifierValue databaseName,
+ final Optional<IdentifierValue> schemaName, final IdentifierValue tableName) {
+ Collection<TableSegmentBinderContext> fromTableContexts = tableBinderContexts.get(CaseInsensitiveString.of(tableName.getValue()));
+ IdentifierValue originalTableName = fromTableContexts.stream()
+ .map(TableSegmentBinderContext::getOriginalTableName).filter(Optional::isPresent).map(Optional::get).findFirst().orElse(tableName);
+ Optional<OwnerSegment> fromTableOwner = fromTableContexts.stream()
+ .map(TableSegmentBinderContext::getOriginalOwner).filter(Optional::isPresent).map(Optional::get).findFirst();
+ IdentifierValue resolvedSchemaName = fromTableOwner.map(OwnerSegment::getIdentifier).orElseGet(() -> schemaName.orElse(null));
+ TableNameSegment tableNameSegment = new TableNameSegment(segment.getTableName().getStartIndex(), segment.getTableName().getStopIndex(), originalTableName);
+ tableNameSegment.setTableBoundInfo(new TableSegmentBoundInfo(databaseName, resolvedSchemaName));
+ SimpleTableSegment result = new SimpleTableSegment(tableNameSegment);
+ fromTableOwner.ifPresent(result::setOwner);
+ result.setAlias(segment.getAliasSegment().orElseGet(() -> new AliasSegment(segment.getTableName().getStartIndex(), segment.getTableName().getStopIndex(), tableName)));
+ return result;
+ }
+
+ private static void checkTableExists(final SQLStatementBinderContext binderContext, final ShardingSphereSchema schema, final IdentifierValue tableName, final SimpleTableSegment segment,
+ final Multimap<CaseInsensitiveString, TableSegmentBinderContext> tableBinderContexts) {
String tableNameValue = tableName.getValue();
+ if (isUpdateTargetTableAlias(binderContext, tableBinderContexts, tableNameValue, segment)) {
+ return;
+ }
// TODO refactor table exists check with spi @duanzhengqiang
if (binderContext.getSqlStatement() instanceof CreateTableStatement && isCreateTable(((CreateTableStatement) binderContext.getSqlStatement()).getTable(), tableNameValue)) {
ShardingSpherePreconditions.checkState(binderContext.getHintValueContext().isSkipMetadataValidate()
@@ -311,7 +361,9 @@ public final class SimpleTableSegmentBinder {
columnProjectionSegment.setVisible(each.isVisible());
projectionSegments.add(columnProjectionSegment);
}
- return Optional.of(new SimpleTableSegmentBinderContext(projectionSegments, TableSourceType.PHYSICAL_TABLE));
+ SimpleTableSegmentBinderContext result = new SimpleTableSegmentBinderContext(projectionSegments, TableSourceType.PHYSICAL_TABLE, tableName);
+ segment.getOwner().ifPresent(result::setOriginalOwner);
+ return Optional.of(result);
}
private static ColumnSegment createColumnSegment(final SimpleTableSegment segment, final IdentifierValue databaseName, final IdentifierValue schemaName,
diff --git a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinder.java b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinder.java
index aa89b0e5c84..2bdb69e36f0 100644
--- a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinder.java
+++ b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinder.java
@@ -46,8 +46,15 @@ public final class UpdateStatementBinder implements SQLStatementBinder<UpdateSta
Multimap<CaseInsensitiveString, TableSegmentBinderContext> outerTableBinderContexts = LinkedHashMultimap.create();
WithSegment boundWith = sqlStatement.getWith().map(optional -> WithSegmentBinder.bind(optional, binderContext, outerTableBinderContexts)).orElse(null);
Multimap<CaseInsensitiveString, TableSegmentBinderContext> tableBinderContexts = LinkedHashMultimap.create();
- TableSegment boundTable = TableSegmentBinder.bind(sqlStatement.getTable(), binderContext, tableBinderContexts, outerTableBinderContexts);
- TableSegment boundFrom = sqlStatement.getFrom().map(optional -> TableSegmentBinder.bind(optional, binderContext, tableBinderContexts, outerTableBinderContexts)).orElse(null);
+ TableSegment boundFrom;
+ TableSegment boundTable;
+ if (sqlStatement.isTargetTableIsFromAlias()) {
+ boundFrom = sqlStatement.getFrom().map(optional -> TableSegmentBinder.bind(optional, binderContext, tableBinderContexts, outerTableBinderContexts)).orElse(null);
+ boundTable = TableSegmentBinder.bind(sqlStatement.getTable(), binderContext, tableBinderContexts, outerTableBinderContexts);
+ } else {
+ boundTable = TableSegmentBinder.bind(sqlStatement.getTable(), binderContext, tableBinderContexts, outerTableBinderContexts);
+ boundFrom = sqlStatement.getFrom().map(optional -> TableSegmentBinder.bind(optional, binderContext, tableBinderContexts, outerTableBinderContexts)).orElse(null);
+ }
SetAssignmentSegment boundSetAssignment = sqlStatement.getAssignment()
.map(optional -> AssignmentSegmentBinder.bind(optional, binderContext, tableBinderContexts, outerTableBinderContexts)).orElse(null);
WhereSegment boundWhere = sqlStatement.getWhere().map(optional -> WhereSegmentBinder.bind(optional, binderContext, tableBinderContexts, outerTableBinderContexts)).orElse(null);
@@ -59,7 +66,8 @@ public final class UpdateStatementBinder implements SQLStatementBinder<UpdateSta
private UpdateStatement copy(final UpdateStatement sqlStatement, final WithSegment boundWith, final TableSegment boundTable, final TableSegment boundFrom,
final SetAssignmentSegment boundSetAssignment, final WhereSegment boundWhere, final OrderBySegment boundOrderBy) {
UpdateStatement result = UpdateStatement.builder().databaseType(sqlStatement.getDatabaseType()).with(boundWith).table(boundTable)
- .from(boundFrom).setAssignment(boundSetAssignment).where(boundWhere).orderBy(boundOrderBy).limit(sqlStatement.getLimit().orElse(null)).build();
+ .from(boundFrom).setAssignment(boundSetAssignment).where(boundWhere).orderBy(boundOrderBy).limit(sqlStatement.getLimit().orElse(null))
+ .targetTableIsFromAlias(sqlStatement.isTargetTableIsFromAlias()).build();
SQLStatementCopyUtils.copyAttributes(sqlStatement, result);
return result;
}
diff --git a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/type/dml/UpdateStatementContextTest.java b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/type/dml/UpdateStatementContextTest.java
index d66337b2340..76013af938e 100644
--- a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/type/dml/UpdateStatementContextTest.java
+++ b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/type/dml/UpdateStatementContextTest.java
@@ -23,6 +23,7 @@ import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.assignmen
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.BinaryOperationExpression;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.WhereSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.TableSegmentBoundInfo;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.JoinTableSegment;
@@ -43,6 +44,7 @@ import java.util.stream.Collectors;
import static org.hamcrest.Matchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.mockito.Mockito.when;
@ExtendWith(MockitoExtension.class)
@@ -75,6 +77,38 @@ class UpdateStatementContextTest {
is(Arrays.asList("tbl_1", "tbl_2", "tbl_2")));
}
+ @Test
+ void assertGetTableNamesWithSQLServerUpdateAliasTargetExcludesAlias() {
+ SimpleTableSegment scrapReason = new SimpleTableSegment(new TableNameSegment(50, 65, new IdentifierValue("ScrapReason")));
+ scrapReason.setAlias(new AliasSegment(67, 68, new IdentifierValue("sr")));
+ SimpleTableSegment workOrder = new SimpleTableSegment(new TableNameSegment(75, 83, new IdentifierValue("WorkOrder")));
+ workOrder.setAlias(new AliasSegment(85, 86, new IdentifierValue("wo")));
+ JoinTableSegment joinTable = new JoinTableSegment();
+ joinTable.setLeft(scrapReason);
+ joinTable.setRight(workOrder);
+ SimpleTableSegment aliasTarget = new SimpleTableSegment(new TableNameSegment(7, 8, new IdentifierValue("sr")));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType).table(aliasTarget).from(joinTable).setAssignment(new SetAssignmentSegment(0, 0, Collections.emptyList()))
+ .targetTableIsFromAlias(true).build();
+ UpdateStatementContext actual = new UpdateStatementContext(updateStatement);
+ assertThat(actual.getTablesContext().getTableNames(), is(new HashSet<>(Arrays.asList("ScrapReason", "WorkOrder"))));
+ assertFalse(actual.getTablesContext().getTableNames().contains("sr"));
+ }
+
+ @Test
+ void assertGetTableNamesWithPostgreSQLUpdateFromClauseIncludesTargetTable() {
+ SimpleTableSegment targetTable = new SimpleTableSegment(new TableNameSegment(7, 18, new IdentifierValue("ScrapReason")));
+ targetTable.setAlias(new AliasSegment(20, 21, new IdentifierValue("sr")));
+ SimpleTableSegment fromTable = new SimpleTableSegment(new TableNameSegment(50, 58, new IdentifierValue("WorkOrder")));
+ fromTable.setAlias(new AliasSegment(60, 61, new IdentifierValue("wo")));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType).table(targetTable).from(fromTable).setAssignment(new SetAssignmentSegment(0, 0, Collections.emptyList()))
+ .build();
+ UpdateStatementContext actual = new UpdateStatementContext(updateStatement);
+ assertThat(actual.getTablesContext().getTableNames(), is(new HashSet<>(Arrays.asList("ScrapReason", "WorkOrder"))));
+ assertFalse(actual.getTablesContext().getTableNames().contains("sr"));
+ }
+
private UpdateStatement createUpdateStatement(final TableNameSegment tableNameSegment1, final TableNameSegment tableNameSegment2) {
SimpleTableSegment table1 = new SimpleTableSegment(tableNameSegment1);
SimpleTableSegment table2 = new SimpleTableSegment(tableNameSegment2);
diff --git a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinderTest.java b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinderTest.java
index 454e3c9fb8b..53b3ba27fc0 100644
--- a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinderTest.java
+++ b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/statement/dml/UpdateStatementBinderTest.java
@@ -38,6 +38,7 @@ import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.Ord
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.ColumnOrderByItemSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.WhereSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.AliasSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.OwnerSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SimpleTableSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.TableNameSegment;
@@ -104,6 +105,53 @@ class UpdateStatementBinderTest {
assertThat(actualOrderByItem.getColumn().getColumnBoundInfo().getOriginalTable().getValue(), is("t_order"));
}
+ @Test
+ void assertBindUpdateTargetTableAlias() {
+ SimpleTableSegment targetTable = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("o")));
+ SimpleTableSegment fromTable = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order")));
+ fromTable.setAlias(new AliasSegment(0, 0, new IdentifierValue("o")));
+ ColumnSegment columnSegment = new ColumnSegment(0, 0, new IdentifierValue("status"));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType)
+ .table(targetTable)
+ .from(fromTable)
+ .setAssignment(new SetAssignmentSegment(0, 0, Collections.singletonList(
+ new ColumnAssignmentSegment(0, 0, Collections.singletonList(columnSegment), new LiteralExpressionSegment(0, 0, 1)))))
+ .targetTableIsFromAlias(true)
+ .build();
+ UpdateStatement actual = new UpdateStatementBinder().bind(updateStatement,
+ new SQLStatementBinderContext(createMetaData(), "foo_db", new HintValueContext(), updateStatement));
+ ColumnSegment actualColumn = actual.getAssignment().get().getAssignments().iterator().next()
+ .getColumns().iterator().next();
+ assertThat(((SimpleTableSegment) actual.getTable()).getTableName().getIdentifier().getValue(), is("t_order"));
+ assertThat(((SimpleTableSegment) actual.getTable()).getAliasName().get(), is("o"));
+ assertThat(actualColumn.getColumnBoundInfo().getOriginalTable().getValue(), is("t_order"));
+ assertTrue(actual.isTargetTableIsFromAlias());
+ }
+
+ @Test
+ void assertBindSchemaQualifiedUpdateTargetTableAlias() {
+ SimpleTableSegment targetTable = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("o")));
+ SimpleTableSegment fromTable = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_order")));
+ fromTable.setOwner(new OwnerSegment(0, 0, new IdentifierValue("foo_db")));
+ fromTable.setAlias(new AliasSegment(0, 0, new IdentifierValue("o")));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType)
+ .table(targetTable)
+ .from(fromTable)
+ .setAssignment(new SetAssignmentSegment(0, 0, Collections.singletonList(
+ new ColumnAssignmentSegment(0, 0, Collections.singletonList(new ColumnSegment(0, 0, new IdentifierValue("status"))), new LiteralExpressionSegment(0, 0, 1)))))
+ .targetTableIsFromAlias(true)
+ .build();
+ UpdateStatement actual = new UpdateStatementBinder().bind(updateStatement,
+ new SQLStatementBinderContext(createMetaData(), "foo_db", new HintValueContext(), updateStatement));
+ assertThat(((SimpleTableSegment) actual.getTable()).getTableName().getIdentifier().getValue(), is("t_order"));
+ assertThat(((SimpleTableSegment) actual.getTable()).getAliasName().get(), is("o"));
+ assertTrue(((SimpleTableSegment) actual.getTable()).getOwner().isPresent());
+ assertThat(((SimpleTableSegment) actual.getTable()).getOwner().get().getIdentifier().getValue(), is("foo_db"));
+ assertTrue(actual.isTargetTableIsFromAlias());
+ }
+
private WithSegment createWithSegment() {
return new WithSegment(0, 0, new LinkedList<>(Collections.singletonList(
new CommonTableExpressionSegment(0, 0, new AliasSegment(0, 0, new IdentifierValue("combined_users")),
diff --git a/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverter.java b/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverter.java
index 1c2c4ad94d0..1e9690afc3d 100644
--- a/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverter.java
+++ b/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverter.java
@@ -58,6 +58,7 @@ public final class UpdateStatementConverter implements SQLStatementConverter<Upd
}
private SqlUpdate convertUpdate(final UpdateStatement updateStatement) {
+ checkResolvedTargetTable(updateStatement);
SqlNode table = TableConverter.convert(updateStatement.getTable()).orElseThrow(IllegalStateException::new);
SqlIdentifier alias = convertTableAlias(updateStatement);
SqlNode condition = updateStatement.getWhere().flatMap(WhereConverter::convert).orElse(null);
@@ -70,6 +71,12 @@ public final class UpdateStatementConverter implements SQLStatementConverter<Upd
return new SqlUpdate(SqlParserPos.ZERO, getTargetTableName(table), columns, expressions, condition, null, alias);
}
+ private void checkResolvedTargetTable(final UpdateStatement updateStatement) {
+ if (updateStatement.isTargetTableIsFromAlias() && !updateStatement.getTable().getAlias().isPresent()) {
+ throw new IllegalStateException("Update target table alias must be resolved before SQL Federation conversion.");
+ }
+ }
+
private SqlIdentifier convertTableAlias(final UpdateStatement updateStatement) {
if (updateStatement.getTable().getAlias().isPresent()) {
IdentifierValue aliasIdentifier = updateStatement.getTable().getAlias().get();
diff --git a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverterTest.java b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverterTest.java
index 94f28523819..ca406e31f35 100644
--- a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverterTest.java
+++ b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/statement/type/UpdateStatementConverterTest.java
@@ -46,8 +46,10 @@ import org.junit.jupiter.api.Test;
import java.util.Collections;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
import static org.hamcrest.Matchers.isA;
import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertThrows;
class UpdateStatementConverterTest {
@@ -82,6 +84,35 @@ class UpdateStatementConverterTest {
assertNull(actual.fetch);
}
+ @Test
+ void assertConvertWithResolvedTargetTableAlias() {
+ SimpleTableSegment tableSegment = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("ScrapReason")));
+ tableSegment.setAlias(new AliasSegment(0, 0, new IdentifierValue("sr")));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType)
+ .table(tableSegment)
+ .setAssignment(createSetAssignmentSegment())
+ .where(new WhereSegment(0, 0, new ParameterMarkerExpressionSegment(0, 0, 0)))
+ .targetTableIsFromAlias(true)
+ .build();
+ SqlUpdate actual = (SqlUpdate) new UpdateStatementConverter().convert(updateStatement);
+ assertThat(((SqlIdentifier) actual.getTargetTable()).getSimple(), is("ScrapReason"));
+ assertThat(actual.getAlias().getSimple(), is("sr"));
+ }
+
+ @Test
+ void assertConvertWithUnresolvedTargetTableAlias() {
+ SimpleTableSegment tableSegment = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("sr")));
+ UpdateStatement updateStatement = UpdateStatement.builder()
+ .databaseType(databaseType)
+ .table(tableSegment)
+ .setAssignment(createSetAssignmentSegment())
+ .where(new WhereSegment(0, 0, new ParameterMarkerExpressionSegment(0, 0, 0)))
+ .targetTableIsFromAlias(true)
+ .build();
+ assertThrows(IllegalStateException.class, () -> new UpdateStatementConverter().convert(updateStatement));
+ }
+
private UpdateStatement createUpdateStatement(final boolean withAlias, final OrderBySegment orderBy, final LimitSegment limit) {
SimpleTableSegment tableSegment = new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("t_update")));
if (withAlias) {
diff --git a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index f5b2a6c1dde..e35e6a67a7c 100644
--- a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++ b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -1702,10 +1702,13 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
if (null != ctx.withClause()) {
result.with((WithSegment) visit(ctx.withClause()));
}
- result.table((TableSegment) visit(ctx.tableReferences()));
+ TableSegment targetTable = (TableSegment) visit(ctx.tableReferences());
+ result.table(targetTable);
result.setAssignment((SetAssignmentSegment) visit(ctx.setAssignmentsClause()));
+ TableSegment fromTable = null;
if (null != ctx.fromClause()) {
- result.from((TableSegment) visit(ctx.fromClause()));
+ fromTable = (TableSegment) visit(ctx.fromClause());
+ result.from(fromTable);
}
if (null != ctx.withTableHint()) {
result.withTableHint((WithTableHintSegment) visit(ctx.withTableHint()));
@@ -1719,11 +1722,30 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
if (null != ctx.outputClause()) {
result.output((OutputSegment) visit(ctx.outputClause()));
}
+ result.targetTableIsFromAlias(isTargetTableAliasInFromClause(targetTable, fromTable));
UpdateStatement updateStatement = result.build();
updateStatement.addParameterMarkers(getParameterMarkerSegments());
return updateStatement;
}
+ private boolean isTargetTableAliasInFromClause(final TableSegment targetTable, final TableSegment fromTable) {
+ if (null == fromTable || !(targetTable instanceof SimpleTableSegment)) {
+ return false;
+ }
+ String targetName = ((SimpleTableSegment) targetTable).getTableName().getIdentifier().getValue();
+ return isAliasInFromClause(targetName, fromTable);
+ }
+
+ private boolean isAliasInFromClause(final String targetName, final TableSegment fromSegment) {
+ if (fromSegment instanceof SimpleTableSegment) {
+ return targetName.equalsIgnoreCase(((SimpleTableSegment) fromSegment).getAliasName().orElse(null));
+ }
+ if (fromSegment instanceof JoinTableSegment) {
+ return isAliasInFromClause(targetName, ((JoinTableSegment) fromSegment).getLeft()) || isAliasInFromClause(targetName, ((JoinTableSegment) fromSegment).getRight());
+ }
+ return false;
+ }
+
@Override
public ASTNode visitOptionHint(final OptionHintContext ctx) {
return new OptionHintSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), getOriginalText(ctx));
diff --git a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/TableExtractor.java b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/TableExtractor.java
index 308ac86d705..208f59f9bdf 100644
--- a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/TableExtractor.java
+++ b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/extractor/TableExtractor.java
@@ -313,7 +313,10 @@ public final class TableExtractor {
* @param updateStatement update statement.
*/
public void extractTablesFromUpdate(final UpdateStatement updateStatement) {
- extractTablesFromTableSegment(updateStatement.getTable());
+ if (!updateStatement.isTargetTableIsFromAlias()) {
+ extractTablesFromTableSegment(updateStatement.getTable());
+ }
+ updateStatement.getFrom().ifPresent(this::extractTablesFromTableSegment);
updateStatement.getSetAssignment().getAssignments().forEach(each -> extractTablesFromExpression(each.getColumns().get(0)));
if (updateStatement.getWhere().isPresent()) {
extractTablesFromExpression(updateStatement.getWhere().get().getExpr());
diff --git a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/UpdateStatement.java b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/UpdateStatement.java
index 27de8ce3838..ba3c84d56ac 100644
--- a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/UpdateStatement.java
+++ b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/UpdateStatement.java
@@ -65,12 +65,15 @@ public final class UpdateStatement extends DMLStatement {
private final OutputSegment output;
+ private final boolean targetTableIsFromAlias;
+
private final SQLStatementAttributes attributes;
@Builder
private UpdateStatement(final DatabaseType databaseType, final TableSegment table, final SetAssignmentSegment setAssignment, final WhereSegment where,
final OrderBySegment orderBy, final LimitSegment limit, final TableSegment from, final WhereSegment deleteWhere, final WithSegment with,
- final ReturningSegment returning, final WithTableHintSegment withTableHint, final OptionHintSegment optionHint, final OutputSegment output) {
+ final ReturningSegment returning, final WithTableHintSegment withTableHint, final OptionHintSegment optionHint, final OutputSegment output,
+ final boolean targetTableIsFromAlias) {
super(databaseType);
this.table = table;
this.setAssignment = setAssignment;
@@ -84,6 +87,7 @@ public final class UpdateStatement extends DMLStatement {
this.withTableHint = withTableHint;
this.optionHint = optionHint;
this.output = output;
+ this.targetTableIsFromAlias = targetTableIsFromAlias;
attributes = new SQLStatementAttributes(new WithSQLStatementAttribute(with));
}
diff --git a/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewriter/engine/scenario/EncryptSQLRewriterIT.java b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewriter/engine/scenario/EncryptSQLRewriterIT.java
index ed16f622d50..e323e87eeb1 100644
--- a/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewriter/engine/scenario/EncryptSQLRewriterIT.java
+++ b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewriter/engine/scenario/EncryptSQLRewriterIT.java
@@ -73,6 +73,15 @@ class EncryptSQLRewriterIT extends SQLRewriterIT {
new ShardingSphereColumn("email", Types.VARCHAR, false, false, false, true, false, false),
new ShardingSphereColumn("telephone", Types.VARCHAR, false, false, false, true, false, false),
new ShardingSphereColumn("creation_date", Types.DATE, false, false, false, true, false, false)), Collections.emptyList(), Collections.emptyList()));
+ tables.add(new ShardingSphereTable("ScrapReason", Arrays.asList(
+ new ShardingSphereColumn("ScrapReasonID", Types.INTEGER, false, false, false, true, false, false),
+ new ShardingSphereColumn("Name", Types.VARCHAR, false, false, false, true, false, false),
+ new ShardingSphereColumn("Remark", Types.VARCHAR, false, false, false, true, false, false),
+ new ShardingSphereColumn("ModifiedDate", Types.TIMESTAMP, false, false, false, true, false, false)), Collections.emptyList(), Collections.emptyList()));
+ tables.add(new ShardingSphereTable("WorkOrder", Arrays.asList(
+ new ShardingSphereColumn("WorkOrderID", Types.INTEGER, false, false, false, true, false, false),
+ new ShardingSphereColumn("ScrapReasonID", Types.INTEGER, false, false, false, true, false, false),
+ new ShardingSphereColumn("ScrappedQty", Types.INTEGER, false, false, false, true, false, false)), Collections.emptyList(), Collections.emptyList()));
return Collections.singleton(new ShardingSphereSchema(schemaName, mock(DatabaseType.class), tables, Collections.emptyList()));
}
@@ -85,6 +94,8 @@ class EncryptSQLRewriterIT extends SQLRewriterIT {
singleRule.get().getAttributes().getAttribute(MutableDataNodeRuleAttribute.class).put("encrypt_ds", schemaName, "t_account_detail");
singleRule.get().getAttributes().getAttribute(MutableDataNodeRuleAttribute.class).put("encrypt_ds", schemaName, "t_order");
singleRule.get().getAttributes().getAttribute(MutableDataNodeRuleAttribute.class).put("encrypt_ds", schemaName, "t_user");
+ singleRule.get().getAttributes().getAttribute(MutableDataNodeRuleAttribute.class).put("encrypt_ds", schemaName, "ScrapReason");
+ singleRule.get().getAttributes().getAttribute(MutableDataNodeRuleAttribute.class).put("encrypt_ds", schemaName, "WorkOrder");
}
}
}
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
index 0e8e6aea10a..161e1d6ca7d 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
@@ -94,4 +94,14 @@
<input sql="UPDATE `t_account_bak` SET `account_id` = ?, `certificate_number` = ?, `password` = ?, `amount` = ?, `status` = ? WHERE `account_id` = ? AND `certificate_number` = ? AND `password` = ? AND `amount` = ? AND `status` = ?" parameters="1, cert, pwd, amt, OK, 2, cert1, pwd1, amt1, OK1" />
<output sql="UPDATE `t_account_bak` SET `account_id` = ?, `cipher_certificate_number` = ?, `assisted_query_certificate_number` = ?, `like_query_certificate_number` = ?, `cipher_password` = ?, `assisted_query_password` = ?, `like_query_password` = ?, `cipher_amount` = ?, `status` = ? WHERE `account_id` = ? AND `assisted_query_certificate_number` = ? AND `assisted_query_password` = ? AND `cipher_amount` = ? AND `status` = ?" parameters="1, encrypt_cert, assisted_query_cert, like_qu [...]
</rewrite-assertion>
+
+ <rewrite-assertion id="om_jupdate_scrap_reason_name_froin_for_literals" db-types="SQLServer">
+ <input sql="UPDATE sr SET sr.Name += ' - tool malfunction', sr.Remark = 'tool malfunction' FROM dbo.ScrapReason AS sr JOIN dbo.WorkOrder AS wo ON sr.ScrapReasonID = wo.ScrapReasonID AND wo.ScrappedQty > 300" />
+ <output sql="UPDATE sr SET sr.Name += ' - tool malfunction', [remark_cipher] = 'encrypt_tool malfunction' FROM dbo.ScrapReason AS sr JOIN dbo.WorkOrder AS wo ON sr.ScrapReasonID = wo.ScrapReasonID AND wo.ScrappedQty > 300" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_scrap_reason_name_from_join_for_literals" db-types="PostgreSQL,openGauss">
+ <input sql="UPDATE ScrapReason AS sr SET Name = Name || ' - tool malfunction', Remark = 'tool malfunction' FROM WorkOrder AS wo WHERE sr.ScrapReasonID = wo.ScrapReasonID AND wo.ScrappedQty > 300" />
+ <output sql="UPDATE ScrapReason AS sr SET Name = Name || ' - tool malfunction', "remark_cipher" = 'encrypt_tool malfunction' FROM WorkOrder AS wo WHERE sr.ScrapReasonID = wo.ScrapReasonID AND wo.ScrappedQty > 300" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml b/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
index f72fa3ee07f..5c047405ec9 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
@@ -152,6 +152,12 @@ rules:
likeQuery:
name: user_telephone_like
encryptorName: rewrite_it_like_encryptor_fixture
+ ScrapReason:
+ columns:
+ Remark:
+ cipher:
+ name: remark_cipher
+ encryptorName: rewrite_normal_fixture
encryptors:
rewrite_normal_fixture:
type: REWRITE.NORMAL.FIXTURE