Unknown column sql error while using jooq

Query:

this.dslContext.select(
            ROLE.asterisk(),
            multiset(
                select(PERMISSION.asterisk())
                    .from(ROLE_PERMISSION)
                    .innerJoin(PERMISSION)
                    .on(ROLE_PERMISSION.PERMISSION_ID.eq(PERMISSION.ID))
                    .where(ROLE_PERMISSION.ROLE_ID.eq(ROLE.ID))
            ).as("permissions")
        ).from(ROLE)
            .where(ROLE.ID.eq(id))
            .fetchOneInto(Role.class)

Error:

jOOQ; bad SQL grammar [set @t = @@group_concat_max_len; set @@group_concat_max_len = 4294967295; select `users`.`role`.*, (select coalesce(json_merge_preserve('[]', concat('[', group_concat(json_array(`v0`, `v1`) separator ','), ']')), json_array()) from (select `users`.`permission`.`id` as `v0`, `users`.`permission`.`name` as `v1` from `users`.`role_permission` join `users`.`permission` on `users`.`role_permission`.`permission_id` = `users`.`permission`.`id` where `users`.`role_permission`.`role_id` = `users`.`role`.`id`) as `t`) as `permissions` from `users`.`role` where `users`.`role`.`id` = ?; set @@group_concat_max_len = @t;]; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'users.role.id' in 'where clause'.

Database: MYSQL, Database Name: ‘users’, JOOQ Version: 3.16.6

Answer

Correlating derived tables isn’t supported in MySQL 5.7. Support has been added only in MySQL 8.0.14: https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html

jOOQ currently can’t work around this limitation, see: https://github.com/jOOQ/jOOQ/issues/12045

The solution is either:

• Upgrade your MySQL version
• Use a MULTISET_AGG based approach instead