<p dir="auto">Phenomena:</p>
<p dir="auto">In version 4.0.0-RC1, when one field in the data I inserted was null, the automatically generated primary key ID was assigned to other fields. For example, I want to insert INSERT INTO user (name, remark, age) VALUES ("test", "null, 18"), after sharding, it becomes INSERT INTO user (name, remark, age, id) VALUES ("test", "18", "automatically generated primary key id", null)</p>
<p dir="auto">Analysis:</p>
<p dir="auto">The <strong>getCurrentIndex</strong> method in the <strong>InsertOptimizeResultUnit</strong> class has problems, and judgment is problematic when obtaining the current index subscript. There is no need to recalculate the current table below. The current scale is known by the number of fields in front of it.</p>
<p dir="auto">Which version of ShardingSphere did you use?<br>
4.0.0-RC1</p>
<p dir="auto">Which project did you use? Sharding-JDBC or Sharding-Proxy?<br>
Sharding-JDBC</p>
<p dir="auto">sql<br>
INSERT INTO <code class="notranslate">customer_account</code>(<code class="notranslate">group_id</code>, <code class="notranslate">customer_id</code>, <code class="notranslate">channel</code>, <code class="notranslate">recharge_balance</code>, <code class="notranslate">gift_balance</code>, <code class="notranslate">total_recharge_amount</code>, <code class="notranslate">total_gift_amount</code>, <code class="notranslate">version</code>, <code class="notranslate">query_password</code>, <code class="notranslate">query_salt</code>, <code class="notranslate">query_hash_type</code>, <code class="notranslate">pay_password</code>, <code class="notranslate">pay_salt</code>, <code class="notranslate">pay_hash_type</code>, <code class="notranslate">extra</code>,<code class="notranslate">deleted_at</code>) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);</p>
<p dir="auto">Expected behavior<br>
INSERT INTO <code class="notranslate">customer_account</code>(<code class="notranslate">group_id</code>, <code class="notranslate">customer_id</code>, <code class="notranslate">channel</code>, <code class="notranslate">recharge_balance</code>, <code class="notranslate">gift_balance</code>, <code class="notranslate">total_recharge_amount</code>, <code class="notranslate">total_gift_amount</code>, <code class="notranslate">version</code>, <code class="notranslate">query_password</code>, <code class="notranslate">query_salt</code>, <code class="notranslate">query_hash_type</code>, <code class="notranslate">pay_password</code>, <code class="notranslate">pay_salt</code>, <code class="notranslate">pay_hash_type</code>, <code class="notranslate">extra</code>,<code class="notranslate">deleted_at</code>) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);</p>
<p dir="auto">Actual behavior<br>
INSERT INTO <code class="notranslate">customer_account_0</code> (group_id, customer_id, channel, recharge_balance, gift_balance, total_recharge_amount, total_gift_amount, version, query_password, query_salt, query_hash_type, pay_password, pay_salt, pay_hash_type, extra, deleted_at, id) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, 'null', 'null', 356386280105312257)</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&lt;version&gt;4.0.0-RC1&lt;/version&gt;
10:31:50.527 [main] INFO ShardingSphere-SQL - Logic SQL:  INSERT INTO `customer_account`(`group_id`, `customer_id`, `channel`, `recharge_balance`, `gift_balance`, `total_recharge_amount`, `total_gift_amount`, `version`, `query_password`, `query_salt`, `query_hash_type`, `pay_password`, `pay_salt`, `pay_hash_type`, `extra`,`deleted_at`) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);
10:31:50.527 [main] INFO ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=customer_account, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=customer_id, tableName=customer_account), operator=EQUAL, compareOperator=null, positionValueMap={0=9223372036854775801}, positionIndexMap={})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=customer_account, quoteCharacter=BACK_QUOTE, schemaNameLength=0), SQLToken(startIndex=31)], parametersIndex=0, logicSQL= INSERT INTO `customer_account`(`group_id`, `customer_id`, `channel`, `recharge_balance`, `gift_balance`, `total_recharge_amount`, `total_gift_amount`, `version`, `query_password`, `query_salt`, `query_hash_type`, `pay_password`, `pay_salt`, `pay_hash_type`, `extra`,`deleted_at`) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[group_id, customer_id, channel, recharge_balance, gift_balance, total_recharge_amount, total_gift_amount, version, query_password, query_salt, query_hash_type, pay_password, pay_salt, pay_hash_type, extra, deleted_at], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@44ed0a8f, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@32177fa5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@a96d56c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@6ab4a5b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@2abe9173, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@235d29d6, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@1fdca564, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@43f9dd56, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@1d12e953, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@57cb70be, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@2d4608a6, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@20d87335, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@2a8a4e0c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@26c89563, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@3bd6ba24, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@58f437b0])])
10:31:50.528 [main] INFO ShardingSphere-SQL - Actual SQL: ds0 :::  INSERT INTO `customer_account_0` (group_id, customer_id, channel, recharge_balance, gift_balance, total_recharge_amount, total_gift_amount, version, query_password, query_salt, query_hash_type, pay_password, pay_salt, pay_hash_type, extra, deleted_at, id) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, 'null', 'null', 356386280105312257)
Exception in thread &quot;main&quot; com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'null' for column 'deleted_at' at row 1
"><pre class="notranslate"><code class="notranslate">&lt;version&gt;4.0.0-RC1&lt;/version&gt;
10:31:50.527 [main] INFO ShardingSphere-SQL - Logic SQL:  INSERT INTO `customer_account`(`group_id`, `customer_id`, `channel`, `recharge_balance`, `gift_balance`, `total_recharge_amount`, `total_gift_amount`, `version`, `query_password`, `query_salt`, `query_hash_type`, `pay_password`, `pay_salt`, `pay_hash_type`, `extra`,`deleted_at`) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);
10:31:50.527 [main] INFO ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=customer_account, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=customer_id, tableName=customer_account), operator=EQUAL, compareOperator=null, positionValueMap={0=9223372036854775801}, positionIndexMap={})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=customer_account, quoteCharacter=BACK_QUOTE, schemaNameLength=0), SQLToken(startIndex=31)], parametersIndex=0, logicSQL= INSERT INTO `customer_account`(`group_id`, `customer_id`, `channel`, `recharge_balance`, `gift_balance`, `total_recharge_amount`, `total_gift_amount`, `version`, `query_password`, `query_salt`, `query_hash_type`, `pay_password`, `pay_salt`, `pay_hash_type`, `extra`,`deleted_at`) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, null,null);), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[group_id, customer_id, channel, recharge_balance, gift_balance, total_recharge_amount, total_gift_amount, version, query_password, query_salt, query_hash_type, pay_password, pay_salt, pay_hash_type, extra, deleted_at], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@44ed0a8f, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@32177fa5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@a96d56c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@6ab4a5b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@2abe9173, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@235d29d6, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@1fdca564, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@43f9dd56, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@1d12e953, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@57cb70be, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@2d4608a6, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@20d87335, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@2a8a4e0c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLNumberExpression@26c89563, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@3bd6ba24, org.apache.shardingsphere.core.parse.old.parser.expression.SQLTextExpression@58f437b0])])
10:31:50.528 [main] INFO ShardingSphere-SQL - Actual SQL: ds0 :::  INSERT INTO `customer_account_0` (group_id, customer_id, channel, recharge_balance, gift_balance, total_recharge_amount, total_gift_amount, version, query_password, query_salt, query_hash_type, pay_password, pay_salt, pay_hash_type, extra, deleted_at, id) VALUES ('0', '9223372036854775801', 'default', 0, 0, 0, 0, 0, '', '', 0, '', '', 0, 'null', 'null', 356386280105312257)
Exception in thread "main" com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'null' for column 'deleted_at' at row 1

</code></pre></div>