Thursday, February 19, 2009

Postgres/Hibernate sql fun - NOT

This probably isn't really a postgres, but a more general issue, but then ...

I was writing some stuff in EJB-QL and all I got from Postgres was

ERROR: could not identify an ordering operator for type record
SQLState:42883

This sucks of course :)
After some trial and error I found the following in my query:

group by resource4_.NAME , ... , (measuremen1_.TIME, measuremen1_.ID)

Removing the parens ('(',')') finally made the query run successfully.

Now you may ask, what was the input that led to this? Well, here is is:

SELECT new Foo(a.id, b.id, ..., bla.id, bla.time)
FROM .... , org.acme.Bla bla
GROUP by a.id, b.id, bla

So the query translated the bla in the GROUP BY to (bla,id, bla.time) - including the parens. Explicitly listing bla.id, bla.time in the GROUP BY clause solved this.
This is no fun, as the EJB-QL Query got correctly translated and postgres does not give any hint, what part of this longish SQL it does not like.


No comments: