[GitHub] [shardingsphere] menghaoranss commented on issue #4784: Throw NPE when INSERT column and values mismatch

[GitBox <gi...@apache.org>]

menghaoranss commented on issue #4784:
URL: https://github.com/apache/shardingsphere/issues/4784#issuecomment-629004883


   @hk1997 ,I have tried to track this issue use sharidng-proxy with mysql :
   `yaml`:
   ```
   rules:
   - !!org.apache.shardingsphere.sharding.core.yaml.config.sharding.YamlShardingRuleConfiguration
     tables:
       t_order:
         actualDataNodes: ds_${0..1}.t_order_${0..1}
         tableStrategy:
           standard:
             shardingColumn: order_id
             shardingAlgorithm:
               type: INLINE
               props:
                 algorithm.expression: t_order_${order_id % 2}
         keyGenerator:
           type: SNOWFLAKE
           column: order_id
       t_order_item:
         actualDataNodes: ds_${0..1}.t_order_item_${0..1}
         tableStrategy:
           standard:
             shardingColumn: order_id
             shardingAlgorithm:
               type: INLINE
               props:
                 algorithm.expression: t_order_item_${order_id % 2}
         keyGenerator:
           type: SNOWFLAKE
           column: order_item_id
     bindingTables:
       - t_order,t_order_item
     defaultDatabaseStrategy:
       standard:
         shardingColumn: user_id
         shardingAlgorithm:
           type: INLINE
           props:
             algorithm.expression: ds_${user_id % 2}
     defaultTableStrategy:
       none:
   ```
   `table`:
   ```
   CREATE TABLE `t_order` (
     `order_id` bigint(20) NOT NULL,
     `user_id` int(11) NOT NULL,
     `status` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`order_id`)
   ) ENGINE=InnoDB DEFAULT
   
   ```
   `case`:
   ```
   mysql> insert into t_order(user_id, status) values(1);
   ERROR 1136 (21S01): Column count doesn't match value count at row 1
   
   mysql> insert into t_order(order_id, user_id, status) values(201,200);
   ERROR 1136 (21S01): Column count doesn't match value count at row 1
   
   mysql> insert into t_order(user_id, status) values(1, 2, '3');
   ERROR 10002 (C1000): 2Unknown exception: [null]
   
   mysql> insert into t_order values(201,200,2000,200);
   ERROR 10002 (C1000): 2Unknown exception: [null]
   
   mysql> insert into t_order values(201,200);
   Query OK, 1 row affected (6.66 sec)
   
   mysql> insert into t_order values(201);
   ERROR 1136 (21S01): Column count doesn't match value count at row 1
   
   ```
   there are 2 situations：
   - use default cloumns
      - values num > columns num ：NPE
      - values num = columns num - generatedKey num ：OK
      - values num < columns num - generatedKey num ：excute ERROR
      
   - designate columns
      - values num > columns num ：NPE
      - values num < columns num ：excute ERROR
   
   And , i have debuged the source code , this is the code where NPE occured: 
   
   ```
   InsertClauseShardingConditionEngine.class
   
   private ShardingCondition createShardingCondition(final String tableName, final Iterator<String> columnNames, final InsertValueContext insertValueContext, final List<Object> parameters) {
           ShardingCondition result = new ShardingCondition();
           SPITimeService timeService = new SPITimeService();
           for (ExpressionSegment each : insertValueContext.getValueExpressions()) {
               //NPE here
               String columnName = columnNames.next();
               if (shardingRule.isShardingColumn(columnName, tableName)) {
                   if (each instanceof SimpleExpressionSegment) {
                       result.getRouteValues().add(new ListRouteValue<>(columnName, tableName, Collections.singletonList(getRouteValue((SimpleExpressionSegment) each, parameters))));
                   } else if (ExpressionConditionUtils.isNowExpression(each)) {
                       result.getRouteValues().add(new ListRouteValue<>(columnName, tableName, Collections.singletonList(timeService.getTime())));
                   } else if (ExpressionConditionUtils.isNullExpression(each)) {
                       throw new ShardingSphereException("Insert clause sharding column can't be null.");
                   }
               }
           }
           return result;
       }
   ```
   
   I think some verification needs to be done after `SQLStatementContext` been instantiated：
   ```
   DataNodeRouter.class
   
   private RouteContext createRouteContext(final SQLStatement sqlStatement, final String sql, final List<Object> parameters) {
           try {
               SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(metaData.getSchema().getSchemaMetaData(), sql, parameters, sqlStatement);
               // maybe need check here
               return new RouteContext(sqlStatementContext, parameters, new RouteResult());
               // TODO should pass parameters for master-slave
           } catch (final IndexOutOfBoundsException ex) {
               return new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
           }
       }
   ```
   
   welcome to talk about here. 
   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org