model browsing not possible with PostGreSQL

After setting up a local jummp-instance for development as per email instructions (thanks Mihai!), I got everything up and running.

The home-page shows up alright (using the DDMoRe skin), and most links work. Only the "browse models" link leads to a Grails Runtime Exception (stacktrace below)

This is when running PostgreSQL 9.2.7 on OpenSuse 13.1 (amd_64)

Grail error page

Error 500: Executing action [list] of controller [net.biomodels.jummp.webapp.SearchController] in plugin [jummp-plugin-web-application] caused exception: Runtime error executing action
Servlet: grails
URI: /jummp/grails/search/list.dispatch
Exception Message: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 951
Caused by: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 951
Class: SearchController
At Line: [69]

I stole the query by inserting a quick 'println' :

SELECT DISTINCT m FROM Revision AS r, AclEntry AS ace
JOIN r.model AS m JOIN r.owner as u
JOIN ace.aclObjectIdentity AS aoi
JOIN aoi.aclClass AS ac
JOIN ace.sid AS sid
WHERE
r.uploadDate=(SELECT MAX(r2.uploadDate) from Revision r2 where r.model=r2.model) AND aoi.objectId = r.id
AND ac.className = :className
AND sid.sid IN (:roles)
AND ace.mask IN (:permissions)
AND ace.granting = true
AND r.deleted = false
 AND m.deleted = false 
ORDER BY
r.uploadDate desc

Though this looks perfectly fine, it is apparently not valid SQL. The SQL92 spec (apparently) defines that, in case DISTINCT is specified, ORDER BY can only reference columns from the SELECT clause.

Therefore PostGres, and interestingly Microsoft SQL too, don't allow this MySQL is more agreeable, and violates the standard here to allow it.

This postgresql mailing list message provides some background

Full Stacktrace

org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Position: 946
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at net.biomodels.jummp.core.ModelService.getAllModels(ModelService.groovy:352)
    at org.perf4j.aop.AbstractTimingAspect$1.proceed(AbstractTimingAspect.java:47)
    at org.perf4j.aop.AgnosticTimingAspect.runProfiledMethod(AgnosticTimingAspect.java:52)
    at org.perf4j.aop.AbstractTimingAspect.doPerfLogging(AbstractTimingAspect.java:45)
    at net.biomodels.jummp.webapp.SearchController.browseCore(SearchController.groovy:243)
    at net.biomodels.jummp.webapp.SearchController$_closure2.doCall(SearchController.groovy:69)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)

This Query is constructed by ModelService.getAllModels(), from lines 272-352.

Grail error page

Comments (7)