[Sqlalchemy-tickets] Issue #4264: Passing NaN to Float column in Oracle leads to crash (zzzeek/sqla

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 4264: Passing NaN to Float column in Oracle leads to crash
https://bitbucket.org/zzzeek/sqlalchemy/issues/4264/passing-nan-to-float-column-in-oracle

Leonid Butenko:

Hi,

I think it might be somehow related to the #4259, but with floats now.

I use Oracle 11g, cx_oracle 6.3, oracle_instantclient 11.2 and sqlalchemy 1.2.7

here is my test program
```
import sqlalchemy as sa
import sqlalchemy.orm
import datetime
import sqlalchemy.ext.declarative

connection_string = "oracle://scott:tiger@oracle1120/"

engine = sa.create_engine(connection_string, echo = True, max_overflow = 0, pool_size = 3)
_session = sa.orm.sessionmaker()
_session.configure(bind=engine)
session = _session()

table = sa.Table(
    "nan_test", sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("fval", sa.Float)
)
table.drop(engine)
table.create(engine, checkfirst=True)

Base = sa.ext.declarative.declarative_base()

class Pod(Base):
    __table__ = sa.Table(
        "nan_test", Base.metadata, autoload = True, autoload_with = engine)

session.add(Pod(id=1, fval=float('nan')))
session.commit()
```

Here is the error I get
```
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1055: value 
is not a number (NaN) and cannot be used in Oracle numbers [SQL: 
u'INSERT INTO nan_test (id, fval) VALUES (:id, :fval)'] [parameters: 
{'id': 1, 'fval': nan}] (Background on this error 
at: http://sqlalche.me/e/4xp6)
```

Here is the way how to reproduce it in anaconda docker:
```
>docker run -it --rm continuumio/miniconda bash
>conda install -c conda-forge -c anaconda  sqlalchemy cx_oracle=6.0.3 oracle-instantclient libaio

The following NEW packages will be INSTALLED:

    cx_oracle:            6.0.3-py27hc82e58e_0  anaconda
    libaio:               0.3.110-h14c3975_0    anaconda
    oracle-instantclient: 11.2.0.4.0-0          anaconda
    sqlalchemy:           1.2.7-py27h65ede16_0  conda-forge

>python test_nan.py

2018-05-22 20:24:56,975 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2018-05-22 20:24:56,975 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,977 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2018-05-22 20:24:56,978 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,979 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2018-05-22 20:24:56,979 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,981 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
2018-05-22 20:24:56,982 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,984 INFO sqlalchemy.engine.base.Engine
DROP TABLE nan_test
2018-05-22 20:24:56,984 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:57,031 INFO sqlalchemy.engine.base.Engine COMMIT
2018-05-22 20:24:57,033 INFO sqlalchemy.engine.base.Engine SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2018-05-22 20:24:57,033 INFO sqlalchemy.engine.base.Engine {'name': u'NAN_TEST', 'schema_name': u'Test'}
2018-05-22 20:24:57,039 INFO sqlalchemy.engine.base.Engine
CREATE TABLE nan_test (
        id INTEGER NOT NULL,
        fval FLOAT,
        PRIMARY KEY (id)
)

2018-05-22 20:24:57,039 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:57,055 INFO sqlalchemy.engine.base.Engine COMMIT
2018-05-22 20:24:57,057 INFO sqlalchemy.engine.base.Engine SELECT table_name, compression, compress_for FROM ALL_TABLES WHERE table_name = :table_name AND owner = :owner
2018-05-22 20:24:57,057 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,064 INFO sqlalchemy.engine.base.Engine
            SELECT col.column_name, col.data_type, col.char_length,
              col.data_precision, col.data_scale, col.nullable,
              col.data_default, com.comments            FROM all_tab_columns col
            LEFT JOIN all_col_comments com
            ON col.table_name = com.table_name
            AND col.column_name = com.column_name
            AND col.owner = com.owner
            WHERE col.table_name = :table_name
         AND col.owner = :owner  ORDER BY col.column_id
2018-05-22 20:24:57,064 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,069 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos,
ac.search_condition,
ac.delete_rule
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P', 'U', 'C')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2018-05-22 20:24:57,069 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,102 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name,
b.index_type, b.uniqueness, b.compression, b.prefix_length
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2018-05-22 20:24:57,102 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST', 'schema': u'Test'}
2018-05-22 20:24:57,105 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name,
b.index_type, b.uniqueness, b.compression, b.prefix_length
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2018-05-22 20:24:57,105 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST', 'schema': u'Test'}
2018-05-22 20:24:57,106 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos,
ac.search_condition,
ac.delete_rule
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P', 'U', 'C')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2018-05-22 20:24:57,106 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,136 INFO sqlalchemy.engine.base.Engine
            SELECT comments
            FROM user_tab_comments
            WHERE table_name = :table_name

2018-05-22 20:24:57,137 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,140 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-05-22 20:24:57,141 INFO sqlalchemy.engine.base.Engine INSERT INTO nan_test (id, fval) VALUES (:id, :fval)
2018-05-22 20:24:57,141 INFO sqlalchemy.engine.base.Engine {'id': 1, 'fval': nan}
2018-05-22 20:24:57,142 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "test_nan.py", line 29, in <module>
    session.commit()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements
    execute(statement, multiparams)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1055: value is not a number (NaN) and cannot be used in Oracle numbers [SQL: u'INSERT INTO nan_test (id, fval) VALUES (:id, :fval)'] [parameters: {'id': 1, 'fval': nan}] (Background on this error at: http://sqlalche.me/e/4xp6)

```

I'm not sure whether it is the correct place to report about this problem. It might be cx_oracle related issue. From my point of view, passing NaNs to floating columns should be a normal practice at least in the scientific world, so I thought sqlalchemy might handle this situation as well. Pls advise! 

The solution I found so far is to downgrade the version of cx_oracle to 6.0.1 or ealier.
```
>  conda install cx_oracle=6.0b2

The following packages will be DOWNGRADED:

    cx_oracle: 6.0.3-py27hc82e58e_0 --> 6.0b2-py27_0
> python test.py
....
2018-05-22 20:53:13,774 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST'}
2018-05-22 20:53:13,780 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-05-22 20:53:13,782 INFO sqlalchemy.engine.base.Engine INSERT INTO nan_test (id, fval) VALUES (:id, :fval)
2018-05-22 20:53:13,782 INFO sqlalchemy.engine.base.Engine {'id': 1, 'fval': nan}
2018-05-22 20:53:13,835 INFO sqlalchemy.engine.base.Engine COMMIT
```
So the problem should appear somewhere between version 6.0.1 and 6.0.3 of cx_Oracle and still exist in the most recent version 6.3.

[Sqlalchemy-tickets] Issue #4264: Passing NaN to Float column in Oracle leads to crash (zzzeek/sqla

[Sqlalchemy-tickets] Issue #4264: Passing NaN to Float column in Oracle leads to crash (zzzeek/sqlalchemy)