Unknown column 'pos.reporter_id' in 'field list' when using 'merge on reporter' in BioAssaySetExporter

[Nicklas Nordborg]

1. From any experiment with data, select a bioassay set and then the 'Export' button.
2. Select 'BASEFile' as the file format on the first screen.
3. On the second screen, set 'Average on reporters' to true and select for example ch(1) and ch(2) in the fields list. Do not select any reporter-related formula or anything from the 'Reporter' list.
4. Continue the wizard until the end and wait for the job to begin.

It will end with:

net.sf.basedb.core.BaseException: Unknown column 'pos.reporter_id' in 'field list'
at net.sf.basedb.core.QueryExecutor.getFutureResult(QueryExecutor.java:194)
at net.sf.basedb.core.QueryExecutor.executeQuery(QueryExecutor.java:91)
at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:198)
at net.sf.basedb.core.DynamicQuery.iterate(DynamicQuery.java:46)
at net.sf.basedb.plugins.BioAssaySetExporter.exportBaseFileSectionSpots(BioAssaySetExporter.java:518)
at net.sf.basedb.plugins.BioAssaySetExporter.exportBaseFileMatrix(BioAssaySetExporter.java:326)
at net.sf.basedb.plugins.BioAssaySetExporter.performExport(BioAssaySetExporter.java:1322)
at net.sf.basedb.core.plugin.AbstractExporterPlugin.run(AbstractExporterPlugin.java:144)
at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:106)
at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:504)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'pos.reporter_id' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:224)
at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:212)
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:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
... 1 more

The generated SQL is:

SELECT AVG(`spt`.`ch1`) AS `a19101568`, 
   AVG(`spt`.`ch2`) AS `a24804944`, 
   `pos`.`reporter_id` AS `_position`, 
   `spt`.`column` AS `_column` 
FROM `base29dynamic`.`D2Spot` `spt` 
WHERE `spt`.`cube` = 1 AND `spt`.`layer` = 2 
GROUP BY `spt`.`column`, `pos`.`reporter_id` 
ORDER BY `pos`.`reporter_id` ASC, `spt`.`column` ASC

Eg. it is missing a join to the 'D2Pos' table which contains the 'pos.reporter_id' column.

[Nicklas Nordborg]

(In [4694]) Fixes #1204: Unknown column 'pos.reporter_id' in 'field list' when using 'merge on reporter' in BioAssaySetExporter?