# Database returns nan instead of NULL **URL:** \ **Category:** Ignition\ **Created:** [November 13, 2012, 10:53am UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409 "2012-11-13T10:53:51Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![systemparadox](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@systemparadox](https://forum.inductiveautomation.com/u/systemparadox)\ **Post date:** [November 13, 2012, 10:53am UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/1 "2012-11-13T10:53:51Z")
The database is inconsistently returning nan instead of NULL. This only appears to happen when selecting more than one row and only if all rows are numeric. ```auto CREATE TABLE test_nan_bug (alpha DOUBLE, beta DOUBLE, str VARCHAR(40)); INSERT INTO test_nan_bug VALUES(); ``` ```auto data = system.db.runQuery(""" SELECT alpha FROM test_nan_bug """) for row in data: print list(row) data = system.db.runQuery(""" SELECT alpha, beta FROM test_nan_bug """) for row in data: print list(row) data = system.db.runQuery(""" SELECT alpha, beta, str FROM test_nan_bug """) for row in data: print list(row) ``` ```auto [None] [nan, nan] [None, None, None] ``` [viewtopic.php?f=70&t=5898&p=15794](http://www.inductiveautomation.com/forum/viewtopic.php?f=70&t=5898&p=15794) According to this thread, this behaviour is deliberate?? Some sort of optimisation? I have spent over an hour tracking this down and I cannot find any mention of it in the documentation. Even if this is deliberate behaviour, it seems somewhat inconsistent. Why does it not happen for single-column datasets? Why does mixing it with non-numeric columns deactivate it for all columns? Some clarification on this issue would be appreciated. We are using Ignition 7.5.2 (b1146). Thanks. ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [November 13, 2012, 8:10pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/2 "2012-11-13T20:10:47Z")
It appears to me that behavior comes from MySQL’s JDBC driver. It seems to only happen when you bring back two columns that are both floats or doubles. NaN is null for floating point values. I will have to look into it further. In the meantime you can you get around it by adding more columns to the query? ---
**Author:** ![systemparadox](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@systemparadox](https://forum.inductiveautomation.com/u/systemparadox)\ **Post date:** [November 14, 2012, 9:48am UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/3 "2012-11-14T09:48:42Z")
Yes, for this instance I am adding a string column as a workaround. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [November 16, 2012, 10:36pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/4 "2012-11-16T22:36:21Z")
This is not the MySQL driver: it is Ignition itself. Your query is using the optimized format for datasets which uses java primitive arrays to save memory. If you go through the Dataset interface :dataset.getValueAt(row,col) it will convert the values back into nulls for you. ---
**Author:** ![systemparadox](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@systemparadox](https://forum.inductiveautomation.com/u/systemparadox)\ **Post date:** [November 22, 2012, 9:10am UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/5 "2012-11-22T09:10:04Z")
Why doesn’t PyDataSet use this method? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [November 26, 2012, 5:32pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/6 "2012-11-26T17:32:20Z")
![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") Good question…it does. Do you have a serialized window that you can give me that shows what you’re describing? ---
**Author:** ![systemparadox](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@systemparadox](https://forum.inductiveautomation.com/u/systemparadox)\ **Post date:** [November 27, 2012, 10:44am UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/7 "2012-11-27T10:44:11Z")
The problem can be reproduced using the code given in my original posting (just create the table and paste it into the script playground). ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [November 30, 2012, 6:33pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/8 "2012-11-30T18:33:05Z")
A ticket has been made to look into this issue ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [December 14, 2012, 7:08pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/9 "2012-12-14T19:08:14Z")
This has been fixed for 7.5.5 ---
**Author:** ![systemparadox](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@systemparadox](https://forum.inductiveautomation.com/u/systemparadox)\ **Post date:** [December 17, 2012, 3:09pm UTC](https://forum.inductiveautomation.com/t/database-returns-nan-instead-of-null/5409/10 "2012-12-17T15:09:38Z")
Excellent. Thanks once again Carl.