For what it's worth, functions in order by is now supported by the JPA reference implementation, as of 14 months ago.
---------- Forwarded message ---------- From: James Sutherland <[email protected]> Date: Tue, May 4, 2010 at 2:42 PM Subject: [eclipselink-dev] SVN commit trunk: bug#219814 JPQL enhancements To: "Eclipselink-Dev@Eclipse. Org (E-mail)" <[email protected]> SVN commit trunk: bug#219814 JPQL enhancements Fixed several of the issues related to unnecessary restrictions put on our JPQL support, Code review: Andrei Changes: - Allowed expression inheritance view tests to run on all platforms, as it supported now and should work. - Updated JPQL grammar to remove restrictions, - functions and math operators in select and inside functions, aggregate functions, and constructors (select UPPER(e.firstName) from Employee e) - select without reference to alias (select 1 from Employee e) - functions in order by, booleans in order by (select e from Employee e order by UPPER(e.lastName)) - functions in group by, having (select e, COUNT(p) FROM Employee e JOIN e.projects p group by e having COUNT(p) >= 2) - functions in like, between, in (select e from Employee e where UPPER(e.firstName) like UPPER('b%')) - raise syntax error when invalid characters are used (select e from Employee e where !(e.id = 12345)) - JPQL tests for new syntax - Changed like to be a function instead of relation expression, allows avoiding conversion of type to local base - Some micro - Allow JPQL ordering by booleans or any basic type - Auto convert to string in in-memory like conforming - Convert JPQL select variables to lower case in index (as all other variables) - Allow usage of alias without reference - Change constructor queries to find any constructor taking compatible types (instead of exact types) _______________________________________________ eclipselink-dev mailing list [email protected] https://dev.eclipse.org/mailman/listinfo/eclipselink-dev On Mon, Jul 11, 2011 at 2:25 PM, Andrus Adamchik <[email protected]> wrote: > Sigh... I feel like we should've used common sense and built our own QL based > on EJBQL/JPQL instead of blindly following the JPA spec. Which among other > things says "The Java Persistence query language includes [...] built-in > functions, which may be used in the WHERE or HAVING clause of a query." So no > ORDER BY in this list :-/ > >> I searched the code but those 10k lines are quite a challenge. > > You betcha. > > We should prolly add it. Hope most DB's support it. > > Andrus > > On Jul 11, 2011, at 5:36 PM, Marek Šabo wrote: >> Hi all, >> >> I was trying to push a query through parser today and it didn't make it. >> >> "SELECT stt FROM SubTaskType stt ORDER BY ABS(stt.code) ASC" >> >> Exception is below, my question being whether it is correct behaviour or a >> bug. I know mysql allows such syntax and oracle too (it allows numeric >> sorting of strings). I searched the code but those 10k lines are quite a >> challenge. >> >> Regards, >> >> Marek >> >> Caused by: org.apache.cayenne.ejbql.parser.ParseException: Encountered " >> "ABS" "ABS "" at line 1, column 42. >> Was expecting one of: >> <IDENTIFIER> ... >> "db:" ... >> >> at >> org.apache.cayenne.ejbql.parser.EJBQL.generateParseException(EJBQL.java:9418) >> at >> org.apache.cayenne.ejbql.parser.EJBQL.jj_consume_token(EJBQL.java:9297) >> at org.apache.cayenne.ejbql.parser.EJBQL.path(EJBQL.java:623) >> at org.apache.cayenne.ejbql.parser.EJBQL.orderby_item(EJBQL.java:5138) >> at >> org.apache.cayenne.ejbql.parser.EJBQL.orderby_clause(EJBQL.java:5097) >> at >> org.apache.cayenne.ejbql.parser.EJBQL.select_statement(EJBQL.java:93) >> at org.apache.cayenne.ejbql.parser.EJBQL.parseQuery(EJBQL.java:41) >> at >> org.apache.cayenne.ejbql.parser.EJBQL$EJBQLDefaultParser.compile(EJBQL.java:30) >> > >
