Corrupted number scales for certain exponents

[pkarol]

When inserting numbers between 1e-9 and 1e-7, the values stored in oracle do not have the correct scale:

Expected output:

[[ 0.                 1.               ]
 [ 1.                 0.1              ]
 [ 2.                 0.01             ]
 [ 3.                 0.001            ]
 [ 4.                 0.0001           ]
 [ 5.                 0.00001          ]
 [ 6.                 0.000001         ]
 [ 7.                 0.0000001        ]
 [ 8.                 0.00000001       ]
 [ 9.                 0.000000001      ]
 [10.                 0.0000000001     ]
 [11.                 0.00000000001    ]
 [12.                 0.000000000001   ]
 [13.                 0.0000000000001  ]
 [14.                 0.00000000000001 ]
 [15.                 0.000000000000001]]

Actual output from Oracle:

[[ 0.                 1.               ]
 [ 1.                 0.1              ]
 [ 2.                 0.01             ]
 [ 3.                 0.001            ]
 [ 4.                 0.0001           ]
 [ 5.                 0.00001          ]
 [ 6.                 0.000001         ]
 [ 7.                 0.0000001        ]
 [ 8.                 1.               ]  # ERROR: Should be 1e-8
 [ 9.                 1.               ]  # ERROR: Should be 1e-9
 [10.                 0.0000000001     ]
 [11.                 0.00000000001    ]
 [12.                 0.000000000001   ]
 [13.                 0.0000000000001  ]
 [14.                 0.00000000000001 ]
 [15.                 0.000000000000001]]

The following code replicates the problem:

import unittest

import cx_Oracle
import numpy as np
from numpy.testing import assert_allclose

class MyTestCase(unittest.TestCase):
    NUM_IDS = 16

    def setUp(self):
        config = {
            'username': '<USER>',
            'password': '<PASSWORD',
            'host': '<HOST>',
            'port': 1521,
            'service_name': '<SERVICE NAME>'
        }

        self._database_url = '{username}/{password}@{host}:{port}/{service_name}'.format(**config)

        create_tbl = 'CREATE TABLE TEST (ID NUMBER, VALUE NUMBER)'

        test_values = [np.power(10.0, - x) for x in range(self.NUM_IDS)]
        self.test_data = list(zip(range(self.NUM_IDS), test_values))

        with cx_Oracle.connect(self._database_url) as conn, conn.cursor() as cursor:
            cursor.execute(create_tbl)
            cursor.executemany('INSERT INTO TEST (ID, VALUE) VALUES (:0, :1)', self.test_data)
            conn.commit()

    def test_numbers(self):
        with cx_Oracle.connect(self._database_url) as conn, conn.cursor() as cursor:
            out = cursor.execute('select id, value from test order by id').fetchall()
            np.set_printoptions(precision=self.NUM_IDS, suppress=True)
            # INPUT
            print(np.array(self.test_data))
            # OUTPUT
            print(np.array(out))

            assert_allclose(out, self.test_data, rtol=0, atol=1e-14, verbose=True)

Environment:

• OS: Ubuntu 16.04.6 LTS
• Python 3.6.7 | packaged by conda-forge | (default, Feb 28 2019, 09:07:38) [GCC 7.3.0] on linux
• cx_Oracle version: 7.1.2
• oracle-instantclient version: 11.2.0.4.0
• Oracle Database version: 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

[anthony-tuininga]

I can replicate the issue which appears to occur on insert for some reason. I'll get back to you on the solution once I've discovered the source of the issue!

[anthony-tuininga]

I have just pushed changes to correct this issue. As you can see by the commit, this only happened when the exponent digits started with a zero due to the behaviour of strtol(). Thanks for pointing this out!

[anthony-tuininga]

This has been released with cx_Oracle 7.2.