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