Jooq Multiset - SQL shows the word multiset thorwing syntax error

I am using Jooq version 3.16.6 with Java 11 and Spring Boot 2.6.6 and (PostgreSQL) 14.1

The issue i am having is with multiset , the non multiset query using the old join method works fine . However when using multiset and examining the sql i can see the word multiset actually in the sql query hence it thorws a syntax error

var result = 
dslContext.select(USERS.FIRST_NAME,USER_LEVELS.USER_LEVEL_NAME)
        .from(USERS)
        .join(USER_LEVELS)
    .on(USERS.USER_LEVEL_ID.eq(USER_LEVELS.USER_LEVEL_ID))
        .where(USERS.USERNAME.eq("[email protected]"))
        .fetch();

The above is the standard join method works fine

The below is the multiset version

var result =
      dslContext.select(USERS.FIRST_NAME,
            multiset(select(USER_LEVELS.USER_LEVEL_NAME)
                .from(USER_LEVELS)
                .where(USER_LEVELS.USER_LEVEL_ID.eq(USERS.USER_LEVEL_ID)))
)
            .from(USERS)
            .where(USERS.USERNAME.eq("[email protected]"))
            .getSQL();

I am using the getSQL to see the sql it is running which comes out as below

select "public"."users"."first_name", 
   multiset(select "public"."user_levels"."user_level_name" 
       from "public"."user_levels" where "public"."user_levels"."user_level_id" = "public"."users"."user_level_id") from "public"."users" where "public"."users"."username" = ?

The issue is obviously the multiset being in the sql which causes a syntax error.

I am at a loss as to why this is , unless its some setting for my postgres (which i don't think)

Any ideas ?

CodePudding user response：

You probably haven't configured your SQLDialect correctly, e.g.

spring.jooq.sql-dialect=Postgres