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

regression in numeric precision behavior in 6.0 #68

Closed
zzzeek opened this Issue Aug 18, 2017 · 7 comments

Comments

Projects
None yet
2 participants
@zzzeek
Copy link

zzzeek commented Aug 18, 2017

Using cx_Oracle.NUMERIC with setinputsizes is now causing truncation under cx_Oracle 6.0.

import cx_Oracle
import decimal

conn = cx_Oracle.connect(
    user="scott",
    password="tiger",
    dsn=cx_Oracle.makedsn(
        "192.168.1.185", 1521, sid="xe",
    )
)

cursor = conn.cursor()


def output_type_handler(cursor, name, defaultType,
                        size, precision, scale):
    if defaultType == cx_Oracle.NUMBER:
        return cursor.var(
            cx_Oracle.STRING,
            255,
            outconverter=decimal.Decimal,
            arraysize=cursor.arraysize)

cursor.outputtypehandler = output_type_handler

cursor.execute("""
CREATE TABLE t (
    x NUMERIC(38, 12)
)
""")
try:
    value = decimal.Decimal("319438950232418390.273596")
    cursor.setinputsizes(x=cx_Oracle.NUMBER)
    cursor.execute(
        "INSERT INTO t (x) VALUES (:x)",
        x=value
    )

    cursor.execute("SELECT x FROM t")
    row = cursor.fetchone()
    received = row[0]

    print "cx_Oracle version: %r" % cx_Oracle.__version__
    print "Sent: %r   Received: %r" % (value, received)

    assert received == value, "%r != %r" % (received, value)


finally:
    cursor.execute("DROP TABLE t")


output under 5.2:

cx_Oracle version: '5.2'
Sent: Decimal('319438950232418390.273596')   Received: Decimal('319438950232418390.273596')

output under 6.0:

cx_Oracle version: '6.0'
Sent: Decimal('319438950232418390.273596')   Received: Decimal('319438950232418000')
Traceback (most recent call last):
  File "test.py", line 46, in <module>
    assert received == value, "%r != %r" % (received, value)
AssertionError: Decimal('319438950232418000') != Decimal('319438950232418390.273596')

if I remove the call to setinputsizes(), it works.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 18, 2017

Thanks for the test case. I can replicate this and will get back to you on the source of the issue.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 18, 2017

So if you call cursor.setinputsizes() to specify the use of cx_Oracle.NUMBER, the code silently converts decimal to float, which is what resulted in the truncation. I have added the type check back in again so that this will fail and the default bind will take place instead. Generally, unless you are binding null and need to specify the type, you're better off letting cx_Oracle determine the type from the input data. Note as well, that you could also use cursor.setinputsizes(decimal.Decimal) which would also have eliminated the issue.

@zzzeek

This comment has been minimized.

Copy link
Author

zzzeek commented Aug 18, 2017

still trying, after many years, to get a handle on exactly which types I should be calling setinputsizes(), and which I should not, in cx_Oracle. Right now the SQLAlchemy does it for all types except STRING. in https://bitbucket.org/zzzeek/sqlalchemy/issues/4035#comment-38706596, you seemed to be saying that if I dont use setinputsizes() for STRING, I'm going to get inconsistent results in Python 2 vs. Python 3, and I haven't even gotten to work on that as just setting STRING caused lots of test failures right off.

Then, using decimal.Decimal in setinputsizes(), will that work in cx_Oracle 5 also ?

Right now it seems to be that I should not be calling setinputsizes() in any cases except when a LOB type is being passed but I wonder if I was using it for Numeric due to behaviors in older versions of cx_Oracle like 5.x or even 4.x? Not really sure.

@zzzeek

This comment has been minimized.

Copy link
Author

zzzeek commented Aug 18, 2017

also, "binding null", sure, my users might be using executemany() where some of the rows have nulls. not sure how executemany is taken into account, is that on a per-parameter-set basis that it checks the types of things?

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 18, 2017

still trying, after many years, to get a handle on exactly which types I should be calling setinputsizes(), and which I should not, in cx_Oracle. Right now the SQLAlchemy does it for all types except STRING. in https://bitbucket.org/zzzeek/sqlalchemy/issues/4035#comment-38706596, you seemed to be saying that if I dont use setinputsizes() for STRING, I'm going to get inconsistent results in Python 2 vs. Python 3, and I haven't even gotten to work on that as just setting STRING caused lots of test failures right off.

I understand your pain. If I could go back in my time machine, I would have ensured that both string and unicode in Python 2.x bound to VARCHAR2 in the database. Unfortunately, we're stuck with the situation where if you bind a string you bind to VARCHAR2 but if you bind a unicode value (Python 2.x) you bind to NVARCHAR2.

In general, I recommend not using setinputsizes() unless the type that cx_Oracle would figure out by itself doesn't match the type you require in the database (such as when binding a null value). Since you're writing generic code for SQLAlchemy, however, that recommendation doesn't work as well for you. I would recommend using the Python types (str, int, float, decimal.Decimal, datetime.datetime) when calling setinputsizes() as the generic DB API types cover too much ground and the defaults may not work for you, especially for things like numbers (cx_Oracle.NUMBER effectively means floating point numbers).

Then, using decimal.Decimal in setinputsizes(), will that work in cx_Oracle 5 also?

Yes, that works in cx_Oracle 5, too.

also, "binding null", sure, my users might be using executemany() where some of the rows have nulls. not sure how executemany is taken into account, is that on a per-parameter-set basis that it checks the types of things?

The method executemany() will use the first non-null value to determine the type for each bind variable.

Hope that answers your questions! Feel free to ask further if you need further input.

@zzzeek

This comment has been minimized.

Copy link
Author

zzzeek commented Aug 22, 2017

this fix pending for 6.0.2 ?

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 22, 2017

Yes. That will likely take place next week, unless something critical comes up before then, of course!

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