ClassCastException when nesting array(select row) projections in PostgreSQL #14105

lukaseder · 2022-10-18T13:52:06Z

A spurious "flaky" test failure was observed when running tests in a certain order. This test runs fine in isolation:

Result<Record2<
    Integer,
    Record3<
        String,
        String,
        Record2<Integer, String>[]
    >
>> result =
create().select(
            TAuthor_ID(),
            row(
                TAuthor_FIRST_NAME(),
                TAuthor_LAST_NAME(),
                array(select(row(TBook_ID(), TBook_TITLE()))
                    .from(TBook())
                    .where(TBook_AUTHOR_ID().eq(TAuthor_ID()))
                    .orderBy(TBook_ID())
                )
            )
        )
        .from(TAuthor())
        .orderBy(TAuthor_ID())
        .fetch();

assertEquals(AUTHOR_IDS, result.map(r -> r.value1()));
assertEquals(AUTHOR_FIRST_NAMES, result.map(r -> r.value2().value1()));
assertEquals(AUTHOR_LAST_NAMES, result.map(r -> r.value2().value2()));
assertEquals(asList(BOOK_IDS.subList(0, 2), BOOK_IDS.subList(2, 4)), result.map(a -> Seq.of(a.value2().value3()).map(b -> b.value1()).toList()));
assertEquals(asList(BOOK_TITLES.subList(0, 2), BOOK_TITLES.subList(2, 4)), result.map(a -> Seq.of(a.value2().value3()).map(b -> b.value2()).toList()));

But when the query is run twice, with flipped array projection columns, then the second execution fails with a ClassCastException. E.g. prepend this query to the above:

Result<Record2<
    Integer,
    Record3<
        String,
        String,
        // Switched columns here:
        Record2<String, Integer>[]
    >
>> result1 =
create().select(
            TAuthor_ID(),
            row(
                TAuthor_FIRST_NAME(),
                TAuthor_LAST_NAME(),
                // Switched columns here:
                array(select(row(TBook_TITLE(), TBook_ID()))
                    .from(TBook())
                    .where(TBook_AUTHOR_ID().eq(TAuthor_ID()))
                    .orderBy(TBook_ID())
                )
            )
        )
        .from(TAuthor())
        .orderBy(TAuthor_ID())
        .fetch();

And now, the following exception happens:

org.jooq.exception.DataAccessException: SQL [select "public"."t_author"."id", row ("public"."t_author"."first_name", "public"."t_author"."last_name", array(select row ("public"."t_book"."id", "public"."t_book"."title") as "nested" from "public"."t_book" where "public"."t_book"."author_id" = "public"."t_author"."id" order by "public"."t_book"."id")) as "nested" from "public"."t_author" order by "public"."t_author"."id" -- SQL rendered with a free trial version of jOOQ 3.18.0-SNAPSHOT]; Error while reading field: row (
  "public"."t_author"."first_name",
  "public"."t_author"."last_name",
  array(
    select row ("public"."t_book"."id", "public"."t_book"."title") "nested"
    from "public"."t_book"
    where "public"."t_book"."author_id" = "public"."t_author"."id"
    order by "public"."t_book"."id"
  )
), at JDBC index: 2
	at org.jooq_3.18.0-SNAPSHOT.POSTGRES.debug(Unknown Source)
	at org.jooq_3.18.0-SNAPSHOT.POSTGRES.debug(Unknown Source)
	at org.jooq.impl.Tools.translate(Tools.java:3363)
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:686)
	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1465)
	at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1426)
	at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:239)
	at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:177)
	at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:88)
	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:265)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:341)
	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
	at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2837)
	at org.jooq.test.all.testcases.RowValueExpressionTests.testRowValueExpressionInSelectWithNestedArraysOfRows(RowValueExpressionTests.java:1140)
	at org.jooq.test.jOOQAbstractTest.testRowValueExpressionInSelectWithNestedArraysOfRows(jOOQAbstractTest.java:5096)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: java.sql.SQLException: Error while reading field: row (
  "public"."t_author"."first_name",
  "public"."t_author"."last_name",
  array(
    select row ("public"."t_book"."id", "public"."t_book"."title") "nested"
    from "public"."t_book"
    where "public"."t_book"."author_id" = "public"."t_author"."id"
    order by "public"."t_book"."id"
  )
), at JDBC index: 2
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1591)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1526)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1)
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
	at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1450)
	... 42 more
Caused by: org.jooq.exception.DataTypeException: Error while creating array
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewArray(DefaultBinding.java:4090)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgFromString(DefaultBinding.java:3988)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgSetValue(DefaultBinding.java:4061)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.lambda$13(DefaultBinding.java:4053)
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewRecord(DefaultBinding.java:4049)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:3844)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.get0(DefaultBinding.java:1)
	at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1032)
	at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1581)
	... 46 more
Caused by: java.lang.NumberFormatException: For input string: "Animal Farm"
	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
	at java.base/java.lang.Integer.parseInt(Integer.java:668)
	at java.base/java.lang.Integer.valueOf(Integer.java:999)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgFromString(DefaultBinding.java:3948)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgSetValue(DefaultBinding.java:4061)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.lambda$13(DefaultBinding.java:4053)
	at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewRecord(DefaultBinding.java:4049)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgFromString(DefaultBinding.java:4004)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.lambda$14(DefaultBinding.java:4080)
	at org.jooq.impl.Tools.map(Tools.java:2282)
	at org.jooq.impl.DefaultBinding$DefaultRecordBinding.pgNewArray(DefaultBinding.java:4078)
	... 55 more

A likely cause for this behaviour could be that somehow the data type registers itself in the static type registry, and gets loaded from there on the second execution.

The text was updated successfully, but these errors were encountered:

lukaseder · 2022-10-20T07:53:13Z

The problem is this method in DefaultBinding.DefaultRecordBinding:

private static final Object[] pgNewArray(BindingScope ctx, Field<?> field, Class<?> type, String string) {
    if (string == null)
        return null;

    DataType<?> t = field.getDataType();
    try {
        return Tools.map(
            toPGArray(string),
            // Problem here:
            v -> pgFromString(ctx, field("array_element", type.getComponentType()), v),
            size -> (Object[]) java.lang.reflect.Array.newInstance(type.getComponentType(), size)
        );
    }
    catch (Exception e) {

        // [#11823]
        if (type.getComponentType().getSimpleName().equals("UnknownType"))
            throw new DataTypeException("Error while creating array for UnknownType. Please provide an explicit Class<U> type to your converter, see https://github.com/jOOQ/jOOQ/issues/11823", e);
        else
            throw new DataTypeException("Error while creating array", e);
    }
}

We're creating a plain SQL type from the type.getComponentType(), which is just RecordImpl2, rather than field.getDataType().getArrayComponentDataType(). However, changing that produces a regression on #11829, so let's tread carefully

lukaseder · 2022-10-20T08:00:12Z

Perhaps the DefaultBinding.DefaultRecordBinding::pgFromString method shouldn't call converter::from again, because that has already been done by ::pgNewArray, implicitly

lukaseder · 2022-10-20T08:14:09Z

The regression isn't on #11829 directly, but when we wrap a Field<T[]> again with convertFrom(Arrays::asList), to turn the array into a list.

lukaseder · 2022-10-20T08:17:57Z

Related: #13117

projections

lukaseder · 2022-10-20T09:03:26Z

Fixed in jOOQ 3.18.0 and 3.17.5 (#14117)

lukaseder added T: Defect C: Functionality P: High E: All Editions labels Oct 18, 2022

lukaseder added this to the Version 3.18.0 milestone Oct 18, 2022

lukaseder changed the title ~~ClassCastException when nesting array(select) projections~~ ClassCastException when nesting array(select row) projections Oct 20, 2022

lukaseder added this to To do in 3.18 Other improvements via automation Oct 20, 2022

lukaseder changed the title ~~ClassCastException when nesting array(select row) projections~~ ClassCastException when nesting array(select row) projections in PostgreSQL Oct 20, 2022

lukaseder added a commit that referenced this issue Oct 20, 2022

[#14105] ClassCastException when nesting array(select) projections

da12147

lukaseder added a commit that referenced this issue Oct 20, 2022

[#14105] ClassCastException when nesting array(select)

461cf40

projections

lukaseder closed this as completed Oct 20, 2022

3.18 Other improvements automation moved this from To do to Done Oct 20, 2022

lukaseder added the R: Fixed label Oct 20, 2022

lukaseder mentioned this issue Nov 2, 2022

Query gets affected by previous query and unexpectedly fails (seems that caching in DefaultDataType causes the problem) #14165

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ClassCastException when nesting array(select row) projections in PostgreSQL #14105

ClassCastException when nesting array(select row) projections in PostgreSQL #14105

lukaseder commented Oct 18, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

ClassCastException when nesting array(select row) projections in PostgreSQL #14105

ClassCastException when nesting array(select row) projections in PostgreSQL #14105

Comments

lukaseder commented Oct 18, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022

lukaseder commented Oct 20, 2022