Issue 1:
Phenomena:

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)

Analysis:

The getCurrentIndex method in the InsertOptimizeResultUnit 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.

Issue 2:
Which version of ShardingSphere did you use?

4.0.0-RC1

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

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);

Expected behavior

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);

Actual behavior

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)

<version>4.0.0-RC1</version>
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