Search code examples
hibernatehqlhibernate-spatial

HQL gives "unnexpected AST node (" within case clause

This is a simplified query which will reproduce the error:

SELECT 
 CASE 
     WHEN contains(buffer(r.polygon, 0.009), h.latlng) THEN 1 
     ELSE 0 
 END as near 
FROM Region r, House h 
WHERE r.id = 732 AND h.id = 892877180

Executing session.createQuery(hql) will throw QuerySyntaxException: unexpected AST node: ( near line 1, column 32. Column 32 is the first opening parentheses which comes right after contains.

As the underlying database is MySQL and this HQL happens to be valid MySQL too I tested the exact same query against the MySQL database and it worked fine. So I am assuming the issue relies on the Hibernate Query Language syntax.

I did not find anywhere in the Hibernate documentation stating that using functions inside a CASE WHEN ... THEN clause is not supported nor a bug report on this issue though.

Note that when there is no CASE clause it works fine:

SELECT contains(buffer(r.polygon, 0.009), h.latlng) as near 
FROM Region r, House h 
WHERE r.id = 732 AND h.id = 892877180

I'm using Hibernate ORM 4.3.11, Hibernate Spatial 4.3 and MySQL 5.6.34 on Ubuntu Linux 18.04.1.

Solution

  • I think it's because the HQL parser expects a relational expression in the CASE WHEN-clause, and not just any boolean expression. I suspect this wil work:

    SELECT 
 CASE 
   WHEN contains(buffer(r.polygon, 0.009), h.latlng) = TRUE THEN 1 
 ELSE 0 
END as near  
FROM Region r, House h 
WHERE r.id = 732 AND h.id = 892877180