Error on getting bytea[] from a ResultSet

[albert-kam]

I'm submitting a ... (I am not sure whether this is currently supported or not, but i'll put it as a feature request for now)

• bug report
• feature request

Describe the issue
A clear and concise description of what the issue is.

Driver Version? --> 42.2.12

Java Version? --> openjdk version "11.0.7" 2020-04-14

OS Version? --> Debian 10.4

PostgreSQL Version? --> PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

To Reproduce
Steps to reproduce the behaviour: --> Run TestSql.java (JUnit)

Expected behaviour
(byte[][]) rs.getArray("children_bytesarr").getArray() should work, just like (String[]) rs.getArray("children_names").getArray(), but it throws error instead.
The complete ddl, source code is provided below.

Logs
The error:

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class [[B ([Ljava.lang.Object; and [[B are in module java.base of loader 'bootstrap')
	at test.TestSql.lambda$1(TestSql.java:116)
	at org.jdbi.v3.core.result.ResultSetResultIterator.next(ResultSetResultIterator.java:83)
	at org.jdbi.v3.core.result.ResultIterable.one(ResultIterable.java:138)
	at test.TestSql.lambda$0(TestSql.java:118)
	at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:341)
	at test.TestSql.testArrayArg_onByteA(TestSql.java:84)

Basically i just want to get parent and children aggregated into arrays for each names and bytea fields in a single query. This works in psql, but i was not successful in getting the bytea[] value from the ResultSet.

The models:

-- for tests
CREATE TABLE parent (
	id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
	name VARCHAR(255) NOT NULL
);

CREATE TABLE child (
	id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	parent_id int NOT NULL REFERENCES parent(id), 
	name VARCHAR(512) NOT NULL,
	raw BYTEA NOT NULL
);

And the query:

private static final String FIND_PARENT = 
        "SELECT r.id AS id, r.name, f.children_names, f.children_bytesarr "
        + "FROM parent r "
        + "	 LEFT JOIN ("
        + "		SELECT f.parent_id AS id, "
        + "			ARRAY_AGG(f.name) as children_names, ARRAY_AGG(f.raw) as children_bytesarr "
        + "		FROM child f "
        + "		GROUP BY f.parent_id "
        + "		HAVING f.parent_id = :parent_id"
        + "  ) f USING (id) "
        + "WHERE r.id = :parent_id";

And here's the related code with resultSet (rs) which triggers the error, i leave the complete code below:

return handle.createQuery(FIND_PARENT)
    .bind("parent_id", parentId)
    .map((rs, ctx) -> Parent.builder()
            .withId(rs.getInt("id"))
            .withName(rs.getString("name"))
            .withChildrenNames((String[]) rs.getArray("children_names").getArray())
            // this triggers error
            .withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray())
            .build())
    .one();

And here's the complete JUnit TestSql.java:

package test;

import static org.junit.Assert.assertEquals;

import java.util.Collections;
import java.util.List;

import javax.sql.DataSource;

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.postgres.PostgresPlugin;
import org.junit.Test;

import com.google.common.collect.Lists;

public class TestSql {
	private static class Parent {
		private int id;
		private String name;
		private List<String> childrenNames;
		private List<byte[]> childrenBytesList;
		private Parent(Builder builder) {
			id = builder.id;
			name = builder.name;
			childrenNames = builder.childrenNames;
			childrenBytesList = builder.childrenBytesList;
		}
		public static Builder builder() {
			return new Builder();
		}
		public static final class Builder {
			private int id;
			private String name;
			private List<String> childrenNames = Collections.emptyList();
			private List<byte[]> childrenBytesList = Collections.emptyList();

			private Builder() {
			}

			public Builder withId(int id) {
				this.id = id;
				return this;
			}

			public Builder withName(String name) {
				this.name = name;
				return this;
			}

			public Builder withChildrenNames(String[] childrenNames) {
				this.childrenNames = Lists.newArrayList(childrenNames);
				return this;
			}

			public Builder withChildrenBytesArr(byte[][] childrenBytesArr) {
				childrenBytesList = Lists.newArrayList(childrenBytesArr);
				return this;
			}

			public Parent build() {
				return new Parent(this);
			}
		}
		
	}
	private static final String FIND_PARENT = 
			"SELECT r.id AS id, r.name, f.children_names, f.children_bytesarr "
			+ "FROM parent r "
			+ "	 LEFT JOIN ("
			+ "		SELECT f.parent_id AS id, "
			+ "			ARRAY_AGG(f.name) as children_names, ARRAY_AGG(f.raw) as children_bytesarr "
			+ "		FROM child f "
			+ "		GROUP BY f.parent_id "
			+ "		HAVING f.parent_id = :parent_id"
			+ "  ) f USING (id) "
			+ "WHERE r.id = :parent_id";
	@Test
	public void testArrayArg_onByteA() {
		Jdbi jdbi = Jdbi.create(getDataSource())
				.installPlugin(new PostgresPlugin());
		
		Parent parent = jdbi.withHandle(handle -> {
			handle.createBatch()
				.add("DELETE FROM child")
				.add("DELETE FROM parent")
				.add("ALTER TABLE parent ALTER COLUMN id RESTART WITH 1")
				.add("ALTER TABLE child ALTER COLUMN id RESTART WITH 1")
				.execute();
			
			int parentId = handle.createUpdate("INSERT INTO parent (name) VALUES (:name)")
				.bind("name", "myparent")
				.executeAndReturnGeneratedKeys("id")
				.mapTo(Integer.class)
				.one();
			
			handle.createUpdate("INSERT INTO child (parent_id, name, raw) VALUES (:parent_id, :name, :raw)")
				.bind("parent_id", parentId)
				.bind("name", "my 1st child")
				.bind("raw", new byte[] { 12, 23 })
				.execute();
			
			handle.createUpdate("INSERT INTO child (parent_id, name, raw) VALUES (:parent_id, :name, :raw)")
				.bind("parent_id", parentId)
				.bind("name", "my 2nd child")
				.bind("raw", new byte[] { 88, 99 })
				.execute();
			
			return handle.createQuery(FIND_PARENT)
				.bind("parent_id", parentId)
				.map((rs, ctx) -> Parent.builder()
						.withId(rs.getInt("id"))
						.withName(rs.getString("name"))
						.withChildrenNames((String[]) rs.getArray("children_names").getArray())
						.withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray())
						.build())
				.one();
		});
		
		assertEquals("myparent", parent.name);
		assertEquals(Lists.newArrayList("my 1st child", "my 2nd child"), parent.childrenNames);
		assertEquals(Lists.newArrayList(new byte[] { 12, 23 }, new byte[] { 88, 99 }), parent.childrenBytesList);
	}

	private DataSource getDataSource() {
		return ...
	}
}

The test will pass if .withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray()) is commented.

Please share your thoughts.

[bokken]

Support for bytea[] is in #1194

[albert-kam]

Support for bytea[] is in #1194

Hello @bokken, does this mean it will be released in 42.3.0 ?

May i ask when is this version planned to be released ?

Thank you very much for your support.