Search code examples
javasqloracle-databasehsqldb

HSQLDB org.springframework.dao.TransientDataAccessResourceException on complex select

I have a query that runs fine in Oracle, but I receive a org.springframework.dao.TransientDataAccessResourceException when attempting to run the same query in HSQLDB (using the Oracle compatibility mode).

Here is the query in question:

select 
    Orders.id,
    (select sum(decode(Orders.status, 'C', Orderlines.qty, 0)) 
     from Orderlines where orderId = Orders.id
    ) as "productQuantity"
from Orders
join Orderlines on Orders.id = Orderlines.orderId
where Orders.customerId = ?
group by Orders.id, Orders.status

Here is the root of the stack trace

Caused by: org.hsqldb.HsqlException: java.lang.NullPointerException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.result.Result.newErrorResult(Unknown Source)
at org.hsqldb.result.Result.newErrorResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 60 more
Caused by: java.lang.NullPointerException
at org.hsqldb.ExpressionColumn.getValue(Unknown Source)
at org.hsqldb.Expression.getValue(Unknown Source)
at org.hsqldb.ExpressionOp.getValue(Unknown Source)
at org.hsqldb.ExpressionAggregate.updateAggregatingValue(Unknown Source)
at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
at org.hsqldb.QuerySpecification.getResult(Unknown Source)
at org.hsqldb.StatementQuery.getResult(Unknown Source)
... 63 more

If I simplify the query slightly by removing the decode clause, HSQLDB can handle it.

select 
    Orders.id,
    (select sum(Orders.status) 
     from Orderlines where orderId = Orders.id
    ) as "productQuantity"
from Orders
join Orderlines on Orders.id = Orderlines.orderId
where Orders.customerId = ?
group by Orders.id, Orders.status

Is this a known limitation of HSQLDB?

Any ideas how to get past this? I'm attempting to use HSQLDB for unit tests so modifying the query isn't really an option unless the solution also works for Oracle.

Solution

  • This is a limitation of HSQLDB. You can try CASE Order.status WHEN 'C' THEN Orderlines.qty ELSE 0 END instead and see if it works.