0

I have the following mapping

@Entity
@Table(name = "table_a")
data class TableBEntity(
    @Id
    @Column(name = "id")
    val id: Int? = null,

    @Transient
    val tableBList: List<TableBEntity> = listOf()
)

@Entity
@Table(name = "table_b")
data class TableBEntity(
    @Id
    @Column(name = "id")
    val id: Int? = null,

    @Column(name = "user_id")
    val userId: Int? = null,

    @Column(name = "amount_cents")
    val amountCents: Int? = null,
)

And trying to make the following query with multiset

val test = it.select(
            TABLE_A.asterisk(),
            multisetAgg(
                TABLE_B.USER_ID,
                TABLE_B.AMOUNT_CENTS
            ).`as`("tableBList").convertFrom(List::class.java) { r -> r.map {
                    s -> TableBEntity(s[TABLE_B.USER_ID], s[TABLE_B.AMOUNT_CENTS])
                }
            })
            .from(TABLE_A)
            .join(TABLE_B)
            .on(TABLE_B.ID.eq(TABLE_A.ID))
            .where(TABLE_A.ID.eq(1))
val result = test.fetchInto(TableAEntity::class.java)

But when I inspect result, I get correctly the tableBlist, but the problem is that only user id is filled, the other fields come always null, even though they are not null.

In fact, when I try to run the jooq generated sql I get the correct list in tableBList column:

The generated sql:

set @t = @@group_concat_max_len;
set @@group_concat_max_len = 4294967295;

select `table_a`.*, json_merge_preserve('[]', 
concat('[', 
  group_concat(
    json_array(`table_b`.`user_id`,
               `table_b`.`amount_cents`) separator ','), ']')) as `tableBList`
from `table_a` join `table_b` on `table_b`.`id` = `table_a`.`id`
where `table_a`.`id` = 1;

set @@group_concat_max_len = @t;

And result when I execute the query directly:

id tableBList
1 [[6693, 20000], [6693, 30000], [6693, 17000], [6693, 30000], [6693, 80000]]

As you can see, there is no null values on the list, but when I inspect the variable s on the line s -> TableBEntity(s[TABLE_B.USER_ID, TABLE_B.AMOUNT_CENTS]), I get:

user_id amount_cents
*6693 {null}

And result contains a list of TableB, with the correct user_id's but amount_cents always null, like the jooq is not being able to parse the list from json array [[6693, 20000], [6693, 30000], [6693, 17000], [6693, 30000], [6693, 80000]]

Am I doing something wrong or is this a possible bug from jooq + mysql?

Tried jooq 3.15.5, 3.15.4, 3.15.3 with no success. MySQL version is 8.0.23

inafalcao
  • 1,415
  • 1
  • 11
  • 25
  • How come your data class has 3 attributes, but your constructor call only passes 2 arguments? Can you post the complete code, please? Also, why mix the annotations and reflective mapping with the functional mapping? It's possible of course, but only complicates things... – Lukas Eder Dec 15 '21 at 07:37
  • The mappings have nothing to do with the problem. I need them for other pruposes. Pretend there are three 3 arguments then, It was Just a typo and Also has nothing to do with the problem that's happening.. – inafalcao Dec 15 '21 at 10:40
  • Well, just checking... I wouldn't want to chase down problems that are caused by some question artifacts, see https://stackoverflow.com/help/minimal-reproducible-example. If an example isn't minimal, there's risk to get side-tracked... Anyway, have you tried removing `(List::class.java)`? It shouldn't be necessary. Why did you put it there? What exact jOOQ 3.15 patch version have you tried (3.14 or 3.13 don't support multiset)? – Lukas Eder Dec 15 '21 at 11:24
  • Multiset suport starts on 3.15. I tried without List::class.java too, same result. Edited the question to fix the versions I tried. – inafalcao Dec 15 '21 at 13:09
  • Alright, thanks for the updates. I'll try to reproduce this, soon. – Lukas Eder Dec 15 '21 at 15:19

0 Answers0