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

[Nicklas Nordborg]

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.

[Nicklas Nordborg]

(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