Database returns nan instead of NULL

Ignition
1

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.

CREATE TABLE test_nan_bug (alpha DOUBLE, beta DOUBLE, str VARCHAR(40));
INSERT INTO test_nan_bug VALUES();
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)
[None]
[nan, nan]
[None, None, None]

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.

2

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?

3

Yes, for this instance I am adding a string column as a workaround.

4

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.

5

Why doesn’t PyDataSet use this method?

6

:scratch: Good question…it does. Do you have a serialized window that you can give me that shows what you’re describing?

7

The problem can be reproduced using the code given in my original posting (just create the table and paste it into the script playground).

8

A ticket has been made to look into this issue

9

This has been fixed for 7.5.5

10

Excellent. Thanks once again Carl.