Why does this JPA query fail on a H2 database?-CodePudding

I've defined the following JPA query in a Spring Data interface

@Query("select tr.bidder.supplierId "  
    "from   TaskResponse tr "  
    "where  tr.task.id = :taskId "  
    "and    tr.extended = true "  
    "and    tr.bidder.supplierId not in (:extendedSupplierIds)")
Set<String> supplierIdsDueTaskRevocationNotification(UUID taskId,
        Set<String> extendedSupplierIds);

The query works fine when executed against a Postgres database, but fails when executed against a H2 database with the following error:

Syntax error in SQL statement 

SELECT BIDDER1_.SUPPLIER_ID AS COL_0_0_ 
FROM TASK_RESPONSES TASKRESPON0_ 
CROSS JOIN BIDDERS BIDDER1_ 
WHERE TASKRESPON0_.BIDDER_ID=BIDDER1_.ID 
AND TASKRESPON0_.TASK_ID=? 
AND TASKRESPON0_.EXTENDED=1 
AND (BIDDER1_.SUPPLIER_ID NOT IN  ()[*])"; 

expected "NOT, EXISTS, INTERSECTS, UNIQUE";

It seems like it's the not in (:extendedSupplierIds) predicate that's the problem, because if I remove this predicate the query executes without errors on both databases.

Is there a way I can re-write the query so that it will work on both databases?

Update

Following the advice of one respondent, I changed the query to use explicit joins

@Query("select b.supplierId "  
    "from   TaskResponse tr "  
    "join   tr.bidder b "  
    "join   tr.task t "  
    "where  t.id = :taskId "  
    "and    tr.extended = true "  
    "and    b.supplierId not in (:extendedSupplierIds)")
Set<String> supplierIdsDueTaskRevocationNotification(UUID taskId,
        Set<String> extendedSupplierIds);

But it made no difference, I still get the same error.

CodePudding user response：

This is caused by a bug, possibly in org.hibernate.dialect.H2Dialect.

If you pass an empty set as a query parameter value which forms the right-hand side of a "not in" predicate in a JPQL query, the query fails when running against a H2 database. To work around this, use a Set containing a single null element instead.

e.g. at the point where you call this query use

UUID taskId = UUID.randomUUID();
Set<String> supplierIds = // get this from somewhere

if (supplierIds.isEmpty()) {
  supplierIds.add(null);
}

myRepository.supplierIdsDueTaskRevocationNotification(taskId, supplierIds)

CodePudding user response：

You could check this SQLGrammarException: could not prepare statement. It seems you are having the same kind of problem as follow