how to query data with float field is/isn't NaN value

[flyly0755]

Describe the bug one table with a float field which contains NaN value, how to query data with this field NaN value or without NaN value? try code as below not works

res = session.query(TableOrm).filter(TableOrm.floatField1 is not float('NaN')).all()

Try query raw sql can work

SELECT * FROM TableOrm WHERE not isNaN(floatField1)

To Reproduce As given above

Expected behavior Can correctly query data with float field is/isn't NaN value

Versions

• Version of package with the problem. python 3.9.11 SQLAlchemy==1.4.49 SQLAlchemy-Utils==0.41.1 clickhouse-sqlalchemy==0.2.4

[flyly0755]

@xzkostyan can you help to take a look at this issue?

[markalexander]

To generate

SELECT * FROM TableOrm WHERE not isNaN(floatField1)

I would think you just do something like:

res = session.query(TableOrm).filter(~func.isNaN(TableOrm.floatField1)).all()

Does this not work?

[flyly0755]

res = session.query(TableOrm).filter(~func.isNaN(TableOrm.floatField1)).all()

Yes, I took a try, this method is ok, can filter NaN records or non-NaN records