New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Insertion of Nan in Oracle numbers produces corrupt data #91

Closed
dark-epistemology opened this Issue Oct 5, 2017 · 15 comments

Comments

Projects
None yet
4 participants
@dark-epistemology
Copy link

dark-epistemology commented Oct 5, 2017

It is possible to insert nan values into Oracle numbers.

#!/usr/bin/env python
from numpy import nan
import cx_Oracle

connection = cx_Oracle.connect('/@TSTRP0')
cur = connection.cursor()

cur.execute( 'drop table ieee754' );
cur.execute( 'create table ieee754( id varchar2(10), oracle_number number, native_float binary_double )' )
cur.execute( "insert into ieee754(id,oracle_number,native_float) values ( 'Nan', :1, :2 )", ( nan, nan ))

connection.commit()

This produces the following data in the database:

SCOTT@TSTRP0> set null <NULL>
SCOTT@TSTRP0> select id, ORACLE_NUMBER, dump(oracle_number,16) xON, native_float, dump(native_float,16) xBD from ieee754;

ID			  ORACLE_NUMBER XON			  NATIVE_FLOAT XBD
------------------------- ------------- ------------------------- ------------ ---------------------------------------------
Nan					Typ=2 Len=1: c1 		   Nan Typ=101 Len=8: ff,f8,0,0,0,0,0,0

0xC1 is not an Oracle number:

SCOTT@TSTRP0> select to_char( ORACLE_NUMBER ) from ieee754;
select to_char( ORACLE_NUMBER ) from ieee754
                *
ERROR at line 1:
ORA-01722: invalid number

Its use may cause crashes, CPU spin or wrong results.

Answer the following questions:

  1. What is your version of Python? Is it 32-bit or 64-bit?
Python 2.7.6 (default, Feb 12 2015, 15:58:12) 
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2

64 bits.
2. What is your version of cx_Oracle?
Above test was done in 5.0.3, but I've reproduced with 5.1 and 5.2

  1. What is your version of the Oracle client (e.g. Instant Client)? How was it
    installed? Where is it installed?
    11.2.0.4; was installed with

  2. What is your version of the Oracle Database?
    Reproduced in 11.2.0.4 and 12.1.0.2

  3. What is your OS and version?

Linux myvm 2.6.32-279.22.1.el6.x86_64 #1 SMP Sun Jan 13 09:21:40 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Client
6. What compiler version did you use? For example, with GCC, run
gcc --version.
GCC 4.4.6 20120305 (Red Hat 4.4.6-4)
7. What environment variables did you set? How exactly did you set them?
Not relevant here
8. What exact command caused the problem (e.g. what command did you try to
install with)? Who were you logged in as?
See above
9. What error(s) you are seeing?
None during the insertion of the Nans, but my users do operations with them and this has produced core dumps from the shadow processes (or worse, shared servers), crashed the database once or twice and produced wrong results.

@cjbj cjbj added the bug label Oct 10, 2017

anthony-tuininga added a commit that referenced this issue Oct 14, 2017

Update ODPI-C
- prevent use of NaN with Oracle numbers since it produces corrupt data
  (#91)
- correct handling of double values when converted to float values.
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Oct 14, 2017

Commits have been added to prevent the use of NaN. This will be included in the 6.0.3 and 6.1 releases when they are made.

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Oct 19, 2017

Thanks a lot, we'll test it ASAP.
Cheers

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Nov 6, 2017

Hi Anthony,

It works, thanks a lot; problem is, our guys here are nowhere near going to 6.x, since ODPI seems to break some of their code. Is it possible to get a backport to 5.3?

Cheers,

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 6, 2017

Glad to hear it works for you. What does ODPI-C appear to break? I'd rather correct that, if possible, then consider a backport to 5.3.

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Nov 13, 2017

Finally got the guys, sorry for the delay.
Apparently, they were used to close connections without explicitly closing cursors, and most of their code gets DPI-1054: connection cannot be closed when open statements or LOBs exist.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 13, 2017

Ok. That makes sense. That code was added to prevent cursor and LOB leaks with session pools, but it does create a bit of a headache if your code simply closes the connection without closing the dependent objects first. You should be able to simply remove the connection.close() calls as cx_Oracle automatically cleans up after itself -- providing you aren't storing cursors, LOBs or connections in structures that are retained -- or add the necessary cursor close calls if you prefer that approach. I understand that either method will take time to test.

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Nov 15, 2017

Just had a meeting with the guys, and they're willing to review the existing code base to fix the cursor closing issue; but, as you remarked, this is going to take some time. And they convinced me that with the global growth of scientific python (Numpy+pandas+cx_oracle), their use case (NaNs) was bound to be representative of a lot of other users.
So they insisted on my requesting a backport to 5.3, which I respectfully transmit
Cheers,
Fred

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 15, 2017

I have backported the change to the v5.x branch. It seems to work for me. Please let me know if it does for you, too. Assuming it does, I can then consider making a 5.3.1 release.

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Nov 16, 2017

Thanks a lot Anthony, I'll try and do the test before the week-end.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Nov 16, 2017

You're welcome. Don't rush for my sake, though. :-)

@dark-epistemology

This comment has been minimized.

Copy link
Author

dark-epistemology commented Nov 22, 2017

(NaN)$ ./tstNan.py
Testing cx_Oracle version : 5.3
value is not a number (NaN) and cannot be used in Oracle numbers

Thanks again!

@tres-pitt

This comment has been minimized.

Copy link

tres-pitt commented Feb 13, 2018

so, is there any way to insert (what sql developer would display as) '(null') into a table in the status quo?

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Feb 13, 2018

For database null values you should use the Python value None.

@tres-pitt

This comment has been minimized.

Copy link

tres-pitt commented Feb 21, 2018

Thanks Anthony. This has worked for me so far, but what about numeric columns?

From the official website, 'None is assumed to be a string of length 1 so any values that are later bound as numbers or dates will raise a TypeError exception'

This prevents the use of executemany...the iterative alternative works but is like 50-100x slower

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Feb 26, 2018

You should use cursor.setinputsizes() to tell cx_Oracle you intend to bind numeric data.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment