Opened 14 years ago

Closed 14 years ago

#941 closed defect (fixed)

SQLException when exporting "merged on reporters" with BioAssaySet exporter on Postgres

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: critical Milestone: BASE 2.6.1
Component: coreplugins Version:
Keywords: Cc:

Description

This may be related to #939, but I am not sure. In any case, here is how to reproduce it:

  1. Assuming that you have an experiment with some data analyzed in it
  2. Go to the Bioassaysets tree view
  3. Choose any bioassayset and click on the Export tool. Select the Bioassay set file exporter plugin.
  4. Choose BASEFile as the file format and then on the next page
    • Average on reporters = true
    • Export as matrix or serial file = matrix
    • Fields and reporter doesn't matter
  5. Download the exported file and open it in an editor
  6. The contents of the file is a stacktrace:
net.sf.basedb.core.BaseException: ERROR: column "spt.position" must appear in the GROUP BY clause or be used in an aggregate function
	at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:206)
	at net.sf.basedb.core.DynamicQuery.iterate(DynamicQuery.java:48)
	at net.sf.basedb.plugins.BioAssaySetExporter.exportBaseFileSectionSpots(BioAssaySetExporter.java:524)
	at net.sf.basedb.plugins.BioAssaySetExporter.exportBaseFileMatrix(BioAssaySetExporter.java:336)
	at net.sf.basedb.plugins.BioAssaySetExporter.performExport(BioAssaySetExporter.java:1263)
	at net.sf.basedb.core.plugin.AbstractExporterPlugin.doExport(AbstractExporterPlugin.java:186)
	at net.sf.basedb.core.PluginResponse.downloadImmediately(PluginResponse.java:159)
	at org.apache.jsp.common.plugin.index_jsp._jspService(index_jsp.java:557)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at net.sf.basedb.clients.web.servlet.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:72)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: ERROR: column "spt.position" must appear in the GROUP BY clause or be used in an aggregate function
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:180)
	at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:168)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
	... 1 more

The problem is that when merging on reporters there is GROUP BY clause on the query. The "position" column is not in the GROUP BY and thus, it can't be used in the SELECT part either (unless we use COUNT(position), AVG(position), etc. but those expressions has no meaning.

So, the "position" must be removed from the query when merging on reporters. The problem with this is that the export code seems to base a lot of it's logic based on the position number. For example, to fill in blanks to make sure the columns line up when there are missing values in the data. So, when merging on reporter the position value must be replaced by something else, possible the reporter ID.

MySQL isn't very picky about this and allows the query to run. The problem is that for each reporter it selects a random position value from the merged reporters. This value may be different for different bioassays which means that the columns doesn't line up and that the same reporter is appearing on more than one line (a new line is started each time the position value changes). I guess this is the effect that is seen in ticket #939.

Change History (2)

comment:1 Changed 14 years ago by Nicklas Nordborg

Owner: changed from everyone to Nicklas Nordborg
Status: newassigned

comment:2 Changed 14 years ago by Nicklas Nordborg

Resolution: fixed
Status: assignedclosed

(In [4167]) Fixes #941: SQLException when exporting "merged on reporters" with BioAssaySet? exporter on Postgres References #939: BioAssaySetExporter? doesn't export BASEfiles for multiple array designs correctly

Note: See TracTickets for help on using tickets.