Hibernate orderBy gotcha

I just spent the good part of an hour trying to figure out why the "order by" clause in my HQL query was causing a SQLGrammarException "could not execute query".

The SQL worked perfectly, I was stumped. Google couldn't even tell me the answer! I used the time-tested method of try everything and figure out the pattern to solve this one....

The answer was that if you are specifying the "select" clause, the attribute that you are ordering by must appear in the select list.

So, to continue with the example in the hibernate 3.3 documentation

select cat.sex from DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate

is incorrect, but

select cat.sex, cat.name, cat.weight, cat.birthdate from DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate

works.