Opened 14 years ago

Closed 14 years ago

#708 closed defect (fixed)

"NaN" and "Infinity" values in files causes SqlException

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: major Milestone: BASE 2.4
Component: core Version:
Keywords: Cc:

Description

The string "NaN" when parsed by Java is seen as a valid numeric value and is converted to either Double.NaN or Float.NaN. Databases usually don't like NaN values:

Caused by: net.sf.basedb.core.BaseException: 'NaN' is not a valid numeric or approximate numeric value
        at net.sf.basedb.core.BasicBatcher.addToBatch(BasicBatcher.java:564)
        at net.sf.basedb.core.BasicBatcher.insert(BasicBatcher.java:315)
        at net.sf.basedb.core.RawDataBatcher.doInsert(RawDataBatcher.java:405)
        at net.sf.basedb.core.RawDataBatcher.insert(RawDataBatcher.java:349)
        at net.sf.basedb.plugins.IlluminaRawDataImporter.handleData(IlluminaRawDataImporter.java:404)
        at net.sf.basedb.plugins.AbstractFlatFileImporter.doImport(AbstractFlatFileImporter.java:605)
        ... 5 more
Caused by: java.sql.SQLException: 'NaN' is not a valid numeric or approximate numeric value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.PreparedStatement.setDouble(PreparedStatement.java:2682)
        at com.mysql.jdbc.PreparedStatement.setNumericObject(PreparedStatement.java:2909)
        at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3126)
        at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3032)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:391)
        at net.sf.basedb.core.BasicBatcher.addToBatch(BasicBatcher.java:553)
        ... 10 more

So, we have to convert NaN to null at the batcher level, or possible catch this at the string parsing level and use the regular error handling. It would be best to do it at the batcher level, otherwise we have to go through a lot of places with string parsing code.

This is related to ticket #520, but that only solved the problem for intensity columns in the spot batcher.

Change History (4)

comment:1 Changed 14 years ago by Nicklas Nordborg

Doing it at the batcher level turned out to a bad idea. The reason is that we have validation code checking for null values happening before we get to that part of the code. Since we want to handle null values properly at all times, the NaN->null conversion must happen at the parsing level. As it turns out, this can be fixed in the Type class.

comment:2 Changed 14 years ago by Nicklas Nordborg

Owner: changed from everyone to Nicklas Nordborg
Status: newassigned

comment:3 Changed 14 years ago by Nicklas Nordborg

Summary: Batchers fail if passed NaN values"NaN" and "Infinity" values in files causes SqlException

comment:4 Changed 14 years ago by Nicklas Nordborg

Resolution: fixed
Status: assignedclosed

(In [3624]) Fixes #708: "NaN" and "Infinity" values in files causes SqlException?

Note: See TracTickets for help on using tickets.