Pyodbc cuting out trailing 0 from decimal(11,0)

[Tetlanesh]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.6.5 64bit
• pyodbc: 4.0.30
• OS: Windows10 64
• DB: HIVE Server2 1.2.1000.2.6.5.0-292
• driver: Hortonworks HIVE ODBC Driver 2.06.01.1001

Issue

I'm querying HIVE database. Table I'm querying contain field that is in Decimal(11,0) format.

If the value of that field ends with 0 - for example 1111111110 the result from pyodbc execute will be 111111111 with last zero truncated.

When using the same connector from other tools - like Power BI - I get correct result of 1111111110 (screenshot attached).

Below is the code to create table with decimal(11,0) column and populate it with example data and result of hive query on it and result I get when using pyodbc which have 0's ommited at the end (except when value = 0)

edit: ORDER BY DECIMAL11 clause in below queries is only for better visibility of results. Exactly the same result is without it.

HIVE create table with data code:

CREATE TABLE SB_CICORP.TEST_TABLE_DECIMAL AS SELECT cast(1111111110 as decimal(11,0)) as decimal11;
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(0);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(10);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(111110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1111110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11111110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(111111110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11111111110);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(111111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1111111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11111111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(111111111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(1111111111);
INSERT INTO SB_CICORP.TEST_TABLE_DECIMAL VALUES(11111111111);

SELECT * FROM  SB_CICORP.TEST_TABLE_DECIMAL ORDER BY DECIMAL11;
+-------------------------------+--+
| test_table_decimal.decimal11  |
+-------------------------------+--+
| 0                             |
| 1                             |
| 10                            |
| 11                            |
| 110                           |
| 111                           |
| 1110                          |
| 1111                          |
| 11110                         |
| 11111                         |
| 111110                        |
| 111111                        |
| 1111110                       |
| 1111111                       |
| 11111110                      |
| 11111111                      |
| 111111110                     |
| 111111111                     |
| 1111111110                    |
| 1111111111                    |
| 11111111110                   |
| 11111111111                   |
+-------------------------------+--+

DESCRIBE SB_CICORP.TEST_TABLE_DECIMAL;
+------------+----------------+----------+--+
|  col_name  |   data_type    | comment  |
+------------+----------------+----------+--+
| decimal11  | decimal(11,0)  |          |
+------------+----------------+----------+--+

Python pyodbc query code and results:

import pyodbc

conn = pyodbc.connect(f'DSN=HIVE PROD OWS;UID={MY_USER};PWD={MY_PASSWORD}',autocommit=True)

sql = '''
    select * from sb_cicorp.test_table_decimal order by decimal11
'''

curr = conn.execute(sql)
curr.fetchall()
[(Decimal('0'), ),
 (Decimal('1'), ),
 (Decimal('1'), ),
 (Decimal('11'), ),
 (Decimal('11'), ),
 (Decimal('111'), ),
 (Decimal('111'), ),
 (Decimal('1111'), ),
 (Decimal('1111'), ),
 (Decimal('11111'), ),
 (Decimal('11111'), ),
 (Decimal('111111'), ),
 (Decimal('111111'), ),
 (Decimal('1111111'), ),
 (Decimal('1111111'), ),
 (Decimal('11111111'), ),
 (Decimal('11111111'), ),
 (Decimal('111111111'), ),
 (Decimal('111111111'), ),
 (Decimal('1111111111'), ),
 (Decimal('1111111111'), ),
 (Decimal('11111111111'), )]

Attached is the screenshot of results from the same query, using the same connector but with PowerBI Desktop.

ODBC LOGS both when using PYODBC and when using POWERBI on the same query and using the same ODBC driver

SQL_PYODBC.LOG SQL_POWERBI.LOG

[gordthompson]

The SQLDescribeCol/SQLDescribeColW calls from pyodbc seem to be returning the correct values. Interesting that PowerBI does not use those functions, but makes a bunch of SQLColAttributeW calls instead. When it comes to SQLGetData, both approaches get the same result:

kernel-ff7eb7b7 2a6c-3e40   EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HSTMT               0x0000020140C31800
        UWORD                        1 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x000002013DC239C0 [       4] "10"
        SQLLEN                  4096
        SQLLEN *            0x000000AAD3BEA5D0 (4)

        DIAG [01000] [Hortonworks][Hardy] (50) Decimal value 10 right truncated because there are more digits to the right of the decimal point than the scale of the column. (50)

[alifahsanul]

Hi @Tetlanesh I also face this issue and can't find any solutions Do you have any suggestion?

[Tetlanesh]

@alifahsanul I still have this bug unfortunetly. Havent found any workaround.

[alifahsanul]

@Tetlanesh thanks for the reply. For the workaround I am using pypyodbc https://github.com/jiangwen365/pypyodbc I finally got the correct data

[Tetlanesh]

@alifahsanul thx for the tip. I'll look into pypyodbc.