RestEasy/Panache Reactive unable to find records in database using the IN operator

[hakuseki]

Hi

I'm struggling with an issue where I need to find customers in my database where the call is passed a list of Ids.

Using the Panache Active Record I'm trying to use the find() or the list() but it fails to find anything.

  @GET
    @Path("/customerId/multi")
    @Produces(MediaType.APPLICATION_XML)
    public Uni<List<Accounts>> findByCustomerId(@RestQuery @Separator(",") List<String> customerId) {
        return Accounts.findByCustomerId(customerId);
    }
...
public static Uni<List<Accounts>> findByCustomerId(List<String> customerId) {
        return find("agasCustomerNumber in :vals ", Parameters.with("vals", customerId)).list();
    }

The SQL executed is somewhat problematic, using a call with &customerId=1,2,3 produce a list of three items but the Hibernate executes this:

Hibernate: 
    select
        a1_0.id,
        a1_0.agas_customernumber,
        a1_0.companycustomernumber,
        a1_0.customernumber,
        a1_0.email,
        a1_0.first_name,
        a1_0.imported_timestamp,
        a1_0.last_login,
        a1_0.last_name,
        a1_0.migrated_timestamp,
        a1_0.migration_state,
        a1_0.customernumberorganisation,
        a1_0.password,
        a1_0.personnumber,
        a1_0.pisa_uid,
        a1_0.username 
    from
        Accounts a1_0 
    where
        a1_0.agas_customernumber in($1,$2,$3,$4)

Note the forth argument...

Also I haven't found any documentation on how to retrieve records like this...

Maybe there're other alternatives or I'm doing something wrong?

Thx

Quarkus 3.0.4.Final is used

[quarkus-bot[bot]]

/cc @FroMage (panache), @loicmathieu (panache)

[hakuseki]

I'm using a PostgreSQL db where I can execute SELECT * from accounts where agas_customernumber in ('1','2','3') and it returns successfully three records.

Howver when running the query in HQL:

public static Uni<List<Accounts>> findByCustomerId(List<String> customerId) {
        return list("agasCustomerNumber in :vals ", Parameters.with("vals", customerId));
    }

or

    public static Uni<List<Accounts>> findByCustomerId(List<String> customerId) {
        return find("agasCustomerNumber in :vals ", Parameters.with("vals", customerId)).list();
    }

I get nothing back

The logs:

2023-06-02 10:58:23,538 DEBUG [org.hib.orm.sql.ast.create] (vert.x-eventloop-thread-1) Registration of TableGroup [StandardTableGroup(se.arbetsformedlingen.ciam.migration.model.Accounts(46221460937356))] with identifierForTableGroup [se.arbetsformedlingen.ciam.migration.model.Accounts] for NavigablePath [se.arbetsformedlingen.ciam.migration.model.Accounts] 
2023-06-02 10:58:23,541 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmParameter : SqmNamedParameter(vals)
2023-06-02 10:58:23,542 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmPath : SqmBasicValuedSimplePath(se.arbetsformedlingen.ciam.migration.model.Accounts(46221460937356).agasCustomerNumber) 
2023-06-02 10:58:23,542 DEBUG [org.hib.orm.que.sqm.transform] (vert.x-eventloop-thread-1) Adding domain-param xref expansion : SqmNamedParameter(vals)
2023-06-02 10:58:23,560 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmParameter : SqmNamedParameter(vals)
2023-06-02 10:58:23,561 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmPath : SqmBasicValuedSimplePath(se.arbetsformedlingen.ciam.migration.model.Accounts(46221460937356).agasCustomerNumber) 
2023-06-02 10:58:23,572 DEBUG [org.hib.orm.que.sqm.transform] (vert.x-eventloop-thread-1) Adding domain-param xref expansion : SqmNamedParameter(vals)
2023-06-02 10:58:23,572 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmParameter : SqmNamedParameter(vals)
2023-06-02 10:58:23,572 DEBUG [org.hib.que.sqm.sql.BaseSqmToSqlAstConverter] (vert.x-eventloop-thread-1) Determining mapping-model type for SqmPath : SqmBasicValuedSimplePath(se.arbetsformedlingen.ciam.migration.model.Accounts(46221460937356).agasCustomerNumber) 
2023-06-02 10:58:23,586 DEBUG [org.hib.orm.sql.exec] (vert.x-eventloop-thread-1) Skipping reading Query result cache data: cache-enabled = true, cache-mode = NORMAL
2023-06-02 10:58:23,591 TRACE [org.hib.orm.jdb.bind] (vert.x-eventloop-thread-1) binding parameter [1] as [VARCHAR] - [1]
2023-06-02 10:58:23,605 TRACE [org.hib.orm.jdb.bind] (vert.x-eventloop-thread-1) binding parameter [2] as [VARCHAR] - [2]
2023-06-02 10:58:23,607 TRACE [org.hib.orm.jdb.bind] (vert.x-eventloop-thread-1) binding parameter [3] as [VARCHAR] - [3]
2023-06-02 10:58:23,608 DEBUG [org.hib.SQL] (vert.x-eventloop-thread-1) 
    select
        a1_0.id,
        a1_0.agas_customernumber,
        a1_0.companycustomernumber,
        a1_0.customernumber,
        a1_0.email,
        a1_0.first_name,
        a1_0.imported_timestamp,
        a1_0.last_login,
        a1_0.last_name,
        a1_0.migrated_timestamp,
        a1_0.migration_state,
        a1_0.customernumberorganisation,
        a1_0.password,
        a1_0.personnumber,
        a1_0.pisa_uid,
        a1_0.username 
    from
        Accounts a1_0 
    where
        a1_0.agas_customernumber in($1,$2,$3)

Hibernate: 
    select
        a1_0.id,
        a1_0.agas_customernumber,
        a1_0.companycustomernumber,
        a1_0.customernumber,
        a1_0.email,
        a1_0.first_name,
        a1_0.imported_timestamp,
        a1_0.last_login,
        a1_0.last_name,
        a1_0.migrated_timestamp,
        a1_0.migration_state,
        a1_0.customernumberorganisation,
        a1_0.password,
        a1_0.personnumber,
        a1_0.pisa_uid,
        a1_0.username 
    from
        Accounts a1_0 
    where
        a1_0.agas_customernumber in($1,$2,$3)

2023-06-02 10:58:23,622 DEBUG [org.hib.orm.res.loading] (vert.x-eventloop-thread-1) Calling top-level assembler (0 / 1) : org.hibernate.reactive.sql.results.graph.entity.internal.ReactiveEntityAssembler@7bb08153
2023-06-02 10:58:23,628 DEBUG [org.hib.orm.res.loading] (vert.x-eventloop-thread-1) Calling top-level assembler (0 / 1) : org.hibernate.reactive.sql.results.graph.entity.internal.ReactiveEntityAssembler@7bb08153
2023-06-02 10:58:23,636 DEBUG [org.hib.orm.res.loading] (vert.x-eventloop-thread-1) Calling top-level assembler (0 / 1) : org.hibernate.reactive.sql.results.graph.entity.internal.ReactiveEntityAssembler@7bb08153
2023-06-02 10:58:23,638 DEBUG [org.hib.res.jdb.int.LogicalConnectionManagedImpl] (vert.x-eventloop-thread-1) Initiating JDBC connection release from afterTransaction

[FroMage]

Can you debug or print the list that you pass to the query, just to make sure it's not the REST layer giving you an empty list?

[hakuseki]

REST

Panache

[FroMage]

OK thanks. This looks like an ORM bug, no, @Sanne or @yrodiere ?

[yrodiere]

@hakuseki I don't see the issue about the fourth argument in your second example; was that a problem on your end, or does it appear randomly?

This looks like an ORM bug, no, @Sanne or @yrodiere ?

In this case it might even be a Hibernate Reactive bug.

@hakuseki Please open a bug report with a reproducer so that we can investigate?

[hakuseki]

I added a property that removed the fourth. Padding = false.
Didn't help though.

[hakuseki]

Bug reported with demo repo

#33865

[FroMage]

OK, we can close the discussion, it's a bug. Thanks everyone.

[hakuseki]

If REST returned in JSON it works fine