No value specified for parameter exception when sql is 'INSERT INTO tableName *** ON CONFLICT *** DO UPDATE set ** WHERE ***'

[shijie-328931589]

Bug Report

Before report a bug, make sure you have:

Which version of ShardingSphere did you use?

5.4.1

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

ShardingSphere-JDBC

Expected behavior

Actual behavior

Caused by: org.postgresql.util.PSQLException: 未设定参数值 3 的内容。
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:275)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:307)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy122.execute(Unknown Source)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:490)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:486)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:83)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:62)

Reason analyze (If you can)

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

use postgresql,the sql is like this:
INSERT INTO tableName ( c1, c2, c3 ) VALUES ( ?, now(), ? ) ON CONFLICT ( c1, c3 ) DO UPDATE set c2 = now() WHERE user_id= ?
when execute this sql, appear exception

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

[terrymanu]

What is the code for set parameters?

[shijie-328931589]

    @Insert(" INSERT INTO user_little_secretary_read ( user_id, read_time, type ) " +
        "VALUES" +
        "( #{userId}, now(),  #{type} ) " +
        "ON CONFLICT ( user_id, type ) DO " +
        "UPDATE " +
        " set read_time = now() " +
        "WHERE " +
        "  user_id = #{userId}   " +
        " AND type = #{type} ")
    void insertUserLittleSecretaryRead(@Param("userId") long userId, @Param("type") long type);

[terrymanu]

The issue involves other third-party dependencies, but our focus is solely on ShardingSphere itself. Since ShardingSphere implements the JDBC interface, standard applications should be functional. We wish to allocate more effort towards enhancing the current version, and therefore will no longer handle such issues. Please read the documentation or provide more effective information when submitting an issue.

[shijie-328931589]

Have the same problem when use ShardingSphereDataSource. the code:

    String sql = " INSERT INTO user_little_secretary_read ( user_id, read_time, type )  VALUES ( ?, now(),  ? ) ON CONFLICT ( user_id, type ) DO UPDATE " +
            " set read_time = now() WHERE user_little_secretary_read.user_id = ? AND user_little_secretary_read.type = ? ";
    ShardingSphereDataSource dataSource = (ShardingSphereDataSource)RoutingDataSourceUtils.getCurrentShardingInfo().getRealDataSource();
    try {
        try(Connection connection = dataSource.getConnection()){
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setLong(1, userId);
            statement.setLong(2, type);
            statement.setLong(3, userId);
            statement.setLong(4, type);
            statement.execute();
        }
    }catch (Exception e){
        throw new RuntimeException(e);
    }

[terrymanu]

I notice the SQL is different in your 2 messages:

INSERT INTO tableName ( c1, c2, c3 ) VALUES ( ?, now(), ? ) ON CONFLICT ( c1, c3 ) DO UPDATE set c2 = now() WHERE user_id= ?

The placeholder count is 3.

INSERT INTO user_little_secretary_read ( user_id, read_time, type )  VALUES ( ?, now(),  ? ) ON CONFLICT ( user_id, type ) DO UPDATE set read_time = now() WHERE user_little_secretary_read.user_id = ? AND user_little_secretary_read.type = ? 

The placeholder count is 4.

Can you fill the error message for each SQL?

[shijie-328931589]

sorry, I mean this type of sql has the same problem:
"ON CONFLICT DO UPDATE *** Where *** "
my real sql is second , and the exception is :
Caused by: org.postgresql.util.PSQLException: 未设定参数值 3 的内容。
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:275)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:307)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy122.execute(Unknown Source)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:490)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:486)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:83)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:62)

[omkar-shitole]

Hello,
I investigated this particular bug report.

It is a bug. I successfully reproduced it. Also, I would like to let you know that this particular query -

• The query executes successfully with INSERT INTO & VALUES when no WHERE statement is provided.
• The query fails when extra parameter markers/placeholders are provided beyond those assigned to VALUES.
• The WHERE statement works well with the INSERT INTO&SELECT statements.

I reviewed the ANTLR rules related to this issue, and they seem to be correctly implemented.

Any suggestions regarding the scope of the investigation and fix would be greatly appreciated.

Also, I would request you to assign this issue to me.

[omkar-shitole]

In this PR ,
I have added e2e test cases to cover similar and related queries for the ON CONFLICT clause, as highlighted in the referenced issue.
Additionally, I noticed that the current implementation fails to execute ON CONFLICT ... DO NOTHING, resulting in the following error:

Click to expand error stack trace

'''Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18) at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.PostgreSQLStatementVisitor.visitOptOnConflict(PostgreSQLStatementVisitor.java:712)Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18) at org.apache.shardingsphere.sql.parser.postgresql.visitor.statement.PostgreSQLStatementVisitor.visitOptOnConflict(PostgreSQLStatementVisitor.java:712)'''

This PR addresses both issues by fixing the error and ensures that queries with DO NOTHING are successfully executed.

I kindly request you to proceed with the review and merge process.