CODEWITHSUNDEEP
spring-booth2jooq
Axel
Axel

Reputation: 2725

Function not found when generating code with DDL Database - jooq

I have a spring boot gradle project with a mysql database. Previously under jooq version 3.13.6 my sql was parsed without errors. When updating to a higher jooq version (3.14.X and 3.15.X) and generating/parsing the migrations with jooq, I get the following output:

SEVERE DDLDatabase Error: Your SQL string could not be parsed or interpreted. This may have a variety of reasons, including:

  • The jOOQ parser doesn't understand your SQL
  • The jOOQ DDL simulation logic (translating to H2) cannot simulate your SQL

org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "coalesce" not found;

A basic sql example where the error occurs is given below. Parsing the same view worked with jooq 3.13.6.

DROP VIEW IF EXISTS view1;
CREATE VIEW view1 AS
SELECT COALESCE(SUM(table1.col1), 0)   AS 'sum'
FROM table1;

I am currently lost here. I don't see any related changes in the jooq changelog.

Any help or directions to further have a look into are highly appreciated.

Extended Stacktrace:

11:10:30 SEVERE DDLDatabase Error        : Your SQL string could not be parsed or interpreted. This may have a variety of reasons, including:
- The jOOQ parser doesn't understand your SQL
- The jOOQ DDL simulation logic (translating to H2) cannot simulate your SQL

If you think this is a bug or a feature worth requesting, please report it here: https://github.com/jOOQ/jOOQ/issues/new/choose

As a workaround, you can use the Settings.parseIgnoreComments syntax documented here:
https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-parser/
11:10:30 SEVERE Error while loading file: /Users/axel/projects/service/./src/main/resources/db/migration/V5__create_view1.sql
11:10:30 SEVERE Error in file: /Users/axel/projects/service/build/tmp/generateJooq/config.xml. Error : Error while exporting schema
org.jooq.exception.DataAccessException: Error while exporting schema
        at org.jooq.meta.extensions.AbstractInterpretingDatabase.connection(AbstractInterpretingDatabase.java:103)
        at org.jooq.meta.extensions.AbstractInterpretingDatabase.create0(AbstractInterpretingDatabase.java:77)
        at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:332)
        at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:322)
        at org.jooq.meta.AbstractDatabase.setConnection(AbstractDatabase.java:312)
        at org.jooq.codegen.GenerationTool.run0(GenerationTool.java:531)
        at org.jooq.codegen.GenerationTool.run(GenerationTool.java:237)
        at org.jooq.codegen.GenerationTool.generate(GenerationTool.java:232)
        at org.jooq.codegen.GenerationTool.main(GenerationTool.java:204)
Caused by: org.jooq.exception.DataAccessException: SQL [create view "view1" as select "coalesce"("sum"("table1"."col1"), 0) "sum" from "table1"]; Function "coalesce" not found; SQL statement:
create view "view1" as select "coalesce"("sum"("table1"."col1"), 0) "sum" from "table1" [90022-200]
        at org.jooq_3.15.5.H2.debug(Unknown Source)
        at org.jooq.impl.Tools.translate(Tools.java:2988)
        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
        at org.jooq.meta.extensions.ddl.DDLDatabase.load(DDLDatabase.java:183)
        at org.jooq.meta.extensions.ddl.DDLDatabase.lambda$export$0(DDLDatabase.java:156)
        at org.jooq.FilePattern.load0(FilePattern.java:307)
        at org.jooq.FilePattern.load(FilePattern.java:287)
        at org.jooq.FilePattern.load(FilePattern.java:300)
        at org.jooq.FilePattern.load(FilePattern.java:251)
        at org.jooq.meta.extensions.ddl.DDLDatabase.export(DDLDatabase.java:156)
        at org.jooq.meta.extensions.AbstractInterpretingDatabase.connection(AbstractInterpretingDatabase.java:100)
        ... 8 more

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "coalesce" not found; SQL statement:
create view "view1" as select "coalesce"("sum"("table1"."col1"), 0) "sum" from "table1" [90022-200]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:576)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
        at org.h2.message.DbException.get(DbException.java:205)
        at org.h2.message.DbException.get(DbException.java:181)
        at org.h2.command.Parser.readJavaFunction(Parser.java:3565)
        at org.h2.command.Parser.readFunction(Parser.java:3770)
        at org.h2.command.Parser.readTerm(Parser.java:4305)
        at org.h2.command.Parser.readFactor(Parser.java:3343)
        at org.h2.command.Parser.readSum(Parser.java:3330)
        at org.h2.command.Parser.readConcat(Parser.java:3305)
        at org.h2.command.Parser.readCondition(Parser.java:3108)
        at org.h2.command.Parser.readExpression(Parser.java:3059)
        at org.h2.command.Parser.readFunctionParameters(Parser.java:3778)
        at org.h2.command.Parser.readFunction(Parser.java:3772)
        at org.h2.command.Parser.readTerm(Parser.java:4305)
        at org.h2.command.Parser.readFactor(Parser.java:3343)
        at org.h2.command.Parser.readSum(Parser.java:3330)
        at org.h2.command.Parser.readConcat(Parser.java:3305)
        at org.h2.command.Parser.readCondition(Parser.java:3108)
        at org.h2.command.Parser.readExpression(Parser.java:3059)
        at org.h2.command.Parser.parseSelectExpressions(Parser.java:2931)
        at org.h2.command.Parser.parseSelect(Parser.java:2952)
        at org.h2.command.Parser.parseQuerySub(Parser.java:2817)
        at org.h2.command.Parser.parseSelectUnion(Parser.java:2649)
        at org.h2.command.Parser.parseQuery(Parser.java:2620)
        at org.h2.command.Parser.parseCreateView(Parser.java:6950)
        at org.h2.command.Parser.parseCreate(Parser.java:6223)
        at org.h2.command.Parser.parsePrepared(Parser.java:903)
        at org.h2.command.Parser.parse(Parser.java:843)
        at org.h2.command.Parser.parse(Parser.java:815)
        at org.h2.command.Parser.prepareCommand(Parser.java:738)
        at org.h2.engine.Session.prepareLocal(Session.java:657)
        at org.h2.engine.Session.prepareCommand(Session.java:595)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
        at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:212)
        at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201)
        at org.jooq.tools.jdbc.DefaultStatement.execute(DefaultStatement.java:102)
        at org.jooq.impl.SettingsEnabledPreparedStatement.execute(SettingsEnabledPreparedStatement.java:227)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:414)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:335)
        ... 16 more


> Task :generateJooq FAILED

Jooq Configuration:

jooq {
version = "3.15.5"
edition = JooqEdition.OSS
configurations {
    main {
        generationTool {
            generator {
                name = 'org.jooq.codegen.KotlinGenerator'
                strategy {
                    name = 'org.jooq.codegen.DefaultGeneratorStrategy'
                }
                generate {
                    relations = true
                    deprecated = false
                    records = true
                    immutablePojos = true
                    fluentSetters = true
                    daos = false
                    pojosEqualsAndHashCode = true
                    javaTimeTypes = true
                }
                target {
                    packageName = 'de.project.service.jooq'
                }
                database {
                    name = 'org.jooq.meta.extensions.ddl.DDLDatabase'
                    properties {
                        property {
                            key = 'scripts'
                            value = 'src/main/resources/db/migration/*.sql'
                        }
                        property {
                            key = 'sort'
                            value = 'semantic'
                        }
                        property {
                            key = 'unqualifiedSchema'
                            value = 'none'
                        }
                        property {
                            key = 'defaultNameCase'
                            value = 'lower'
                        }
                    }
                }
            }
        }
    }
}

Upvotes: 2

Views: 1038

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220987

You probably have the following configuration set:

<property>
    <key>defaultNameCase</key>
    <value>lower</value>
</property>

In jOOQ 3.15, this transforms all identifiers to lower case and quotes them before handing the SQL statement to H2 behind the scenes for DDL simulation, in order to emulate e.g. PostgreSQL behaviour, where unquoted identifiers are lower case, not upper case as in many other RDBMS.

There's a bug in the current implementation, which also quotes built-in functions, not just user defined objects. See:

The only workaround I can think of would be to turn off that property again, and manually quote all identifiers to be lower case. Alternatively, instead of using the DDLDatabase, you can always connect to an actual database instead, e.g. by using testcontainers. This will be much more robust in many ways, anyway, than the DDLDatabase

In any case, this is quite the frequent problem, so, I've fixed this for the upcoming jOOQ 3.16. The above setting will no longer quote "system names", which are well known identifiers of built-in functions

Upvotes: 1

Related Questions