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)