15134

[dissdic]

Fixes #15134.

Changes proposed in this pull request:

• do type conversion when executing setObject method of ShardingSpherePreparedStatement instance instead of just setting the Object to the placeholder in a SQL despite the target type is given specificly

[TeslaCN]

Hi @dissdic We may need further discussion in issue. Convert this PR to draft for now.

[terrymanu]

@dissdic Hi, any update?

[terrymanu]

@dissdic The progress of this PR has not been upgraded for a long time. If there is no update, I will close it soon.

[dissdic]

here is the stacktrace info I got :

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT kkk.id as id,kkk.name as name,kkk.domain_name as domain_name,kkk."version" as "version",kkk.insert_user as insert_user,kkk.insert_date as insert_date,kkk.update_user as update_user,kkk.update_date as update_date FROM wms_kkk as kkk  WHERE kkk.id = ? ]; nested exception is org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
  建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
  位置：256
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.translateException(JdbcTemplateUtil.java:79)
at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:61)
at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:733)
at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:660)
at logwire.web.service.query.sql.DynamicSqlSelect.forIterator(DynamicSqlSelect.java:443)
at logwire.web.service.query.sql.DynamicSqlSelect.getSingleRow(DynamicSqlSelect.java:601)
... 54 common frames omitted
Caused by: org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
  建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
  位置：256
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.driver.executor.callback.impl.PreparedStatementExecuteQueryCallback.executeQuery(PreparedStatementExecuteQueryCallback.java:40)
at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:44)
at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:36)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.executeQuery(DriverJDBCExecutor.java:73)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:212)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:181)
at logwire.web.service.query.sql.DynamicSqlSelect.lambda$doSelect$15(DynamicSqlSelect.java:725)
at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:46)
... 58 common frames omitted

for setting values in to a SQL,we used the setValues(PreparedStatement ps) in PreparedStatementSetter,as you can see in the image below:

and the implementation of this abstract method is like this:

and the code of the another setValue method present in the sceenshot is:

the core code is:

field.setValueIgnoreCheck(ps, index + 1, values.get(i), this.dialect);

which present as:

the code of the method setValueInPreparedStatement shown in the sceenshot is:

despite these execution processes,I got the explicit detail info of this method while debugging:

this shows that eventually the setValue of ShardingSpherePreparedStatement was invoked.and I got the Exception written in the beginning.

[dissdic]

Sorry, I've actually complemented the description and the detail info before in issue #15134.

[terrymanu]

OK, please continue to discuss.

[dissdic]

yes, so I checked out the source code of the method setObject of the ShardingSpherePreparedStatement instance,and found out that it didn't do any type conversion, I natually thought this would be the reason why the Exception occured when executing the SQL, considering the implementation PgPreparedStatement providered by postgresql jdbc driver has done the type conversion when the method setObject invoked.

[dissdic]

any suggestions?

[dissdic]

OK, please continue to discuss.

any suggestions?

[dissdic]

OK, please continue to discuss.

any suggestions?pls

[dissdic]

OK, please continue to discuss. have you guys checkout the issue?

[strongduanmu]

Since this pr has long time no response, I will close it.