Fetching NULL in DATE column gives ValueError: year 0 is out of range

[paul-lilley]

Environment - where the ValueError is given

• Python: Python 3.6.5
• pyodbc: 4.0.25b14 (compiled against unixODBC 2.3.7)
• OS: Red Hat Enterprise Linux Server release 7.5 (Maipo) 3.10.0-862.11.6.el7.x86_64
• DB: DB2 v10.5.0.7
• driver: unixODBC 2.3.7

Issue

When fetching NULL from a nullable DATE column (or a DATE variable set to NULL) pyodbc raises
ValueError: year 0 is out of range
I'd expect it to return None (which it in fact does when run in Anaconda on Windows against the same DB2). I've not tested against other databases.

Code to recreate the issue:

import pyodbc

if __name__ == '__main__':
    expected = None
    create_var = "CREATE VARIABLE null_date DATE DEFAULT NULL;"
    sql_select = "SELECT null_date FROM sysibm.systables fetch first 1 row only;"
    drop_var = "DROP VARIABLE null_date;"

    con = pyodbc.connect("DSN=XXXXXX;UID=XXXXXX;PWD=XXXXXX;")

    cursor = con.cursor()
    cursor.execute(create_var)
    try:
        row = cursor.execute(sql_select).fetchone()
        result = row[0]
        if result != expected:
            print(f'Null in a DATE column returned {result} instead of the expected {expected}')
        else:
            print('OK')
    finally:
        cursor.execute(drop_var)

[paul-lilley]

unixODBC log attached.
sql_pyodbc_linux.log

ODBC log from windows (where the issue does not appear with pyodbc 4.0.24 installed from pypi)
SQL.LOG

[paul-lilley]

I can workaround with an output converter function:

def null_date_converter(value):
    val_str = value.decode('utf-8')  # ? is it really utf-8
    #print(f'{__name__} value: {val_str}')
    if value is None:
        return None
    else:
        return datetime.datetime.strptime(val_str, "%Y-%m-%d").date()
con.add_output_converter(pyodbc.SQL_TYPE_DATE, null_date_converter)

Although the workaround behaves OK on both platforms it is not an ideal solution.

[paul-lilley]

Adding a TRACE

if (sizeof(cbFetched) == sizeof(long))
            TRACE("GetDataTimestamp: cbFetched %ld != %d SQL_NULL_DATA\n", cbFetched, SQL_NULL_DATA);
        else
            TRACE("GetDataTimestamp: cbFetched %d != %d SQL_NULL_DATA\n", cbFetched, SQL_NULL_DATA);

gives

GetDataTimestamp: cbFetched=4294967295 SQL_NULL_DATA=-1

4294967295 is 2^32 -1 so it's looking like an unsigned long is coming in somewhere.

Maybe a problem in unixODBC (or my compilation of it) rather than pyodbc?
However, the output converter correctly detects the NULL so my suspicions are still in pyodbc

[paul-lilley]

Compiler warnings that I failed to notice last night:

src/connection.cpp: In function ‘PyObject* Connection_getinfo(PyObject*, PyObject*)’:
src/connection.cpp:835:40: warning: dereferencing type-punned pointer will break strict-aliasing rules [-Wstrict-aliasing]
         SQLUINTEGER n = *(SQLUINTEGER*)szBuffer; // Does this work on PPC or do we need a union?
                                        ^
src/connection.cpp:848:49: warning: dereferencing type-punned pointer will break strict-aliasing rules [-Wstrict-aliasing]
         result = PyInt_FromLong(*(SQLUSMALLINT*)szBuffer);

Though I don't think these are relevent to this problem

[paul-lilley]

The NULL detection for output converters seems to work, GetDataUser calls ReadVarColumn which uses this:

if (ret == SQL_SUCCESS && (int)cbData < 0)
{
    // HACK: FreeTDS 0.91 on OS/X returns -4 for NULL data instead of SQL_NULL_DATA
    // (-1).  I've traced into the code and it appears to be the result of assigning -1
    // to a SQLLEN.  We are going to treat all negative values as NULL.
    ret = SQL_NULL_DATA;
    cbData = 0;
}

rather than a direct comparison of cbData to SQL_NULL_DATA - note also the explicit cast (int)cbData.
I confess that I don't fully understand why the explicit cast to int is needed.

I'm tempted to suggest replacement of the current NULL test in GetDataTimestamp (and presumably any other places where we check for NULL from SQLGetData) with that used in ReadVarColumn, i.e.
replace

if (cbFetched == SQL_NULL_DATA)

with

if ((int)cbFetched < 0)  // HACK: FreeTDS 0.91 on OS/X returns -4 for NULL data instead of SQL_NULL_DATA (-1)

Does this sound reasonable? If so I'm happy to try putting together a pull-request.
I'd also appreciate any deeper insight into what is really going on here, explicitly casting to int seems to fix the issue but feels a bit hackish.

[v-chojas]

I believe this could be a bug in your ODBC driver.
4294967295 is 32 1-bits, same as -1 when in a 32-bit variable, but on 64-bit systems the SQLLEN should be 64 bits; your ODBC driver appears to be assuming that SQLLEN is 32 bits instead, so for SQL_NULL_DATA it returns a 32-bit -1, which appears as 4294967295 to the DM and pyODBC which use 64-bit SQLLEN.

Just to be sure, you can run odbcinst -j and see the sizes, If they are not 8, then you have miscompiled unixODBC.

[paul-lilley]

Hi, thanks for the comment.
I agree that it looks like a 32/64bit issue (or possibly unsigned/signed long).
I'm pretty sure I have odbc compiled as 64bit:

unixODBC 2.3.7
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /home/xxxxxx/xxxxxx/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[v-chojas]

That further narrows it down to the ODBC driver, the unixODBC configuration appears correct.

[paul-lilley]

I think I've found the problem, as you suggested in the ODBC driver.
I'd incorrectly installed the DB2 driver as the 64bit CLI driver (libdb2.so) which is also a 32bit ODBC driver, rather than the specific 64bit ODBC Manager driver (libdb2o.so).
This mistake was hidden in the logging from unixODBC which explictly casts the SQLLEN to int before logging it. I'll put a patch request together to unixODBC to improve the logging and show the SQLLEN as a long if it is defined as such.
I think the change in pyodbc I suggested above is this not required for my problem, though someone who uses FreeTDS 0.91 on OS/X may get some mileage from it.

Thanks for your input @v-chojas !

[manjunathsudheer666]

What is the workaround for this error?

[8orbs]

same question

[chinmaykel]

@paul-lilley @v-chojas
How were you able to establish that TRACE which gave you the cbFetched value?

[v-chojas]

https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log ?