createArrayOf fails if byte[][] erased to Object[]

[pingw33n]

Postgres driver: org.postgresql:postgresql:42.5.0

Reproducer:

@Testcontainers
public class PgTest {
    @Container
    PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(DockerImageName.parse("postgres:12-alpine"));

    @Test
    void test() throws Exception {
        try (var c = DriverManager.getConnection(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())) {
            c.createArrayOf("bytea", new Object[]{new byte[]{1, 2, 3}});
        }
    }
}

Throws:

java.lang.UnsupportedOperationException: byte[] nested inside Object[]

	at org.postgresql.jdbc.ArrayEncoding$15.appendArray(ArrayEncoding.java:1083)
	at org.postgresql.jdbc.ArrayEncoding$15.appendArray(ArrayEncoding.java:1045)
	at org.postgresql.jdbc.ArrayEncoding$AbstractArrayEncoder.toArrayString(ArrayEncoding.java:204)
	at org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1422)
	at org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1429)
	at ttt.PgTest.test(PgTest.java:19)

Original issue in JDBI: jdbi/jdbi#2109

The problem is that the code uses getComponentType() to select ArrayEncoder in ArrayEncoding.getArrayEncoder() which obviously fails for erased Object[] and falls back to using OBJECT_ARRAY which for some reason doesn't support encoding byte[] elements. I think it should be possible to addiitionally use the first element to detect element type falling back to OBJECT_ARRAY for empty arrays only.

[davecramer]

Interesting @bokken I believe you have some insight into this ?

[bokken]

There is discussion on #2226.
The specific carve out for byte[] is mentioned there as a use case which has never been supported, and the primary focus there was the regression from previous support.

I think it would work to use PGbytea.toPGString((byte[])array[i]) (presumably in quotes). The BYTEA_ARRAY does the string representation using HEX, perhaps that could be refactored out to be reusable

[davecramer]

So I guess the issues here is we don't know whether this is an array of bytes or a string?

[bokken]

we don't know whether this is an array of bytes or a string

There is some ambiguity there, but /assuming/ an array of bytea seems reasonable enough in this scenario.
Part of the question is why would one represent a byte[][] as an Object[], where the values are byte[]. A byte[][] is an Object[], so any api (such as Connection.createArrayOf(String, Object[])) can be given a byte[][] directly.

[davecramer]

There is some ambiguity there, but /assuming/ an array of bytea seems reasonable enough in this scenario.

if this is the case should we implement it this way ?

[pingw33n]

Part of the question is why would one represent a byte[][] as an Object[], where the values are byte[].

As an example, one can have createArray<T, U>(String typeName, Collection<T> items, Function<T, U> mapper) and U is erased so it's not possible to construct typed array.

Looking at JDBC docs, it doesn't restrict elements to be in either of the forms, so implementors must support both.

[davecramer]

Looking at JDBC docs, it doesn't restrict elements to be in either of the forms, so implementors must support both.

Not sure I agree with this opinion; but I'm not ruling it out. There is a considerably amount of ambiguity in the spec.

[bokken]

As an example, one can have createArray<T, U>(String typeName, Collection<T> items, Function<T, U> mapper) and U is erased so it's not possible to construct typed array.

That is not part of jdbc spec we are implementing, so not sure how relevant it is.
Even then it is still /possible/ to construct a typed array, it is just not convenient. The same instanceof check/flexing could be done there.

Looking at JDBC docs, it doesn't restrict elements to be in either of the forms, so implementors must support both.

I agree with @davecramer that there is much leeway/ambiguity in the spec. For example oracle does not support this method at all.

This is a specific use case which has never been supported by pgjdbc. It might be reasonable to add support.

[hgschmie]

Hi (JDBI code author/maintainer here),

What our code does is that we receive a request to bind a varargs list of byte[] arrays onto a specific column (which maps onto a bytea []. So our code at some point calls

                java.sql.Array sqlArray = statement.getConnection().createArrayOf(typeName, array);
                statement.setArray(position, sqlArray);

with typeName being "bytea" and the array being an Object[] which contains byte[] arrays. While I understand that it would be preferable for this to be a byte[][], our calling API does not work this way (because users call our code with a varargs list of regular byte arrays). And our arrays are not uniform but "ragged" (the Object[] may contain a byte[10], then a byte[30], then a byte[5]) and this should be adequately reflected in the resulting data in the DB (which would not work with a byte[][] where all elements in the array would end up with the same length.

I feel that this is an actual driver limitation, because the DB itself seems to support this just fine.

[davecramer]

I think we are open to the implementation. Care to provide a PR ?

[davecramer]

@hgschmie any reason not to provide a PR here ?