Query PostgreSQL money type cause ERROR: Bad value for type double : -92,233,720,368,547,758.08

[TeslaCN]

Bug Report

Which version of ShardingSphere did you use?

master - a4c01712aba7f17f3f4fe8264aaf4c19cb572432

This issue may affect all version of ShardingSphere-Proxy.

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

ShardingSphere-Proxy PostgreSQL

Expected behavior

Query worked.

Expected output:

-$92,233,720,368,547,758.08

Actual behavior

Client:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: Bad value for type double : -92,233,720,368,547,758.08
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

Proxy log:

[INFO ] 2023-01-11 15:13:04.143 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select ?::money
[INFO ] 2023-01-11 15:13:04.144 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select ?::money ::: [-92233720368547758.08]
[ERROR] 2023-01-11 15:13:05.561 [Connection-4-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
org.postgresql.util.PSQLException: Bad value for type double : -92,233,720,368,547,758.08
    at org.postgresql.jdbc.PgResultSet.toDouble(PgResultSet.java:3314)
    at org.postgresql.jdbc.PgResultSet.getDouble(PgResultSet.java:2652)
    at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:232)
    at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2936)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
    at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.getValue(JDBCStreamQueryResult.java:106)
    at org.apache.shardingsphere.infra.merge.result.impl.transparent.TransparentMergedResult.getValue(TransparentMergedResult.java:43)
    at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.getRowData(DatabaseCommunicationEngine.java:348)
    at org.apache.shardingsphere.proxy.backend.handler.data.impl.UnicastDatabaseBackendHandler.getRowData(UnicastDatabaseBackendHandler.java:88)
    at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.Portal.nextPacket(Portal.java:169)
    at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.Portal.execute(Portal.java:145)
    at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.execute.PostgreSQLComExecuteExecutor.execute(PostgreSQLComExecuteExecutor.java:44)
    at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.execute.PostgreSQLComExecuteExecutor.execute(PostgreSQLComExecuteExecutor.java:34)
    at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
    at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
    at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)

Reason analyze (If you can)

Proxy using getObject to retrieve value, while money type should be retrieve by getString.

https://github.com/apache/shardingsphere/blob/a4c01712aba7f17f3f4fe8264aaf4c19cb572432/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/DatabaseCommunicationEngine.java#L348

PostgreSQL JDBC related issue: https://github.com/pgjdbc/pgjdbc/issues/425

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

The following code worked if connected to PostgreSQL directly.

try (PreparedStatement preparedStatement = connection.prepareStatement("select ?::money")) {
preparedStatement.setObject(1, "-92233720368547758.08");
try (ResultSet resultSet = preparedStatement.executeQuery()) {
    resultSet.next();
    System.out.println(resultSet.getString(1));
}

[TeslaCN]

This issue could be temporarily avoided by casting money to varchar. The following code worked for latest Proxy.

try (PreparedStatement preparedStatement = connection.prepareStatement("select ?::money::varchar")) {
    preparedStatement.setObject(1, "-92233720368547758.08");
    try (ResultSet resultSet = preparedStatement.executeQuery()) {
        resultSet.next();
        System.out.println(resultSet.getString(1));
    }
}