Opened 16 years ago
Closed 16 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 16 years ago by
comment:2 Changed 16 years ago by
Owner: | changed from everyone to Nicklas Nordborg |
---|---|
Status: | new → assigned |
comment:3 Changed 16 years ago by
Summary: | Batchers fail if passed NaN values → "NaN" and "Infinity" values in files causes SqlException |
---|
comment:4 Changed 16 years ago by
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
(In [3624]) Fixes #708: "NaN" and "Infinity" values in files causes SqlException?
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.