<h2 dir="auto">Bug Report</h2>
<p dir="auto"><strong>For English only</strong>, other languages will not accept.</p>
<p dir="auto">Before report a bug, make sure you have:</p>
<ul dir="auto">
<li>Searched open and closed <a href="https://github.com/apache/shardingsphere/issues">GitHub issues</a>.</li>
<li>Read documentation: <a href="https://shardingsphere.apache.org/document/current/en/overview" rel="nofollow">ShardingSphere Doc</a>.</li>
</ul>
<p dir="auto">Please pay attention on issues you submitted, because we maybe need more details.<br>
If no response anymore and we cannot reproduce it on current information, we will <strong>close it</strong>.</p>
<p dir="auto">Please answer these questions before submitting your issue. Thanks!</p>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">5.0.0</p>
<h3 dir="auto">Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?</h3>
<p dir="auto">ShardingSphere-JDBC and ShardingSphere-Proxy</p>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">auto sharding algorithm with mod sharding algorithm, 6 databases are used, every database has 99 tables, so the sharding count is 594</p>
<p dir="auto">when insert a record of table tb_test_info with id 701450883798396931<br>
701450883798396931 % 6 = 1,  the datasource is ds_1<br>
701450883798396931 % 594 = 1, the actual table is tb_test_info_1</p>
<p dir="auto">the record be insert into table ds_1.tb_test_info_1</p>
<h3 dir="auto">Actual behavior</h3>
<p dir="auto">more thant one datanodes are found and exception is thrown, the record failed to insert into table ds_1.tb_test_info_1</p>
<p dir="auto">Caused by: java.lang.IllegalStateException: Insert statement does not support sharding table routing to multiple data nodes.<br>
at com.google.common.base.Preconditions.checkState(Preconditions.java:508)<br>
at org.apache.shardingsphere.sharding.route.engine.validator.dml.impl.ShardingInsertStatementValidator.postValidate(ShardingInsertStatementValidator.java:105)<br>
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.lambda$createRouteContext$1(ShardingSQLRouter.java:57)<br>
at java.util.Optional.ifPresent(Optional.java:159)<br>
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:57)<br>
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:44)<br>
at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:62)<br>
at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:53)<br>
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.route(KernelProcessor.java:54)<br>
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:46)<br>
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:378)<br>
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:286)</p>
<h3 dir="auto">Reason analyze (If you can)</h3>
<p dir="auto">postValidate() method found more than one data node in file  ShardingInsertStatementValidator.java</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="public void postValidate(final ShardingRule shardingRule, final SQLStatementContext&lt;InsertStatement&gt; sqlStatementContext,
                         final RouteContext routeContext, final ShardingSphereSchema schema) {
    Optional&lt;SubquerySegment&gt; insertSelect = sqlStatementContext.getSqlStatement().getInsertSelect();
    if (insertSelect.isPresent() &amp;&amp; shardingConditions.isNeedMerge()) {
        boolean singleRoutingOrSameShardingCondition = routeContext.isSingleRouting() || shardingConditions.isSameShardingCondition();
        Preconditions.checkState(singleRoutingOrSameShardingCondition, &quot;Subquery sharding conditions must be same with primary query.&quot;);
    }
    String tableName = sqlStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
    if (!routeContext.isSingleRouting() &amp;&amp; !shardingRule.isBroadcastTable(tableName)) {
        boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -&gt; dataNodes.size() == 1);
        Preconditions.checkState(isSingleDataNode, &quot;Insert statement does not support sharding table routing to multiple data nodes.&quot;);
    }
}"><pre class="notranslate"><code class="notranslate">public void postValidate(final ShardingRule shardingRule, final SQLStatementContext&lt;InsertStatement&gt; sqlStatementContext,
                         final RouteContext routeContext, final ShardingSphereSchema schema) {
    Optional&lt;SubquerySegment&gt; insertSelect = sqlStatementContext.getSqlStatement().getInsertSelect();
    if (insertSelect.isPresent() &amp;&amp; shardingConditions.isNeedMerge()) {
        boolean singleRoutingOrSameShardingCondition = routeContext.isSingleRouting() || shardingConditions.isSameShardingCondition();
        Preconditions.checkState(singleRoutingOrSameShardingCondition, "Subquery sharding conditions must be same with primary query.");
    }
    String tableName = sqlStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
    if (!routeContext.isSingleRouting() &amp;&amp; !shardingRule.isBroadcastTable(tableName)) {
        boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -&gt; dataNodes.size() == 1);
        Preconditions.checkState(isSingleDataNode, "Insert statement does not support sharding table routing to multiple data nodes.");
    }
}
</code></pre></div>
<p dir="auto">shardingsphere proxy  create tables using auto table algorithm and mod algorithm<br>
tables will be create in format tb_test_info_{0..593} in different datasource<br>
eg.<br>
ds1.tb_test_info_1<br>
ds3.tb_test_info_21<br>
ds5.tb_test_info_11</p>
<p dir="auto">table is found by doSharding() method in ModShardingAlgorithm.java</p>
<p dir="auto">public String doSharding(final Collection availableTargetNames, final PreciseShardingValue&lt;Comparable&lt;?&gt;&gt; shardingValue) {<br>
for (String each : availableTargetNames) {<br>
if (each.endsWith(String.valueOf(getLongValue(shardingValue.getValue()) % shardingCount))) {<br>
return each;<br>
}<br>
}<br>
return null;<br>
}</p>
<p dir="auto">it compare and find the first table end with a number in string format<br>
the table is in format tb_test_info_{0..593} ,<br>
701450883798396931 % 594 = 1<br>
so in a datasource, the firtst table  end with 1 will be  selected</p>
<p dir="auto">in case shardingsphere proxy create table for auto table algorithm and mod algorithm in format tb_test_info_{0..593}<br>
"_" should be use in endsWith coompare, as more than one tables can end with the same number</p>
<p dir="auto">if (each.endsWith("_".contact(String.valueOf(getLongValue(shardingValue.getValue()) % shardingCount)))) {<br>
return each;<br>
}</p>
<p dir="auto">method route0() in ShardingStandardRoutingEngine.java do sharding with loop of all datasources</p>
<p dir="auto">private Collection route0(final TableRule tableRule,<br>
final ShardingStrategy databaseShardingStrategy, final List databaseShardingValues,<br>
final ShardingStrategy tableShardingStrategy, final List tableShardingValues) {<br>
Collection routedDataSources = routeDataSources(tableRule, databaseShardingStrategy, databaseShardingValues);<br>
Collection result = new LinkedList&lt;&gt;();<br>
for (String each : routedDataSources) {<br>
result.addAll(routeTables(tableRule, each, tableShardingStrategy, tableShardingValues));<br>
}<br>
return result;<br>
}</p>
<p dir="auto">all datasource will be execute the routeTables method, table will be searched  so in this case, 6 datasources</p>
<p dir="auto">finally the follwing 3 table are select in different datasources<br>
ds_1.tb_test_info_1<br>
ds_3.tb_test_info_21<br>
ds_5.tb_test_info_11</p>
<p dir="auto">also it's not effective for auto table algorithm to query table in all datasource, it's better to find the exactly one datasource and only do sharding in the datasource,</p>
<h3 dir="auto">Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.</h3>
<p dir="auto">configs in spring boot starter for shardingsphere jdbc like below:</p>
<p dir="auto">spring:<br>
shardingsphere:<br>
rules:<br>
sharding:<br>
auto-tables:<br>
tb_test_info:<br>
actual-data-sources: ds_$-&gt;{0..5}<br>
sharding-strategy:<br>
standard:<br>
sharding-column: id<br>
sharding-algorithm-name: mod</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    sharding-algorithms:
      mod: 
        type: MOD
        props:
          sharding-count: 594"><pre class="notranslate"><code class="notranslate">    sharding-algorithms:
      mod: 
        type: MOD
        props:
          sharding-count: 594
</code></pre></div>
<p dir="auto">dist sql used in shardingsphere proxy</p>
<p dir="auto">CREATE SHARDING TABLE RULE tb_test_info (<br>
RESOURCES(ds_0,ds_1,ds_2,ds_3,ds_4,ds_5),<br>
SHARDING_COLUMN=id,<br>
TYPE(NAME=MOD,PROPERTIES("sharding-count"=594))<br>
);</p>
<p dir="auto">create table sql</p>
<p dir="auto">CREATE TABLE <code class="notranslate">tb_test_info</code> (<br>
<code class="notranslate">id</code> bigint NOT NULL ,<br>
<code class="notranslate">name</code> varchar(32) DEFAULT NULL ,<br>
PRIMARY KEY (<code class="notranslate">id</code>) USING BTREE<br>
)</p>
<p dir="auto">insert a record of table tb_test_info with id 701450883798396931</p>
<h3 dir="auto">Example codes for reproduce this issue (such as a github link).</h3>
<p dir="auto">For English only, other languages we will close it directly.</p>
<p dir="auto">Please answer these questions before submitting your issue. Thanks!</p>
<h3 dir="auto">Which version of Sharding-Sphere do you using?</h3>
<h3 dir="auto">Expected behavior</h3>
<h3 dir="auto">Actual behavior</h3>
<h3 dir="auto">Reason analyze</h3>
<h3 dir="auto">Steps to reproduce the behavior</h3>
<h3 dir="auto">Please provide the reproduce example codes (such as a github link).</h3>