Opened 15 years ago

Closed 15 years ago

#414 closed defect (fixed)

Experiment explorer not working in Postgres

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: major Milestone: BASE 2.1
Component: core Version:
Keywords: Cc:

Description (last modified by 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.

Change History (3)

comment:1 Changed 15 years ago by Nicklas Nordborg

Description: modified (diff)
Status: newassigned

comment:2 Changed 15 years ago by Nicklas Nordborg

Component: webcore

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.

comment:3 Changed 15 years ago by Nicklas Nordborg

Resolution: fixed
Status: assignedclosed

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

Note: See TracTickets for help on using tickets.