You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "zhangliang.apache.org via notifications" <no...@shardingsphere.apache.org> on 2026/10/31 11:25:45 UTC
(shardingsphere) branch master updated: Fix INSERT routing for direct Integer casts (#40433)
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 3220ccc7dbe Fix INSERT routing for direct Integer casts (#40433)
3220ccc7dbe is described below
commit 3220ccc7dbe60a30b74659b6a851d41fbacb1ba4
Author: Liang Zhang <zh...@apache.org>
AuthorDate: Sat Oct 31 19:25:38 2026 +0800
Fix INSERT routing for direct Integer casts (#40433)
---
.../InsertClauseShardingConditionEngine.java | 34 +++++--
.../InsertClauseShardingConditionEngineTest.java | 102 +++++++++++++++++++--
.../test/resources/cases/dml/e2e-dml-insert.xml | 9 +-
3 files changed, 126 insertions(+), 19 deletions(-)
diff --git a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngine.java b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngine.java
index df4e78aefa4..2ae979d1a09 100644
--- a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngine.java
+++ b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngine.java
@@ -21,6 +21,7 @@ import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.database.exception.core.exception.data.InsertColumnsAndValuesMismatchedException;
import org.apache.shardingsphere.database.exception.core.exception.syntax.table.NoSuchTableException;
import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
+import org.apache.shardingsphere.infra.annotation.HighFrequencyInvocation;
import org.apache.shardingsphere.infra.binder.context.segment.insert.keygen.GeneratedKeyContext;
import org.apache.shardingsphere.infra.binder.context.segment.insert.values.InsertValueContext;
import org.apache.shardingsphere.infra.binder.context.statement.type.dml.InsertStatementContext;
@@ -34,6 +35,7 @@ import org.apache.shardingsphere.sharding.route.engine.condition.ShardingConditi
import org.apache.shardingsphere.sharding.route.engine.condition.value.ListShardingConditionValue;
import org.apache.shardingsphere.sharding.rule.ShardingRule;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ExpressionSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.TypeCastExpression;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.complex.CommonExpressionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
@@ -134,22 +136,40 @@ public final class InsertClauseShardingConditionEngine {
if (!shardingColumn.isPresent()) {
continue;
}
- if (each instanceof SimpleExpressionSegment) {
- List<Integer> parameterMarkerIndexes = each instanceof ParameterMarkerExpressionSegment
- ? Collections.singletonList(((ParameterMarkerExpressionSegment) each).getParameterMarkerIndex())
+ ExpressionSegment expression = unwrapIntegerCast(each, params);
+ if (expression instanceof SimpleExpressionSegment) {
+ List<Integer> parameterMarkerIndexes = expression instanceof ParameterMarkerExpressionSegment
+ ? Collections.singletonList(((ParameterMarkerExpressionSegment) expression).getParameterMarkerIndex())
: Collections.emptyList();
- Object shardingValue = getShardingValue((SimpleExpressionSegment) each, params);
+ Object shardingValue = getShardingValue((SimpleExpressionSegment) expression, params);
result.getValues().add(new ListShardingConditionValue<>(shardingColumn.get(), tableName, Collections.singletonList(shardingValue),
parameterMarkerIndexes));
- } else if (each instanceof CommonExpressionSegment) {
- generateShardingCondition((CommonExpressionSegment) each, result, shardingColumn.get(), tableName);
- } else if (ExpressionConditionUtils.isNowExpression(each)) {
+ } else if (expression instanceof CommonExpressionSegment) {
+ generateShardingCondition((CommonExpressionSegment) expression, result, shardingColumn.get(), tableName);
+ } else if (ExpressionConditionUtils.isNowExpression(expression)) {
result.getValues().add(new ListShardingConditionValue<>(shardingColumn.get(), tableName, Collections.singletonList(timestampServiceRule.getTimestamp())));
}
}
return result;
}
+ @HighFrequencyInvocation
+ private ExpressionSegment unwrapIntegerCast(final ExpressionSegment expressionSegment, final List<Object> params) {
+ if (!(expressionSegment instanceof TypeCastExpression)) {
+ return expressionSegment;
+ }
+ TypeCastExpression typeCastExpression = (TypeCastExpression) expressionSegment;
+ String dataType = typeCastExpression.getDataType();
+ if (!"int4".equalsIgnoreCase(dataType) && !"int".equalsIgnoreCase(dataType) && !"integer".equalsIgnoreCase(dataType)) {
+ return expressionSegment;
+ }
+ ExpressionSegment result = typeCastExpression.getExpression();
+ if (result instanceof LiteralExpressionSegment) {
+ return ((LiteralExpressionSegment) result).getLiterals() instanceof Integer ? result : expressionSegment;
+ }
+ return result instanceof ParameterMarkerExpressionSegment && params.get(((ParameterMarkerExpressionSegment) result).getParameterMarkerIndex()) instanceof Integer ? result : expressionSegment;
+ }
+
private void generateShardingCondition(final CommonExpressionSegment expressionSegment, final ShardingCondition condition, final String shardingColumn, final String tableName) {
try {
Integer value = Integer.valueOf(expressionSegment.getText());
diff --git a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngineTest.java b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngineTest.java
index 19525176b22..b72b200917e 100644
--- a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngineTest.java
+++ b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/InsertClauseShardingConditionEngineTest.java
@@ -19,7 +19,6 @@ package org.apache.shardingsphere.sharding.route.engine.condition.engine;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
import org.apache.shardingsphere.database.exception.core.exception.data.InsertColumnsAndValuesMismatchedException;
-import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
import org.apache.shardingsphere.infra.binder.context.segment.insert.keygen.GeneratedKeyContext;
import org.apache.shardingsphere.infra.binder.context.segment.insert.values.InsertSelectContext;
import org.apache.shardingsphere.infra.binder.context.segment.insert.values.InsertValueContext;
@@ -29,8 +28,10 @@ import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
import org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
import org.apache.shardingsphere.sharding.route.engine.condition.ShardingCondition;
+import org.apache.shardingsphere.sharding.route.engine.condition.value.ListShardingConditionValue;
import org.apache.shardingsphere.sharding.rule.ShardingRule;
import org.apache.shardingsphere.sharding.rule.ShardingTable;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.TypeCastExpression;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.complex.CommonExpressionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
@@ -38,26 +39,30 @@ import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table
import org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.TableNameSegment;
import org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.InsertStatement;
import org.apache.shardingsphere.sql.parser.statement.core.value.identifier.IdentifierValue;
-import org.apache.shardingsphere.timeservice.config.TimestampServiceRuleConfiguration;
import org.apache.shardingsphere.timeservice.core.rule.TimestampServiceRule;
import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Named;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.MethodSource;
import org.mockito.Answers;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import org.mockito.junit.jupiter.MockitoSettings;
import org.mockito.quality.Strictness;
+import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Optional;
-import java.util.Properties;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.is;
+import static org.hamcrest.Matchers.isA;
+import static org.hamcrest.Matchers.sameInstance;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
@@ -85,7 +90,7 @@ class InsertClauseShardingConditionEngineTest {
void setUp() {
ShardingSphereDatabase database = mockDatabase();
InsertStatement insertStatement = createInsertStatement();
- shardingConditionEngine = new InsertClauseShardingConditionEngine(database, rule, new TimestampServiceRule(new TimestampServiceRuleConfiguration("System", new Properties())));
+ shardingConditionEngine = new InsertClauseShardingConditionEngine(database, rule, mock(TimestampServiceRule.class));
when(insertStatementContext.getSqlStatement()).thenReturn(insertStatement);
when(insertStatementContext.getColumnNames()).thenReturn(Arrays.asList("foo_col_1", "foo_col_3"));
when(insertStatementContext.getInsertValueContexts()).thenReturn(Collections.singletonList(createInsertValueContext()));
@@ -165,8 +170,7 @@ class InsertClauseShardingConditionEngineTest {
void assertCreateShardingConditionsWithoutDefaultSchema() {
ShardingSphereDatabase database = mock(ShardingSphereDatabase.class);
when(database.getDefaultSchemaName()).thenReturn("foo_default_schema");
- InsertClauseShardingConditionEngine engine = new InsertClauseShardingConditionEngine(
- database, rule, new TimestampServiceRule(new TimestampServiceRuleConfiguration("System", new Properties())));
+ InsertClauseShardingConditionEngine engine = new InsertClauseShardingConditionEngine(database, rule, mock(TimestampServiceRule.class));
assertThrows(SchemaNotFoundException.class, () -> engine.createShardingConditions(insertStatementContext, Collections.emptyList()));
}
@@ -189,7 +193,7 @@ class InsertClauseShardingConditionEngineTest {
List<ShardingCondition> shardingConditions = shardingConditionEngine.createShardingConditions(insertStatementContext, Collections.emptyList());
assertThat(shardingConditions.get(0).getStartIndex(), is(0));
assertFalse(shardingConditions.get(0).getValues().isEmpty());
- verify(rule).generateKeys(argThat((AlgorithmSQLContext each) -> "foo_default_schema".equals(each.getSchemaName())), anyInt());
+ verify(rule).generateKeys(argThat(each -> "foo_default_schema".equals(each.getSchemaName())), anyInt());
}
@Test
@@ -203,6 +207,90 @@ class InsertClauseShardingConditionEngineTest {
assertThat(shardingConditions.get(0).getValues().get(0).getParameterMarkerIndexes(), is(Collections.singletonList(0)));
}
+ @ParameterizedTest(name = "{0}: {1}, parameter={2}")
+ @MethodSource("getIntegerCasts")
+ void assertCreateShardingConditionsWithIntegerCast(final String dataType, final Integer value, final boolean parameterized) {
+ TypeCastExpression expression = new TypeCastExpression(0, 8, (parameterized ? "?" : value.toString()) + "::" + dataType,
+ parameterized ? new ParameterMarkerExpressionSegment(0, 0, 0) : new LiteralExpressionSegment(0, 0, value), dataType);
+ List<Object> params = parameterized ? Collections.singletonList(value) : Collections.emptyList();
+ InsertValueContext insertValueContext = new InsertValueContext(Collections.singleton(expression), params, 0);
+ when(insertStatementContext.getInsertValueContexts()).thenReturn(Collections.singletonList(insertValueContext));
+ when(rule.findShardingColumn("foo_col_1", "foo_tbl")).thenReturn(Optional.of("foo_col_1"));
+ List<ShardingCondition> actual = shardingConditionEngine.createShardingConditions(insertStatementContext, params);
+ assertThat(actual.size(), is(1));
+ assertThat(actual.get(0).getValues().size(), is(1));
+ ListShardingConditionValue<?> actualValue = (ListShardingConditionValue<?>) actual.get(0).getValues().get(0);
+ assertThat(actualValue.getColumnName(), is("foo_col_1"));
+ assertThat(actualValue.getTableName(), is("foo_tbl"));
+ assertThat(actualValue.getValues(), is(Collections.singletonList(value)));
+ assertThat(actualValue.getValues().iterator().next(), isA(Integer.class));
+ assertThat(actualValue.getParameterMarkerIndexes(), is(parameterized ? Collections.singletonList(0) : Collections.emptyList()));
+ assertThat(insertValueContext.getValueExpressions().get(0), sameInstance(expression));
+ }
+
+ private static Object[][] getIntegerCasts() {
+ return new Object[][]{
+ {"int4", 1, true},
+ {"int4", Integer.MIN_VALUE, true},
+ {"int4", Integer.MAX_VALUE, true},
+ {"int", -1, true},
+ {"INTEGER", 0, true},
+ {"int4", 1, false},
+ {"int4", Integer.MIN_VALUE, false},
+ {"int4", Integer.MAX_VALUE, false},
+ {"int", -1, false},
+ {"INTEGER", 0, false}};
+ }
+
+ @ParameterizedTest(name = "{0}")
+ @MethodSource("getUnsupportedIntegerCasts")
+ void assertCreateShardingConditionsWithUnsupportedIntegerCast(final TypeCastExpression expression, final List<Object> params) {
+ InsertValueContext insertValueContext = new InsertValueContext(Collections.singleton(expression), params, 0);
+ when(insertStatementContext.getInsertValueContexts()).thenReturn(Collections.singletonList(insertValueContext));
+ when(rule.findShardingColumn("foo_col_1", "foo_tbl")).thenReturn(Optional.of("foo_col_1"));
+ List<ShardingCondition> actual = shardingConditionEngine.createShardingConditions(insertStatementContext, params);
+ assertThat(actual.size(), is(1));
+ assertTrue(actual.get(0).getValues().isEmpty());
+ assertThat(insertValueContext.getValueExpressions().get(0), sameInstance(expression));
+ }
+
+ private static Object[][] getUnsupportedIntegerCasts() {
+ ParameterMarkerExpressionSegment parameterMarker = new ParameterMarkerExpressionSegment(0, 0, 0);
+ TypeCastExpression integerCast = new TypeCastExpression(0, 8, "?::int4", parameterMarker, "int4");
+ return new Object[][]{
+ {Named.of("string parameter", integerCast), Collections.singletonList("01")},
+ {Named.of("decimal parameter", integerCast), Collections.singletonList(BigDecimal.valueOf(15, 1))},
+ {Named.of("long parameter", integerCast), Collections.singletonList(1L)},
+ {Named.of("null parameter", integerCast), Collections.singletonList(null)},
+ {Named.of("text target", new TypeCastExpression(0, 8, "?::text", parameterMarker, "text")), Collections.singletonList(1)},
+ {Named.of("numeric modifier", new TypeCastExpression(0, 8, "?::numeric(4,1)", parameterMarker, "numeric(4,1)")), Collections.singletonList(1)},
+ {Named.of("user type", new TypeCastExpression(0, 8, "?::foo_type", parameterMarker, "foo_type")), Collections.singletonList(1)},
+ {Named.of("nested cast", new TypeCastExpression(0, 14, "?::int4::int4", integerCast, "int4")), Collections.singletonList(1)},
+ {Named.of("string literal", new TypeCastExpression(0, 8, "'01'::int4", new LiteralExpressionSegment(0, 3, "01"), "int4")), Collections.emptyList()},
+ {Named.of("decimal literal", new TypeCastExpression(0, 8, "1.5::int4", new LiteralExpressionSegment(0, 2, BigDecimal.valueOf(15, 1)), "int4")), Collections.emptyList()},
+ {Named.of("long literal", new TypeCastExpression(0, 8, "2147483648::int4", new LiteralExpressionSegment(0, 9, 2147483648L), "int4")), Collections.emptyList()},
+ {Named.of("null literal", new TypeCastExpression(0, 8, "null::int4", new LiteralExpressionSegment(0, 3, null), "int4")), Collections.emptyList()}};
+ }
+
+ @Test
+ void assertCreateShardingConditionsWithIntegerParameterCastsInMultipleRows() {
+ List<Object> params = Arrays.asList("foo_first", 1, "foo_second", 2);
+ InsertValueContext firstRow = new InsertValueContext(Arrays.asList(
+ new TypeCastExpression(0, 8, "?::text", new ParameterMarkerExpressionSegment(0, 0, 0), "text"),
+ new TypeCastExpression(10, 18, "?::int4", new ParameterMarkerExpressionSegment(10, 10, 1), "int4")), params, 0);
+ InsertValueContext secondRow = new InsertValueContext(Arrays.asList(
+ new TypeCastExpression(20, 28, "?::text", new ParameterMarkerExpressionSegment(20, 20, 2), "text"),
+ new TypeCastExpression(30, 38, "?::int4", new ParameterMarkerExpressionSegment(30, 30, 3), "int4")), params, 2);
+ when(insertStatementContext.getInsertValueContexts()).thenReturn(Arrays.asList(firstRow, secondRow));
+ when(rule.findShardingColumn("foo_col_3", "foo_tbl")).thenReturn(Optional.of("foo_col_3"));
+ List<ShardingCondition> actual = shardingConditionEngine.createShardingConditions(insertStatementContext, params);
+ assertThat(actual.size(), is(2));
+ assertThat(((ListShardingConditionValue<?>) actual.get(0).getValues().get(0)).getValues(), is(Collections.singletonList(1)));
+ assertThat(actual.get(0).getValues().get(0).getParameterMarkerIndexes(), is(Collections.singletonList(1)));
+ assertThat(((ListShardingConditionValue<?>) actual.get(1).getValues().get(0)).getValues(), is(Collections.singletonList(2)));
+ assertThat(actual.get(1).getValues().get(0).getParameterMarkerIndexes(), is(Collections.singletonList(3)));
+ }
+
@Test
void assertCreateShardingConditionsSelectStatement() {
when(insertStatementContext.getGeneratedKeyContext()).thenReturn(Optional.empty());
diff --git a/test/e2e/sql/src/test/resources/cases/dml/e2e-dml-insert.xml b/test/e2e/sql/src/test/resources/cases/dml/e2e-dml-insert.xml
index fc33332ebd9..911281c4416 100644
--- a/test/e2e/sql/src/test/resources/cases/dml/e2e-dml-insert.xml
+++ b/test/e2e/sql/src/test/resources/cases/dml/e2e-dml-insert.xml
@@ -31,11 +31,10 @@
<assertion parameters="4:int, 4:int, insert:String, test:String" expected-data-file="insert_for_order_4.xml" />
</test-case>
- <!-- TODO Fix https://github.com/apache/shardingsphere/issues/23764 -->
- <!-- <test-case sql="INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08')" db-types="PostgreSQL,openGauss" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
- <!-- <assertion parameters="1:int, 1:int, insert:String" expected-data-file="insert_for_order_1.xml" />-->
- <!-- <assertion parameters="2:int, 2:int, insert:String" expected-data-file="insert_for_order_2.xml" />-->
- <!-- </test-case>-->
+ <test-case sql="INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?::int4, ?, ?, 1, 'test', '2017-08-08')" db-types="PostgreSQL,openGauss" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1:int, 1:int, insert:String" expected-data-file="insert_for_order_1.xml" />
+ <assertion parameters="2:int, 2:int, insert:String" expected-data-file="insert_for_order_2.xml" />
+ </test-case>
<test-case sql="INSERT INTO t_order (order_id, user_id, status, merchant_id, remark, creation_date) VALUES (?, ?, ?::char(10), 1, 'test', '2017-08-08')" db-types="PostgreSQL,openGauss" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1:int, 1:int, insert:String" expected-data-file="insert_for_order_1.xml" />