Opened 17 years ago

Closed 17 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 AbstractSqlQuery.iterate( net.sf.basedb.clients.web.ExperimentExplorer.getReporter( org.apache.jsp.views.experiments.explorer.view.view_jsp._jspService(

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 = 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 by Nicklas Nordborg, 17 years ago

Description: modified (diff)
Status: newassigned

comment:2 by Nicklas Nordborg, 17 years ago

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 by Nicklas Nordborg, 17 years ago

Resolution: fixed
Status: assignedclosed

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

Note: See TracTickets for help on using tickets.