Shardingsphere: "No value specified for parameter" On INSERT

[ieu]

We have application run on MySQL 8.0 and attempt to take advantages of its JSON feature, and come across this issue unluckily.

Which version of ShardingSphere did you use?

4.1.1

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

ShardingSphere-JDBC

Expected behavior

INSERT successfully

Actual behavior

An exception was thrown saying "No value specified for parameter"

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Prepare a table with column in type JSON,
2. INSERT INTO that table with value constructed with JSON functions using PreparedStatement.

Example codes for reproduce this issue (such as a github link).

The table to test with:

CREATE TABLE `post`
(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `title` VARCHAR(80),
    `content` TEXT,
    `tags` JSON,
    `extra` JSON
);

User code that caused exception:

jdbcOperations.update(
        "INSERT `post`(`title`, `extra`) VALUES (:title, JSON_OBJECT('contact', :contact))",
        new MapSqlParameterSource()
                .addValue("title", "ShardingSphere On the Start")
                .addValue("contact", "john_smith")
);

Another example that caused exception:

jdbcOperations.update(
        "INSERT `post`(`title`, `tags`) VALUES (:title, JSON_ARRAY(:tags))",
        new MapSqlParameterSource()
                .addValue("title", "ShardingSphere On the Start")
                .addValue("tags", Arrays.asList("java", "database"))
);

Stack Trace:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT `post`(`title`, `extra`) VALUES (?, JSON_OBJECT('contact', ?))]; nested exception is java.sql.SQLException: No value specified for parameter 2
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:883) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:321) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_261]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_261]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_261]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_261]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.doInvoke(ApplicationListenerMethodAdapter.java:305) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.processEvent(ApplicationListenerMethodAdapter.java:190) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.onApplicationEvent(ApplicationListenerMethodAdapter.java:153) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:404) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:361) ~[spring-context-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.boot.context.event.EventPublishingRunListener.running(EventPublishingRunListener.java:108) ~[spring-boot-2.3.5.RELEASE.jar:2.3.5.RELEASE]
    at org.springframework.boot.SpringApplicationRunListeners.running(SpringApplicationRunListeners.java:77) ~[spring-boot-2.3.5.RELEASE.jar:2.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:330) [spring-boot-2.3.5.RELEASE.jar:2.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) [spring-boot-2.3.5.RELEASE.jar:2.3.5.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.3.5.RELEASE.jar:2.3.5.RELEASE]
Caused by: java.sql.SQLException: No value specified for parameter 2
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1115) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:45) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:41) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82) ~[sharding-core-execute-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58) ~[sharding-core-execute-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97) ~[shardingsphere-executor-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93) ~[shardingsphere-executor-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76) ~[shardingsphere-executor-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68) ~[sharding-core-execute-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51) ~[sharding-core-execute-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.executeUpdate(PreparedStatementExecutor.java:123) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeUpdate(ShardingPreparedStatement.java:133) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    ... 22 common frames omitted

[tristaZero]

@ieu Hi thanks for the detailed description.

Hi @strongduanmu

bad SQL grammar [INSERT post(title, extra) VALUES (?, JSON_OBJECT('contact', ?))]; nested exception is java.sql.SQLException: No value specified for parameter 2

This exception info looks familiar, I guess we have fixed on the master branch, right？

[strongduanmu]

@ieu Hi thanks for the detailed description.

Hi @strongduanmu

bad SQL grammar [INSERT post(title, extra) VALUES (?, JSON_OBJECT('contact', ?))]; nested exception is java.sql.SQLException: No value specified for parameter 2

This exception info looks familiar, I guess we have fixed on the master branch, right？

@tristaZero This exception looks a little different from the previous question, I will test and confirm it. 😀

[strongduanmu]

@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

@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

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.

/**
 * 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:

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

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

[tristaZero]

@strongduanmu

I appreciated this debug explanation. :)
I suppose we have to fix this new issue in the next release.
Speaking of the solution you mentioned, I agree that we need to count the parameters in JSON_OBJECT(contact, ?). However I am unsure whether we need a new ExpressionProjectionSegment containing Collection<ExpressionSegment> or add Collection<ExpressionSegment> to the existing ExpressionProjectionSegment.

For me I prefer a new expressionSegment since String text and subExpressions could not have a particular value meanwhile. It also looks like the existing ExpressionProjectionSegment has no special follow-up handling and just to store the complex parsing content in text member.

As @jingshanglu did substantial work on expression parsing, let us listen to what he thinks about this one. :)

[jingshanglu]

@strongduanmu @tristaZero I think it's better to use a new subclass of ExpressionSegment to describe functions.Becausel, functions not only exist in projection. There are two ways to define a function expression. One is to see the function function through the class name,like that:

class CharFunction implements ExpressionSegment {
    List<ExpressionSegment> parameters;
}

and the other is to know the function function function through the attribute in the class, like that:

class Function implements ExpressionSegment {
    String/enum  type/methodName;
    List<ExpressionSegment> parameters;

According our sence, we choose one of them.

[strongduanmu]

@jingshanglu I prefer the second way, which seems more general.

[strongduanmu]

After discussing with @jingshanglu , he will be responsible for the optimization of this issue.