使用JOOQ时未知列SQL错误

Question

查询：

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)

错误：

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'.

数据库：MySQL，数据库名称：'用户'，JOOQ版本：3.16.6

Answer 1

MySQL 5.7中不支持相关的派生表。支持仅在MySQL 8.0.14中添加：
https://dev.mysql.com/doc/refman/ 8.0/en/derived-tables.html

JOOQ当前无法处理此限制，请参阅：
https://github.com/jooq/jooq/jooq/sissues/issues/12045

解决方案是解决方案要么：

• 升级您的MySQL版本
• 使用 multiset_agg 基于的方法