Jooq: JooQ 3.12.0 codegen fails with an incorrect query where 3.11 worked correctly (PostgreSQL)

[eduramiba]

Expected behavior and actual behavior:

When running codegen, it fails to scan the database for tables.
Tested with PostgreSQL 10 and 11.

Error:

ERROR:  syntax error at or near "select" at character 1122
STATEMENT:  select "tables"."table_schema", "tables"."table_name", "tables"."specific_name", "tables"."table_valued_function", "tables"."materialized_view", "tables"."description" from ((select "information_schema"."tables"."table_schema", "information_schema"."tables"."table_name", "information_schema"."tables"."table_name" as "specific_name", false as "table_valued_function", false as "materialized_view", "pg_catalog"."pg_description"."description" from "information_schema"."tables" join "pg_catalog"."pg_namespace" on "information_schema"."tables"."table_schema" = "pg_catalog"."pg_namespace"."nspname" join "pg_catalog"."pg_class" on ("pg_catalog"."pg_class"."relname" = "information_schema"."tables"."table_name" and "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid) left outer join "pg_catalog"."pg_description" on ("pg_catalog"."pg_description"."objoid" = "pg_catalog"."pg_class".oid and "pg_catalog"."pg_description"."objsubid" = $1) where ("information_schema"."tables"."table_schema" in ($2) and ("information_schema"."tables"."table_schema", "information_schema"."tables"."table_name") not in select "pg_catalog"."pg_namespace"."nspname", "pg_catalog"."pg_class"."relname" from "pg_catalog"."pg_class" join "pg_catalog"."pg_namespace" on "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid where "pg_catalog"."pg_class"."relkind" = 'm')) union all (select "pg_catalog"."pg_namespace"."nspname"::varchar, "pg_catalog"."pg_class"."relname"::varchar, "pg_catalog"."pg_class"."relname"::varchar, false as "table_valued_function", true as "materialized_view", "pg_catalog"."pg_description"."description" from "pg_catalog"."pg_class" join "pg_catalog"."pg_namespace" on "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid left outer join "pg_catalog"."pg_description" on ("pg_catalog"."pg_description"."objoid" = "pg_catalog"."pg_class".oid and "pg_catalog"."pg_description"."objsubid" = $3) where ("pg_catalog"."pg_namespace"."nspname" in ($4) and "pg_catalog"."pg_class"."relkind" = 'm')) union all (select "information_schema"."routines"."routine_schema", "information_schema"."routines"."routine_name", "information_schema"."routines"."specific_name", true as "table_valued_function", false as "materialized_view", '' from "information_schema"."routines" join "pg_catalog"."pg_namespace" on "information_schema"."routines"."specific_schema" = "pg_catalog"."pg_namespace"."nspname" join "pg_catalog"."pg_proc" on ("pg_catalog"."pg_proc"."pronamespace" = "pg_catalog"."pg_namespace".oid and (("pg_catalog"."pg_proc"."proname" || $5) || cast("pg_catalog"."pg_proc".oid as varchar)) = "information_schema"."routines"."specific_name") where ("information_schema"."routines"."routine_schema" in ($6) and "pg_catalog"."pg_proc"."proretset"))) as "tables" order by 1, 2
[WARNING] SQL exception            : Exception while executing meta query: ERROR: syntax error at or near "select"
  Position: 1122

If you think this is a bug in jOOQ, please report it here: https://github.com/jOOQ/jOOQ/issues/new

```sql
select 
  "tables"."table_schema", 
  "tables"."table_name", 
  "tables"."specific_name", 
  "tables"."table_valued_function", 
  "tables"."materialized_view", 
  "tables"."description"
from (
  (
    select 
      "information_schema"."tables"."table_schema", 
      "information_schema"."tables"."table_name", 
      "information_schema"."tables"."table_name" as "specific_name", 
      false as "table_valued_function", 
      false as "materialized_view", 
      "pg_catalog"."pg_description"."description"
    from "information_schema"."tables"
      join "pg_catalog"."pg_namespace"
        on "information_schema"."tables"."table_schema" = "pg_catalog"."pg_namespace"."nspname"
      join "pg_catalog"."pg_class"
        on (
          "pg_catalog"."pg_class"."relname" = "information_schema"."tables"."table_name"
          and "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid
        )
      left outer join "pg_catalog"."pg_description"
        on (
          "pg_catalog"."pg_description"."objoid" = "pg_catalog"."pg_class".oid
          and "pg_catalog"."pg_description"."objsubid" = 0
        )
    where (
      "information_schema"."tables"."table_schema" in ('jooq_test_catalog')
      and ("information_schema"."tables"."table_schema", "information_schema"."tables"."table_name") not in 
        select 
          "pg_catalog"."pg_namespace"."nspname", 
          "pg_catalog"."pg_class"."relname"
        from "pg_catalog"."pg_class"
          join "pg_catalog"."pg_namespace"
            on "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid
        where "pg_catalog"."pg_class"."relkind" = 'm'

    )
  )
  union all (
    select 
      "pg_catalog"."pg_namespace"."nspname"::varchar, 
      "pg_catalog"."pg_class"."relname"::varchar, 
      "pg_catalog"."pg_class"."relname"::varchar, 
      false as "table_valued_function", 
      true as "materialized_view", 
      "pg_catalog"."pg_description"."description"
    from "pg_catalog"."pg_class"
      join "pg_catalog"."pg_namespace"
        on "pg_catalog"."pg_class"."relnamespace" = "pg_catalog"."pg_namespace".oid
      left outer join "pg_catalog"."pg_description"
        on (
          "pg_catalog"."pg_description"."objoid" = "pg_catalog"."pg_class".oid
          and "pg_catalog"."pg_description"."objsubid" = 0
        )
    where (
      "pg_catalog"."pg_namespace"."nspname" in ('jooq_test_catalog')
      and "pg_catalog"."pg_class"."relkind" = 'm'
    )
  )
  union all (
    select 
      "information_schema"."routines"."routine_schema", 
      "information_schema"."routines"."routine_name", 
      "information_schema"."routines"."specific_name", 
      true as "table_valued_function", 
      false as "materialized_view", 
      ''
    from "information_schema"."routines"
      join "pg_catalog"."pg_namespace"
        on "information_schema"."routines"."specific_schema" = "pg_catalog"."pg_namespace"."nspname"
      join "pg_catalog"."pg_proc"
        on (
          "pg_catalog"."pg_proc"."pronamespace" = "pg_catalog"."pg_namespace".oid
          and (("pg_catalog"."pg_proc"."proname" || '_') || cast("pg_catalog"."pg_proc".oid as varchar)) = "information_schema"."routines"."specific_name"
        )
    where (
      "information_schema"."routines"."routine_schema" in ('jooq_test_catalog')
      and "pg_catalog"."pg_proc"."proretset"
    )
  )
) as "tables"
order by 
  1, 
  2

Steps to reproduce the problem:

MVCE here => https://github.com/eduramiba/jooq-issue-recordmapper/tree/jooq-3.12.0-codegen-issue

Versions:

• jOOQ: 3.12.0
• Java: OpenJDK Runtime Environment AdoptOpenJDK (build 12.0.1+12)
• Database (include vendor): PostgreSQL 11.5
• OS: Ubuntu

Thank you!

[lukaseder]

Thank you very much for your report. Those missing parentheses in the not in predicate seem quite significant.

I wonder how those got past our integration tests. Will investigate ASAP

[lukaseder]

Only the jOOQ Open Source edition is affected by this issue, not the commercial distributions.

[knutwannheden]

This bug has been fixed on the master branch. This fix will be down-branched to 3.12 (see #9106) and be made available in a 3.12.1 patch release very soon.

[eduramiba]

Awesome, thanks!

[lukaseder]

Version 3.12.1 has been released. It will be available from Maven Central soon.