cx_Oracle Integer Overflow/Underflow with SQLAlchemy 1.2.x

[mpeleshenko]

I'm seeing integer overflow/underflow for NUMBER types in SQLAlchemy 1.2.x, but not in 1.1.18 with cx_Oracle 7.0.0.

Test Script

import cx_Oracle
import sqlalchemy
import sys

oracle_dialect = 'oracle'
oracle_driver = 'cx_oracle'
oracle_username = ''
oracle_password = ''
oracle_host = ''
oracle_port = 0
oracle_database = ''
oracle_dsn = '{host}:{port}/{database}'.format(host=oracle_host, port=oracle_port, database=oracle_database)

oracle_url = '{dialect}+{driver}://{username}:{password}@{dsn}'.format(
    dialect=oracle_dialect,
    driver=oracle_driver,
    username=oracle_username,
    password=oracle_password,
    dsn=oracle_dsn,
)

query_cases = [
    ('Max 32-bit Number', 'SELECT CAST(2147483647 AS NUMBER(19,0)) FROM dual'),
    ('Min 32-bit Number', 'SELECT CAST(-2147483648 AS NUMBER(19,0)) FROM dual'),
    ('32-bit Integer Overflow', 'SELECT CAST(2147483648 AS NUMBER(19,0)) FROM dual'),
    ('32-bit Integer Underflow', 'SELECT CAST(-2147483649 AS NUMBER(19,0)) FROM dual'),
    ('Max Number with Precision 19', 'SELECT CAST(9999999999999999999 AS NUMBER(19,0)) FROM dual'),
    ('Min Number with Precision 19', 'SELECT CAST(-9999999999999999999 AS NUMBER(19,0)) FROM dual'),
]

cx_oracle_connection = cx_Oracle.connect(
    oracle_username,
    oracle_password,
    oracle_dsn,
)
sqlalchemy_engine = sqlalchemy.create_engine(oracle_url)

print('Python Version:', sys.version)
print('SQLAlchemy Version:', sqlalchemy.__version__)
print('cx_Oracle Version:', cx_Oracle.__version__)

with cx_oracle_connection.cursor() as cursor:
    oracle_server_version = cursor.execute('SELECT banner FROM v$version').fetchall()
    print('Oracle Server Version:')
    for v in oracle_server_version:
        print('\t{}'.format(v[0]))

print('Oracle Client Version:', '.'.join(str(x) for x in cx_Oracle.clientversion()))

print('-' * 50)
print('Test Cases:')
for query_case, query in query_cases:
    cx_oracle_msg = '{} (cx_Oracle): '.format(query_case)
    with cx_oracle_connection.cursor() as cursor:
        try:
            cx_oracle_result = cursor.execute(query).fetchall()
        except Exception as e:
            cx_oracle_result = e
        else:
            cx_oracle_result = cx_oracle_result[0][0]
    print(cx_oracle_msg + str(cx_oracle_result))

    sqlalchemy_msg = '{} (SQLAlchemy): '.format(query_case)
    try:
        sqlalchemy_result = sqlalchemy_engine.execute(query).fetchall()
    except Exception as e:
        sqlalchemy_result = e
    else:
        sqlalchemy_result = sqlalchemy_result[0][0]
    print(sqlalchemy_msg + str(sqlalchemy_result))

    if cx_oracle_result != sqlalchemy_result:
        print('##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR {}'.format(query_case))

Script Output with SQLAlchemy 1.1.18

Python Version: 3.5.6 |Anaconda, Inc.| (default, Aug 26 2018, 16:05:27) [MSC v.1900 64 bit (AMD64)]
SQLAlchemy Version: 1.1.18
cx_Oracle Version: 7.0.0
Oracle Server Version:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	PL/SQL Release 12.1.0.2.0 - Production
	CORE	12.1.0.2.0	Production
	TNS for Linux: Version 12.1.0.2.0 - Production
	NLSRTL Version 12.1.0.2.0 - Production
Oracle Client Version: 12.1.0.2.0
--------------------------------------------------
Test Cases:
Max 32-bit Number (cx_Oracle): 2147483647
Max 32-bit Number (SQLAlchemy): 2147483647
Min 32-bit Number (cx_Oracle): -2147483648
Min 32-bit Number (SQLAlchemy): -2147483648
32-bit Integer Overflow (cx_Oracle): 2147483648
32-bit Integer Overflow (SQLAlchemy): 2147483648
32-bit Integer Underflow (cx_Oracle): -2147483649
32-bit Integer Underflow (SQLAlchemy): -2147483649
Max Number with Precision 19 (cx_Oracle): 9999999999999999999
Max Number with Precision 19 (SQLAlchemy): 9999999999999999999
Min Number with Precision 19 (cx_Oracle): -9999999999999999999
Min Number with Precision 19 (SQLAlchemy): -9999999999999999999

Script Output with SQLAlchemy 1.2.16

Python Version: 3.5.6 |Anaconda, Inc.| (default, Aug 26 2018, 16:05:27) [MSC v.1900 64 bit (AMD64)]
SQLAlchemy Version: 1.2.16
cx_Oracle Version: 7.0.0
Oracle Server Version:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	PL/SQL Release 12.1.0.2.0 - Production
	CORE	12.1.0.2.0	Production
	TNS for Linux: Version 12.1.0.2.0 - Production
	NLSRTL Version 12.1.0.2.0 - Production
Oracle Client Version: 12.1.0.2.0
--------------------------------------------------
Test Cases:
Max 32-bit Number (cx_Oracle): 2147483647
Max 32-bit Number (SQLAlchemy): 2147483647
Min 32-bit Number (cx_Oracle): -2147483648
Min 32-bit Number (SQLAlchemy): -2147483648
32-bit Integer Overflow (cx_Oracle): 2147483648
32-bit Integer Overflow (SQLAlchemy): -2147483648
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR 32-bit Integer Overflow
32-bit Integer Underflow (cx_Oracle): -2147483649
32-bit Integer Underflow (SQLAlchemy): 2147483647
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR 32-bit Integer Underflow
Max Number with Precision 19 (cx_Oracle): 9999999999999999999
Max Number with Precision 19 (SQLAlchemy): (cx_Oracle.DatabaseError) ORA-01455: converting column overflows integer datatype (Background on this error at: http://sqlalche.me/e/4xp6)
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR Max Number with Precision 19
Min Number with Precision 19 (cx_Oracle): -9999999999999999999
Min Number with Precision 19 (SQLAlchemy): (cx_Oracle.DatabaseError) ORA-01455: converting column overflows integer datatype (Background on this error at: http://sqlalche.me/e/4xp6)
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR Min Number with Precision 19

[zzzeek]

here's an overview of what changed for cx_Oracle:

https://docs.sqlalchemy.org/en/rel_1_2/changelog/migration_12.html#major-refactor-to-cx-oracle-dialect-typing-system

the code at work here is likely at https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L900

can you attempt to set coerce_to_decimal=False on create_engine? see https://docs.sqlalchemy.org/en/rel_1_2/dialects/oracle.html#cx-oracle-numeric for details on this feature.

[zzzeek]

so ill have to report upstream issues with cx_oracle which is waht usually happens here. When the type is sent with precision 19 and scale 0, that means it's an integer, so SQLAlchemy uses cx_Oracle's API to treat this as cx_Oracle.NATIVE_INT. that's wahts failing for starters.

[zzzeek]

im also not getting that result for 32 bit integer underflow, it matches for me...

[zzzeek]

welp I'll just stick "int" in there for now, seems to work fine. but I can only reproduce the error you were getting, not the part where the integer underflow doesnt match. The patch coming in should likely improve things at least.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Don't use cx_Oracle.NATIVE_INT in output type handlers https://gerrit.sqlalchemy.org/1107

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_1_2 branch:

Don't use cx_Oracle.NATIVE_INT in output type handlers https://gerrit.sqlalchemy.org/1108

[zzzeek]

just to sum up, im on oracle 11 XE, am not getting the integer underflow mismatch, just the error message:

Python Version: 3.7.0 (default, Jun 28 2018, 10:59:55) 
[GCC 8.1.1 20180502 (Red Hat 8.1.1-1)]
SQLAlchemy Version: 1.2.16
cx_Oracle Version: 7.0.0
Oracle Server Version:
	Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
	PL/SQL Release 11.2.0.2.0 - Production
	CORE	11.2.0.2.0	Production
	TNS for Linux: Version 11.2.0.2.0 - Production
	NLSRTL Version 11.2.0.2.0 - Production
Oracle Client Version: 11.2.0.2.0
--------------------------------------------------
Test Cases:
Max 32-bit Number (cx_Oracle): 2147483647
Max 32-bit Number (SQLAlchemy): 2147483647
Min 32-bit Number (cx_Oracle): -2147483648
Min 32-bit Number (SQLAlchemy): -2147483648
32-bit Integer Overflow (cx_Oracle): 2147483648
32-bit Integer Overflow (SQLAlchemy): 2147483648
32-bit Integer Underflow (cx_Oracle): -2147483649
32-bit Integer Underflow (SQLAlchemy): -2147483649
Max Number with Precision 19 (cx_Oracle): 9999999999999999999
Max Number with Precision 19 (SQLAlchemy): (cx_Oracle.DatabaseError) ORA-01455: converting column overflows integer datatype (Background on this error at: http://sqlalche.me/e/4xp6)
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR Max Number with Precision 19
Min Number with Precision 19 (cx_Oracle): -9999999999999999999
Min Number with Precision 19 (SQLAlchemy): (cx_Oracle.DatabaseError) ORA-01455: converting column overflows integer datatype (Background on this error at: http://sqlalche.me/e/4xp6)
##### SQLALCHEMY DOES NOT MATCH CX_ORACLE FOR Min Number with Precision 19

with the patch that you are encouraged to test out at https://gerrit.sqlalchemy.org/1108, I get no errors:

Python Version: 3.7.0 (default, Jun 28 2018, 10:59:55) 
[GCC 8.1.1 20180502 (Red Hat 8.1.1-1)]
SQLAlchemy Version: 1.2.17
cx_Oracle Version: 7.0.0
Oracle Server Version:
	Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
	PL/SQL Release 11.2.0.2.0 - Production
	CORE	11.2.0.2.0	Production
	TNS for Linux: Version 11.2.0.2.0 - Production
	NLSRTL Version 11.2.0.2.0 - Production
Oracle Client Version: 11.2.0.2.0
--------------------------------------------------
Test Cases:
Max 32-bit Number (cx_Oracle): 2147483647
Max 32-bit Number (SQLAlchemy): 2147483647
Min 32-bit Number (cx_Oracle): -2147483648
Min 32-bit Number (SQLAlchemy): -2147483648
32-bit Integer Overflow (cx_Oracle): 2147483648
32-bit Integer Overflow (SQLAlchemy): 2147483648
32-bit Integer Underflow (cx_Oracle): -2147483649
32-bit Integer Underflow (SQLAlchemy): -2147483649
Max Number with Precision 19 (cx_Oracle): 9999999999999999999
Max Number with Precision 19 (SQLAlchemy): 9999999999999999999
Min Number with Precision 19 (cx_Oracle): -9999999999999999999
Min Number with Precision 19 (SQLAlchemy): -9999999999999999999

so when those patches go in that will resolve this unless you can identify more mismatches.

[mpeleshenko]

Hey @zzzeek , thanks for the quick investigation! The patch indeed passes all my test cases now. When can I expect this to be merged into a release version?

Python Version: 3.5.6 |Anaconda, Inc.| (default, Aug 26 2018, 16:05:27) [MSC v.1900 64 bit (AMD64)]
SQLAlchemy Version: 1.2.16
cx_Oracle Version: 7.0.0
Oracle Server Version:
	Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
	PL/SQL Release 12.1.0.2.0 - Production
	CORE	12.1.0.2.0	Production
	TNS for Linux: Version 12.1.0.2.0 - Production
	NLSRTL Version 12.1.0.2.0 - Production
Oracle Client Version: 12.1.0.2.0
--------------------------------------------------
Test Cases:
Max 32-bit Number (cx_Oracle): 2147483647
Max 32-bit Number (SQLAlchemy): 2147483647
Min 32-bit Number (cx_Oracle): -2147483648
Min 32-bit Number (SQLAlchemy): -2147483648
32-bit Integer Overflow (cx_Oracle): 2147483648
32-bit Integer Overflow (SQLAlchemy): 2147483648
32-bit Integer Underflow (cx_Oracle): -2147483649
32-bit Integer Underflow (SQLAlchemy): -2147483649
Max Number with Precision 19 (cx_Oracle): 9999999999999999999
Max Number with Precision 19 (SQLAlchemy): 9999999999999999999
Min Number with Precision 19 (cx_Oracle): -9999999999999999999
Min Number with Precision 19 (SQLAlchemy): -9999999999999999999

[zzzeek]

releases are usually around a month apart unless something is really urgent. 1.2.17 has one other bugfix pending so next week is possible if you are in a hurry.

[mpeleshenko]

Next week would be great! We have been holding off upgrading to SQLAlchemy 1.2.x because of this bug for a while now. It was great that you made the move to Github issues, as it is much more accessible.