Oracle wrong numbers when different NLS_NUMBERIC_CHARACTERS

[stenet]

Hello,

while testing Polars in combination with arrow-odbc I encountered a problem. I'm not sure if this is a bug or a lack of configuration on my part.

When selecting number values with commas, they are returned incorrectly if NLS_NUMERIC_CHARACTERS = ",." instead of ".," in the database.

Example:

• Column has the data type NUMBER(19,5)
• Number in database = 10
• Return value = 0.00010

Any idea how I could solve the problem?

[pacman82]

Hello @stenet ,

thanks for reporting the issue and sorry to see the arrow-odbc just silently parsed the wrong value. I would have yet to look up what the ODBC standard says precisely about this. Yet arrow-odbc works under the assumption that any DECIMAL or NUMERIC typed value has a text representation using a decimal point (.), no separation for thousands and all trailing zeroes being explicit. In your example it should look like this: 10.00000.

More likely with your settings ten would render as 10,00000. Which would, because of the "wrong" decimal point currently be interpreted as 0.00010.

Now at the very least arrow-odbc should raise an error if an unexpected string representation is discovered, so it would not just write a wrong value silently. In terms of actually getting it to work I would recommend if at all possible to set the decimal point to an actual point, and not use a Seperator for the thousands. Types do have fixed string representations in ODBC and these settings mess with them.

arrow-odbc does not know much, if anything about Oracle databases. Therefore it does not know what Oracle specific settings you are using. ODBC to my knowledge has no interface to talk about locale settings, but it defines how certain types look, both in memory, but also in text. Changing this messes with the interface. Again at the very least arrow-odbc should verify that the decimal strings look like they are expected to and throw an error if not.

I would like to understand your usecase better, why is NLS_NUMERIC_CHARACTERS set to ",." in the first place?

Best, Markus

[stenet]

HI @pacman82

thank you very much for the detailed explanation.

I cannot change NLS_NUMERIC_CHARACTERS at database level as I am only a consumer of the database. I assume this was set this way due to a legacy application.

I already tried using the NUM=US keyword in the connection-string but that didn't help either.

Do you think it would be possible to implement something like pyodbc https://github.com/mkleehammer/pyodbc/blob/bc5b7c6f50f71aa776f881fafce95328338ed35e/src/decimal.cpp#L69? I don't know C++ but it looks like they do an initial scanning for the decimal separators, save that and reuse it.

Thank you again.
Regards, Stefan

[pacman82]

Hm, I think I might be able to just strip everything that is not a digit and interpret the remaining number. E.g.

10,00000 -> 1000000 -> (scale 5) -> 10.00000

[stenet]

Wow, that was fast. You fixed it while I was making dinner ;)
Thank you!

[pacman82]

You are welcome

[stenet]

Hi @pacman82,

I ran some tests with the new version and I'm sorry, but it looks like something is still wrong. I ran the same test with versions 2.1.1 and 2.1.3 but had exactly the same result.

If the data type in Oracle is float, the result is correct. If it is number or decimal (which should be synonyms), the problem with the wrong decimal points still exists. Also, these values have the data type "object" instead of "float64" in the Pandas data frame.

Do you have any idea what could be doing wrong here?
If I can help you with some more detailed tests, let me know.

Best regards, Stefan

[stenet]

This is the result of to_pydict():

[pacman82]

I have a suspicion. This is a different error though. Almost seems like Oracle would not add the trailing zeroes to the decimal text representation. You can always help by enabling logging and sharing the output: https://arrow-odbc.readthedocs.io/en/latest/arrow_odbc.html#arrow_odbc.log_to_stderr

[stenet]

This is the log for my sample from above using log level 4:

DEBUG - ODBC Environment created.
DEBUG - SQLAllocHandle allocated connection (Dbc) handle '0x1fef3a1bd60'
WARN - State: 01000, Native error: 0, Message: [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
WARN - State: 01004, Native error: 0, Message: [Oracle][ODBC]String data, right truncated. doesn't support the version of ODBC behavi
DEBUG - Database managment system name as reported by ODBC: Oracle
DEBUG - ODBC driver reported for column 0. Relational type: Float { precision: 38 }; Nullability: Nullable; Name: 'V';
DEBUG - ODBC driver reported for column 1. Relational type: Decimal { precision: 15, scale: 5 }; Nullability: Nullable; Name: 'V_NUMBER';
DEBUG - ODBC driver reported for column 2. Relational type: Decimal { precision: 38, scale: 0 }; Nullability: Nullable; Name: 'V_DECIMAL';
DEBUG - ODBC driver reported for column 3. Relational type: Decimal { precision: 15, scale: 5 }; Nullability: Nullable; Name: 'V_DECIMAL_2';
DEBUG - ODBC driver reported for column 4. Relational type: Float { precision: 38 }; Nullability: Nullable; Name: 'V_FLOAT';
INFO - Column 'V'
Bytes used per row: 16
INFO - Column 'V_NUMBER'
Bytes used per row: 26
INFO - Column 'V_DECIMAL'
Bytes used per row: 49
INFO - Column 'V_DECIMAL_2'
Bytes used per row: 26
INFO - Column 'V_FLOAT'
Bytes used per row: 16
INFO - Total memory usage per row for single transit buffer: 133
             V     V_NUMBER V_DECIMAL  V_DECIMAL_2      V_FLOAT
0       1.1123      0.11123         1      0.11123       1.1123
1  123456.1123  12345.61123    123456  12345.61123  123456.1123
DEBUG - SQLFreeHandle dropped 0x1fef3a1dbd0 of type Stmt.
DEBUG - SQLFreeHandle dropped 0x1fef3a1bd60 of type Dbc.

[pacman82]

Thank you, there is one more piece of information I am missing. And this is what is the string representation returned by the database. Could you kindly execute a query which fetches some decimals, but fetch them as text using a manually provided schema using the aptly named schema keyword argument?

Showing me the contents of this array would tell me the string representation for decimals used by Oracle.

Best, Markus

[pacman82]

oh, wait. I think you printed it. In this case now I just need time.

[stenet]

Perfect. If I can help you with anything, let me know!

[pacman82]

These were two issues. One was the localized radix character. The other one is Oracle not adding the trailing zeroes. Decimal parsing should now be robust against both. arrow-odbc 2.1.4 is released.

[stenet]

Hi @pacman82

many thanks for your quick help.

Now the results for the previous code looks good:

V_DECIMAL is without decimal places, as the DBType has no decimal places.

[pacman82]

You are welcome