Jooq: Column "INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE" not found in H2 1.4.197

[Skym0sh0]

Currently I am stuck with H2 1.4.197 for my Unit Testing and jOOQ Code Generation. Yesterday I tried to update jOOQ to 3.14.8 (pro-java-8) when suddenly the jOOQ Codegeneration emitted the following error:

rg.jooq.exception.DataAccessException: SQL [select INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, case when (INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%YEAR%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%MONTH%') then 'INTERVAL YEAR TO MONTH' when (INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%DAY%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%HOUR%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%MINUTE%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%SECOND%') then 'INTERVAL DAY TO SECOND' else INFORMATION_SCHEMA.COLUMNS.TYPE_NAME end TYPE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE COLUMN_TYPE, case when (INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION = 65535 and INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE = 32767) then 0 else INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH end CHARACTER_MAXIMUM_LENGTH, decode(INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, 65535, 0, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION) NUMERIC_PRECISION, decode(INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, 32767, 0, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE) NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.REMARKS, INFORMATION_SCHEMA.COLUMNS.SEQUENCE_NAME, INFORMATION_SCHEMA.COLUMNS.DOMAIN_SCHEMA, INFORMATION_SCHEMA.COLUMNS.DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS where (INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = cast(? as varchar(2147483647)) and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = cast(? as varchar(2147483647))) order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION]; Feld "INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE" nicht gefunden
Column "INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE" not found; SQL statement:
select INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, case when (INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%YEAR%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%MONTH%') then 'INTERVAL YEAR TO MONTH' when (INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%DAY%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%HOUR%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%MINUTE%' or INFORMATION_SCHEMA.COLUMNS.INTERVAL_TYPE like '%SECOND%') then 'INTERVAL DAY TO SECOND' else INFORMATION_SCHEMA.COLUMNS.TYPE_NAME end TYPE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE COLUMN_TYPE, case when (INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION = 65535 and INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE = 32767) then 0 else INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH end CHARACTER_MAXIMUM_LENGTH, decode(INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, 65535, 0, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION) NUMERIC_PRECISION, decode(INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, 32767, 0, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE) NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.REMARKS, INFORMATION_SCHEMA.COLUMNS.SEQUENCE_NAME, INFORMATION_SCHEMA.COLUMNS.DOMAIN_SCHEMA, INFORMATION_SCHEMA.COLUMNS.DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS where (INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = cast(? as varchar(2147483647)) and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = cast(? as varchar(2147483647))) order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION [42122-197]
    at org.jooq_3.14.8.H2.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2903)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:333)
    at org.jooq.impl.AbstractResultQuery.iterator(AbstractResultQuery.java:344)
    at org.jooq.impl.SelectImpl.iterator(SelectImpl.java:2890)
    at org.jooq.meta.h2.H2TableDefinition.getElements0(H2TableDefinition.java:92)
    at org.jooq.meta.AbstractElementContainerDefinition.getElements(AbstractElementContainerDefinition.java:88)
    at org.jooq.meta.AbstractTableDefinition.getColumns(AbstractTableDefinition.java:172)
    at org.jooq.codegen.JavaGenerator.generateTable(JavaGenerator.java:5025)
    at org.jooq.codegen.JavaGenerator.generateTables(JavaGenerator.java:5012)
    at org.jooq.codegen.JavaGenerator.generate(JavaGenerator.java:582)
    at org.jooq.codegen.JavaGenerator.generate(JavaGenerator.java:537)
    at org.jooq.codegen.JavaGenerator.generate(JavaGenerator.java:436)
    at org.jooq.codegen.GenerationTool.run0(GenerationTool.java:879)
    at org.jooq.codegen.GenerationTool.run(GenerationTool.java:233)

I looked into the H2information schema and indeed there is no such column within the COLUMNS table:

 try (CloseableDSLContext ctx = DSL.using("jdbc:h2:mem:")) {
    Result<Record> c = ctx.fetch("SELECT * FROM INFORMATION_SCHEMA.COLUMNS");

    // AssertJ
    assertThat(c.fields())
        .extracting(Field::getName)
        .contains("INTERVAL_TYPE");

    assertThat(c.field("INTERVAL_TYPE")).isNotNull();
}

However, upgrading H2 to 1.4.198 solves the error, but is not applicable for me as there are also breaking changes (which clash withmy schema evolution).

[lukaseder]

H2 has been moving very fast over the past years, with a lot of breaking changes. Some of which have been headaches to ourselves as well.

The usual approach here is to offer support for versioned dialects, such as e.g. for Oracle (10g, 11g, 12c, 18c, 20c). But for H2, this is usually not needed as most people can upgrade to the latest version easily.

Before we consider adding versioned dialects for H2 (or work around this particular issue in the code generator), what is the exact problem you have in your schema evolution?

[lukaseder]

(I mean, we could try to avoid adding that column if it isn't supported. But without formal support for H2 1.4.197, and without regression tests on that version, we might run into new issues any time)

[lukaseder]

The problem was introduced in jOOQ 3.14.0 with https://github.com/jOOQ/jOOQ/issues/2230

[lukaseder]

Hmm, there are a few other H2 version guards in the area, e.g. one that protects against using COLUMNS.COLUMN_TYPE in pre 1.4.197 H2 versions. I'll just add another guard here that prevents the usage of COLUMNS.INTERVAL_TYPE pre 1.4.198. This is a workaround, but it might be good enough for you, or at least better than the status quo.

[lukaseder]

Fixed in jOOQ 3.15.0 and 3.14.9 (#11735, due this week)

[Skym0sh0]

Before we consider adding versioned dialects for H2 (or work around this particular issue in the code generator), what is the exact problem you have in your schema evolution?

We have keywords as identifier (like a table named CASE) which are actually renamed by now. However, as we use Liquibase for schema evolution we can not easily get rid of the old keyword-identifiers. With H2 pre-1.4.198 this was not a problem, with 1.4.198 and later the behavior changed.

[lukaseder]

Thanks for the update. I see - well I guess that given that the next version of H2 seems to again introduce quite a few breaking changes (to enable excellent new features), perhaps it's time we did introduce version support for H2 as well, to our commercial distributions... I've created an issue for this: https://github.com/jOOQ/jOOQ/issues/11737

For the time being, this fix should at least help work around this particular problem (at the cost of getting wrong timestamp precisions)