[GitHub] [shardingsphere] strongduanmu edited a comment on issue #7998: "No value specified for parameter" On INSERT

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

strongduanmu edited a comment on issue #7998:
URL: https://github.com/apache/shardingsphere/issues/7998#issuecomment-721219274


   @tristaZero @kimmking @ieu I have tested this SQL on the master branch and got the same exception. The reason for the error is that we do not have a good support for the function expression in the `VALUES` ​​clause of the `INSERT` statement.
   
   Taking the SQL——`INSERT post(title, extra) VALUES (?, JSON_OBJECT(contact, ?))` as an example, the parameters in the values ​​clause will be parsed into  `ParameterMarkerExpressionSegment` and `ExpressionProjectionSegment`.
   
   When initializing `InsertValueContext`, only the parameters in `ParameterMarkerExpressionSegment` can calculate parameterCount normally, which leads to the loss of parameters in `ExpressionProjectionSegment`.
   
   MySQLStatementVisitor.java
   
   ```java
   @Override
   public final ASTNode visitRegularFunction(final RegularFunctionContext ctx) {
       if (null != ctx.completeRegularFunction()) {
           calculateParameterCount(ctx.completeRegularFunction().expr());
       }
       String text = ctx.start.getInputStream().getText(new Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
       return new ExpressionProjectionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), text);
   }
   
   // TODO :FIXME, sql case id: insert_with_str_to_date
   private void calculateParameterCount(final Collection<ExprContext> exprContexts) {
       for (ExprContext each : exprContexts) {
           visit(each);
       }
   }
   ```
   InsertValueContext.java
   ```java
   private int calculateParameterCount(final Collection<ExpressionSegment> assignments) {
       int result = 0;
       for (ExpressionSegment each : assignments) {
           if (each instanceof ParameterMarkerExpressionSegment) {
               result++;
           }
       }
       return result;
   }
   ```
   
   In order to solve this exception, I think that the sub-expression in the function expression should be parsed in the `MySQL Statement SQLVisitor` class, and the changes are as follows:
   
   First, add the `subExpressions` parameter in the `ExpressionProjectionSegment` class.
   
   ```java
   /**
    * Expression projection segment.
    */
   @Getter
   public final class ExpressionProjectionSegment implements ProjectionSegment, ComplexExpressionSegment, AliasAvailable {
       
       private final int startIndex;
       
       private final int stopIndex;
       
       private final String text;
       
       private final Collection<ExpressionSegment> subExpressions = new LinkedList<>();
   }
   ```
   
   Second, modify the visitor parsing processing logic:
   
   ```java
   @Override
   public final ASTNode visitRegularFunction(final RegularFunctionContext ctx) {
       String text = ctx.start.getInputStream().getText(new Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
       ExpressionProjectionSegment result = new ExpressionProjectionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), text);
       if (null != ctx.completeRegularFunction()) {
           result.getSubExpressions().addAll(calculateParameterCount(ctx.completeRegularFunction().expr()));
       }
       return result;
   }
   
   // TODO :FIXME, sql case id: insert_with_str_to_date
   private Collection<ExpressionSegment> calculateParameterCount(final Collection<ExprContext> exprContexts) {
       CollectionValue<ExpressionSegment> result = new CollectionValue<>();
       for (ExprContext each : exprContexts) {
           result.getValue().add((ExpressionSegment) visit(each));
       }
       return result.getValue();
   }
   ```
   
   Finally, adjust the `parameterCount` calculation logic in `InsertValueContext` class:
   
   ```java
   private int calculateParameterCount(final Collection<ExpressionSegment> assignments) {
       int result = 0;
       for (ExpressionSegment each : assignments) {
           if (each instanceof ParameterMarkerExpressionSegment) {
               result++;
           }
           if (each instanceof ExpressionProjectionSegment) {
               result += calculateParameterCount(((ExpressionProjectionSegment) each).getSubExpressions());
           }
       }
       return result;
   }
   ```


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