Query with a big number returns wrong result using cx_Oracle

[sqlalchemy-bot]

Migrated issue, originally created by EmilijusS

Environment :##

1. Python 2.7.11
2. cx_Oracle 5.2.1 works fine, cx_Oracle 6.4.1 (and other 6.x.x versions) don't
3. Oracle client version 12.1.0.2.0
4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
5. Linux CentOS 6.10
6. GCC 6.3.0
7. SQLAlchemy 1.2.10

Example code:##

from sqlalchemy     import create_engine, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

def main():
    engine = create_engine("oracle://CONNECTIONSTRING")
    Base.metadata.create_all( engine )
    ss = sessionmaker( bind=engine )
    s = ss()

    engine.execute("INSERT INTO Test (number) VALUES (1376537018368127)")
    engine.execute("INSERT INTO Test (number) VALUES (1376537018368130)")
    
    s.close()
    s = ss()

    result = s.query(Test).filter(Test.number == 1376537018368127).first()

    print result.number

    s.close()

class Test( Base ):

    __tablename__  = 'Test'

    number = Column( Integer,       primary_key=True )


main()

With cx_Oracle 5.2.1 prints:

1376537018368127

With cx_Oracle 6.4.1 prints:

1376537018368130

##Additional information:##
Sorry if the code doesn't work, I don't really have a way to test it as I can only access this one production Oracle database in which the bug was spotted.

Probably related post in stackoverflow

[sqlalchemy-bot]

Changes by EmilijusS:

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

workaround:

    import cx_Oracle

    @event.listens_for(engine, "do_setinputsizes")
    def _set_ints(inputsizes, cursor, statement, parameters, context):
        for k in inputsizes:
            if isinstance(k.type, Integer):
                inputsizes[k] = cx_Oracle.NATIVE_INT

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/#/q/I4c6b2cca490aff5b98b7ceff3414715202881c89

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

see also oracle/python-cx_Oracle#208 which is the latest in a very long string of setinputsizes-based regressions which have occurred in cx_Oracle (none were documented).

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Bind Integers to int for cx_Oracle

For cx_Oracle, Integer datatypes will now be bound to "int", per advice
from the cx_Oracle developers. Previously, using cx_Oracle.NUMBER caused a
loss in precision within the cx_Oracle 6.x series.

Change-Id: I4c6b2cca490aff5b98b7ceff3414715202881c89
Fixes: #4309

→ 75d48e6

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Bind Integers to int for cx_Oracle

For cx_Oracle, Integer datatypes will now be bound to "int", per advice
from the cx_Oracle developers. Previously, using cx_Oracle.NUMBER caused a
loss in precision within the cx_Oracle 6.x series.

Change-Id: I4c6b2cca490aff5b98b7ceff3414715202881c89
Fixes: #4309
(cherry picked from commit 75d48e6)

→ 108d331