SpotBatcher doesn't handle Float.NaN values

[base]

From Steve Taylor

An is generated error when trying to create a New BioAssaySet using the Formula intensity calculator after importing (seemingly OK) data from a file. For example: I am using 'Mean FG' for this calculation. I am trying to extract intensities based on 2 channel ScanArray data (currently using GenePix format but only using some of the columns). The batch import *seems* to work ok. But I get:

Output from the plugin

Name  	Run plugin: Formula intensity calculator
Description 	
Priority 	5 (1 = highest, 10 = lowest)
Status 	Error: Data truncation: Out of range value adjusted for column 'ch1' at row 1
Percent complete 	100%
Created 	2007-03-20 14:48:34
Started 	2007-03-20 14:49:19
Ended 	2007-03-20 14:49:21
Server 	servalanP
Plugin 	Formula intensity calculator
Configuration 	- none -

A typical error log is:

ERROR net.sf.basedb.core.batcher.sql:159 - INSERT INTO `base2dynamic`.`D15Spot` (`cube`, `layer`, `column`, `position`, `ch1`, `ch2`) VALUES (7, 1, ?, ?, ?, ?)
java.sql.BatchUpdateException: Data truncation: Out of range value adjusted for column 'ch1' at row 1
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:665)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at net.sf.basedb.core.SpotBatcher.flush(SpotBatcher.java:155)
        at net.sf.basedb.core.AbstractBatcher.close(AbstractBatcher.java:117)
        at net.sf.basedb.util.IntensityCalculatorUtil.createRootBioAssaySet(IntensityCalculatorUtil.java:447)
        at net.sf.basedb.plugins.IntensityCalculatorPlugin.run(IntensityCalculatorPlugin.java:256)
        at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:88)
        at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:420)
        at java.lang.Thread.run(Thread.java:595)
20-03-2007 14:49:21 ERROR net.sf.basedb.core.jobqueue:434 - Exception while executing job: Job[id=203; name=Run plugin: Formula intensity calculator]
net.sf.basedb.core.BaseException: Data truncation: Out of range value adjusted for column 'ch1' at row 1
        at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:428)
        at java.lang.Thread.run(Thread.java:595)
Caused by: net.sf.basedb.core.BaseException: Data truncation: Out of range value adjusted for column 'ch1' at row 1
        at net.sf.basedb.core.SpotBatcher.flush(SpotBatcher.java:161)
        at net.sf.basedb.core.AbstractBatcher.close(AbstractBatcher.java:117)
        at net.sf.basedb.util.IntensityCalculatorUtil.createRootBioAssaySet(IntensityCalculatorUtil.java:447)
        at net.sf.basedb.plugins.IntensityCalculatorPlugin.run(IntensityCalculatorPlugin.java:256)
        at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:88)
        at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:420)
        ... 1 more
Caused by: java.sql.BatchUpdateException: Data truncation: Out of range value adjusted for column 'ch1' at row 1
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:665)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at net.sf.basedb.core.SpotBatcher.flush(SpotBatcher.java:155)

The data appears to have loaded in RawDataGenePix ok apart from the last entry (example below)

+-------+---------+----------------+------------+-------------+----------+------+------+-------+------------+------------+------+--------+----------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+-----------+-----------+--------+-----------+-----------+-------+---------+------+
| id    | version | rawbioassay_id | feature_id | reporter_id | position | x    | y    | block | metagrid_x | metagrid_y | row  | column | diameter | ch1_fg_median | ch1_fg_mean | ch1_fg_sd | ch1_bg_median | ch1_bg_mean | ch1_bg_sd | ch1_perc_sd1 | ch1_perc_sd2 | ch1_perc_sat | ch2_fg_median | ch2_fg_mean | ch2_fg_sd | ch2_bg_median | ch2_bg_mean | ch2_bg_sd | ch2_perc_sd1 | ch2_perc_sd2 | ch2_perc_sat | ratios_sd | rgn_ratio | rgn_r2 | fg_pixels | bg_pixels | flags | m_value | cv   |
+-------+---------+----------------+------------+-------------+----------+------+------+-------+------------+------------+------+--------+----------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+-----------+-----------+--------+-----------+-----------+-------+---------+------+
| 34388 |       0 |            192 |       NULL |        NULL |     3073 | NULL | NULL |  NULL |       NULL |       NULL | NULL |   NULL |     NULL |          NULL |        NULL |      NULL |          NULL |        NULL |      NULL |         NULL |         NULL |         NULL |          NULL |        NULL |      NULL |          NULL |        NULL |      NULL |         NULL |         NULL |         NULL |      NULL |      NULL |   NULL |      NULL |      NULL |  NULL |    NULL | NULL | 
+-------+---------+----------------+------------+-------------+----------+------+------+-------+------------+------------+------+--------+----------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+---------------+-------------+-----------+---------------+-------------+-----------+--------------+--------------+--------------+-----------+-----------+--------+-----------+-----------+-------+---------+------+

After deleting this NULL row it the intensity extraction works fine.

Ideally the Raw Data Importer plugin should not import this row in the first place or at least flag it if it finds lines that are empty.

[Nicklas Nordborg]

An empty line is legal and will be imported. If you want to avoid this you have to set the max/min number of columns in your raw data importer.

The real problem seems to be that the intensity calculator isn't ignoring null values. I need to check this a bit more before I can tell anything about why the exception is thrown.

[Nicklas Nordborg]

I have now investigated this further. The error only happens if the raw bioassays in the experiment doesn't have an array design or have different array designs. This triggers an alternate route where the NULL values are converted into Float.NaN values. When those are passed back to the database it comaplains...

The solution is to test for NaN values and convert them back to NULL. This affects the SpotBatcher and also other batchers which take float:s as input. This will make the result identical to the other route (where an array design is present) where the NULL values are kept as NULL during the calculations.

[Nicklas Nordborg]

(In [3210]) Fixes #520: SpotBatcher doesn't handle Float.NaN values

Also changed extra value tables to allow null values.