JdbcSQLSyntaxErrorException with h2 and jooq

Question

I have a table with column type text[].

I am using dsl with jooq with H2.The table has array of string values as:

+-------------+
|column_name  |
+-------------+
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
|[str1, str2] |
+-------------+

Table is a java class generated using jooq. When I execute the following query and print the result:

 println(
        dsl
          .select(Table.column_name)
          .from(Table)
          .fetch())

It prints me the above table data.

But when I execute:

 println(
        dsl
          .select(arrayAggDistinct(elementAt(Table.column_name, 1)))
          .from(Table)
          .fetch())

It throws me an exception:

o]   Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "db.Table.column_name" not found; SQL statement:
[info] select array_agg(distinct "db"."Table"."column_table"[1]) from db.Table [42122-200]
[info]   at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
[info]   at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
[info]   at org.h2.message.DbException.get(DbException.java:205)
[info]   at org.h2.message.DbException.get(DbException.java:181)
[info]   at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:163)
[info]   at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:145)
[info]   at org.h2.expression.function.Function.optimize(Function.java:2594)
[info]   at org.h2.expression.aggregate.AbstractAggregate.optimize(AbstractAggregate.java:92)
[info]   at org.h2.expression.aggregate.Aggregate.optimize(Aggregate.java:705)
[info]   at org.h2.command.dml.Select.prepare(Select.java:1206)

And it executes ok with normal PostgreSQL, but gives me error in this query with H2.

How can I make it work with H2?

Answer 1

Please check out the @Support annotation of DSL.arrayAggDistinct(). As of jOOQ 3.13, it includes the following dialects:

• AURORA_POSTGRES
• COCKROACHDB
• HSQLDB
• POSTGRES

In the near future, H2 will support more standards compliant ARRAY types, which resemble those of PostgreSQL much more: https://github.com/h2database/h2database/issues/2190

At that point, jOOQ will also add more support for H2 arrays and array functions: https://github.com/jOOQ/jOOQ/issues/10175, but right now, you simply can't use this feature on H2.