H2 row expression IN predicate with subquery doesn't work when subquery columns are aliased

[lukaseder]

H2 doesn't really support row expressions, but jOOQ can emulate them using H2's syntactically equivalent array notation (implemented with #1905), except if the IN predicate's subquery usese column aliases, e.g.

assertEquals(asList(1, 2),
create().select(TBook_ID())
        .from(TBook())
        .where(row(TBook_ID(), TBook_AUTHOR_ID()).in(
            select(val(1).as("x"), val(1).as("y")) // Alias here
            .unionAll(
            select(val(2).as("x"), val(1).as("y"))) // Alias here
            .unionAll(
            select(TBook_ID().as("x"), TBook_ID().as("y")).from(TBook())))) // Alias here
        .orderBy(TBook_ID())
        .fetch(0, Integer.class));

The aliases are necessary in some other databases, as subqueries are not allowed to either:

• Have unnamed columns
• Have two columns of the same (generated) name "1"

But in H2, the following invalid SQL is generated:

select "PUBLIC"."T_BOOK"."ID"
from "PUBLIC"."T_BOOK"
where ("PUBLIC"."T_BOOK"."ID", "PUBLIC"."T_BOOK"."AUTHOR_ID") in (
  (
    select ( -- Wrapping subquery result in array
      1 "x", 
      1 "y"
    )
  )
  union all (
    select ( -- Wrapping subquery result in array
      2 "x", 
      1 "y"
    )
  )
  union all (
    select ( -- Wrapping subquery result in array
      "PUBLIC"."T_BOOK"."ID" "x", 
      "PUBLIC"."T_BOOK"."ID" "y"
    )
    from "PUBLIC"."T_BOOK"
  )
)
order by "PUBLIC"."T_BOOK"."ID"

This would be a more correct solution:

select "PUBLIC"."T_BOOK"."ID"
from "PUBLIC"."T_BOOK"
where ("PUBLIC"."T_BOOK"."ID", "PUBLIC"."T_BOOK"."AUTHOR_ID") in (
  select ("x", "y") from (
  (
    select
      1 "x", 
      1 "y"
  )
  union all (
    select 
      2 "x", 
      1 "y"
  )
  union all (
    select
      "PUBLIC"."T_BOOK"."ID" "x", 
      "PUBLIC"."T_BOOK"."ID" "y"
    from "PUBLIC"."T_BOOK"
  )
  )
)
order by "PUBLIC"."T_BOOK"."ID"

[lukaseder]

This seems to no longer be a problem, probably due to the fixes in #3676