Experiment explorer not working in Postgres

[Nicklas Nordborg]

Error message  	ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Stack trace 	

...at AbstractSqlQuery.iterate(AbstractSqlQuery.java:193)
...at net.sf.basedb.clients.web.ExperimentExplorer.getReporter(ExperimentExplorer.java:517)
...at org.apache.jsp.views.experiments.explorer.view.view_jsp._jspService(view_jsp.java:232)

SQL that was generated:

SELECT DISTINCT "rpt"."id" AS "id" 
FROM "base2"."dynamic"."D40363Spot" "spt" 
INNER JOIN "base2"."dynamic"."D40363Pos" AS "pos" 
ON "pos"."cube" = "spt"."cube" 
AND "pos"."position" = "spt"."position"  
INNER JOIN "Reporters" AS rpt 
ON rpt.id = pos."reporter_id"  
WHERE "spt"."cube" = 2 AND "spt"."layer" = 1 
ORDER BY "rpt"."external_id" ASC limit ?

Apparently, we are ordering by "external_id" which is not in the SELECT list and Postgres doesn't like that.

[Nicklas Nordborg]

We must make sure that if we use the DISTINCT keyword the columns that appear in the ORDER BY statement must also be part of the SELECT list.

But we don't want to do that if not needed so it is probably a good idea to create a DbEngine.selectOrderByColumnsIfDistinct() method which returns FALSE by default and TRUE for Postgres. Then the ItemContext.configureQuery can check which columns we are selecting and which columns we are sorting by and automatically add any missing columns to the selection list.

[Nicklas Nordborg]

(In [2812]) Fixes #414: Experiment explorer not working in Postgres