Selecting a large DECIMAL value fails after a few times

[mikaelstaldal]

I'm submitting a ...

• bug report
• feature request

Describe the issue
After inserting a very large DECIMAL value into a table, selecting it repeatedly fails after about five times. The problem did not occur in driver version 42.2.11, but exist in the latest version (42.5.1).

Driver Version?
42.5.1

Java Version?
11

OS Version?
Ubuntu Desktop 22.04

PostgreSQL Version?
14.5

To Reproduce

import org.junit.jupiter.api.Test;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static org.junit.jupiter.api.Assertions.*;

class PostgresTest {
    static final String DB_URL = ""; // TODO set DB URL here
    static final String DB_USERNAME = ""; // TODO set DB user here
    static final String DB_PASSWORD = ""; // TODO set DB password here

    void testSelectDecimalBug() throws SQLException {
        BigDecimal limit = new BigDecimal(BigInteger.TEN.pow(131072).subtract(BigInteger.ONE));

        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD)) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("DROP TABLE IF EXISTS BigDecimalTest");
                statement.execute("CREATE TABLE BigDecimalTest(v DECIMAL NOT NULL);");
            }

            try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO BigDecimalTest (v) VALUES (?);")) {
                preparedStatement.setBigDecimal(1, limit);
                preparedStatement.execute();
            }

            for (int i = 0; i < 100; i++) {
                System.out.println("i = " + i);
                try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT v FROM BigDecimalTest;")) {
                    try (ResultSet resultSet = preparedStatement.executeQuery()) {
                        assertTrue(resultSet.next());
                        assertEquals(limit, resultSet.getBigDecimal(1));
                        assertFalse(resultSet.next());
                    }
                }
            }
        }
    }
}

Expected behaviour
It should work to select the value any number of times (as it did in driver version 42.2.11), but with driver version 42.5.1 it fails after about five times with:

java.lang.IllegalArgumentException: invalid length of bytes "numeric" value
	at org.postgresql.util.ByteConverter.numeric(ByteConverter.java:157)
	at org.postgresql.util.ByteConverter.numeric(ByteConverter.java:111)
	at org.postgresql.jdbc.PgResultSet.getNumeric(PgResultSet.java:2664)
	at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:2639)
	at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:434)

Logs
Did not see anything about this in the Postgres server logs.

[davecramer]

interesting. The 5 times is because it switches to binary instead of text.
Thanks for the report

[mikaelstaldal]

Is it normal that it switches from text to binary for the same query after a while?

[davecramer]

Yes, from the docs https://jdbc.postgresql.org/documentation/query/

When PreparedStatement API is used, ResultSet switches to binary mode after five query executions (this default is set by the prepareThreshold connection property, see [Server Prepared Statements](https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements). This may cause unexpected behaviour when some methods are called. For example, results on method calls such as getString() on non-string data types, while logically equivalent, may be formatted differently after execution exceeds the set prepareThreshold when conversion to object method switches to the method with a return type matching the return mode.

[mikaelstaldal]

Possibly related issue: #2696

[mikaelstaldal]

The problem does not occur when setting prepareThreshold=0.

[davecramer]

Yes, this disables the switch.

[bokken]

The problem is that the 2 bytes for length are being treated as a signed short, but should be treated as unsigned.

https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/util/ByteConverter.java#L131

[mikaelstaldal]

Seems like binaryTransfer=false also make this problem not occur. What's the difference between binaryTransfer=false and prepareThreshold=0?

[davecramer]

binaryTransfer disables all binary transfer
prepareThreshold disables named server statement which will enable binary transfer if possible.

Good question though

[davecramer]

I've clarified it a bit with #2698

[mikaelstaldal]

🎉