From b1ca92625dadce587946df157fac5456b7cf1592 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 18 Jan 2019 21:30:21 -0500 Subject: [PATCH] Don't use cx_Oracle.NATIVE_INT in output type handlers Fixed regression in integer precision logic due to the refactor of the cx_Oracle dialect in 1.2. We now no longer apply the cx_Oracle.NATIVE_INT type to result columns sending integer values (detected as positive precision with scale ==0) which encounters integer overflow issues with values that go beyond the 32 bit boundary. Instead, the output variable is left untyped so that cx_Oracle can choose the best option. Fixes: #4457 Change-Id: I1e3114c2f37bf028fb1f521a3e9789a77e3a7491 (cherry picked from commit 5832f7172907a8151345d95061f93784ce4bb9b1) --- doc/build/changelog/unreleased_12/4457.rst | 10 +++++ lib/sqlalchemy/dialects/oracle/cx_oracle.py | 23 +++++----- test/dialect/oracle/test_types.py | 47 +++++++++++++++++++++ 3 files changed, 67 insertions(+), 13 deletions(-) create mode 100644 doc/build/changelog/unreleased_12/4457.rst diff --git a/doc/build/changelog/unreleased_12/4457.rst b/doc/build/changelog/unreleased_12/4457.rst new file mode 100644 index 0000000000..902dc7430b --- /dev/null +++ b/doc/build/changelog/unreleased_12/4457.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, oracle + :tickets: 4457 + + Fixed regression in integer precision logic due to the refactor of the + cx_Oracle dialect in 1.2. We now no longer apply the cx_Oracle.NATIVE_INT + type to result columns sending integer values (detected as positive + precision with scale ==0) which encounters integer overflow issues with + values that go beyond the 32 bit boundary. Instead, the output variable + is left untyped so that cx_Oracle can choose the best option. diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 09d2f28f58..fb2ebc84ad 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -366,10 +366,10 @@ class _OracleNumeric(sqltypes.Numeric): cx_Oracle = dialect.dbapi is_cx_oracle_6 = dialect._is_cx_oracle_6 - has_native_int = dialect._has_native_int def handler(cursor, name, default_type, size, precision, scale): outconverter = None + if precision: if self.asdecimal: if default_type == cx_Oracle.NATIVE_FLOAT: @@ -384,13 +384,13 @@ class _OracleNumeric(sqltypes.Numeric): outconverter = dialect._to_decimal else: if self.is_number and scale == 0: - if has_native_int: - type_ = cx_Oracle.NATIVE_INT - else: - type_ = cx_Oracle.NUMBER - outconverter = int + # integer. cx_Oracle is observed to handle the widest + # variety of ints when no directives are passed, + # from 5.2 to 7.0. See [ticket:4457] + return None else: type_ = cx_Oracle.NATIVE_FLOAT + else: if self.asdecimal: if default_type == cx_Oracle.NATIVE_FLOAT: @@ -403,11 +403,10 @@ class _OracleNumeric(sqltypes.Numeric): outconverter = dialect._to_decimal else: if self.is_number and scale == 0: - if has_native_int: - type_ = cx_Oracle.NATIVE_INT - else: - type_ = cx_Oracle.NUMBER - outconverter = int + # integer. cx_Oracle is observed to handle the widest + # variety of ints when no directives are passed, + # from 5.2 to 7.0. See [ticket:4457] + return None else: type_ = cx_Oracle.NATIVE_FLOAT @@ -776,8 +775,6 @@ class OracleDialect_cx_oracle(OracleDialect): "cx_Oracle version 5.2 and above are supported" ) - self._has_native_int = hasattr(cx_Oracle, "NATIVE_INT") - self._include_setinputsizes = { cx_Oracle.NCLOB, cx_Oracle.CLOB, diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py index e65fb83340..721e3be209 100644 --- a/test/dialect/oracle/test_types.py +++ b/test/dialect/oracle/test_types.py @@ -643,6 +643,53 @@ class TypesTest(fixtures.TestBase): value = testing.db.scalar("SELECT 5.66 FROM DUAL") assert isinstance(value, decimal.Decimal) + @testing.only_on("oracle+cx_oracle", "cx_oracle-specific feature") + def test_raw_numerics(self): + 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", + ), + ] + + with testing.db.connect() as conn: + for title, stmt in query_cases: + # get a brand new connection that definitely is not + # in the pool to avoid any outputtypehandlers + cx_oracle_raw = testing.db.pool._creator() + cursor = cx_oracle_raw.cursor() + cursor.execute(stmt) + cx_oracle_result = cursor.fetchone()[0] + cursor.close() + + sqla_result = conn.scalar(stmt) + + print( + "%s cx_oracle=%s sqlalchemy=%s" + % (title, cx_oracle_result, sqla_result) + ) + eq_(sqla_result, cx_oracle_result) + @testing.only_on("oracle+cx_oracle", "cx_oracle-specific feature") @testing.fails_if( testing.requires.python3, "cx_oracle always returns unicode on py3k" -- 2.47.2