dbGetQuery() returns random results for DATETIME fields instead of NULL with Azure DB

[mikecrobp]

Issue Description and Expected Result

have just started to use dbGetQuery(). Connection and dbGetQuery complete OK.
All values OK - except for DateTime fields where type is DATETIME2 and value is NULL. In this case the result is not NULL and is a random date, sometimes negative.

Database

Microsoft Azure SQL Data Warehouse 10.0.15543
ODBC Driver is "ODBC Driver 17 for SQL Server"

Reproducible Example

A simple SELECT * FROM table should show this if my problem is not 100% local

R and odbc fully up to date

[detule]

@mikecrobp I don't have access to Azure DB.

• Do you have LONG (VARCHAR, for example) data columns in your table before the datetime field you are seeing this peculiar behavior for?
• Do you see the issue if you were to only SELECT [this_one_particular_field] FROM table ?

If Yes, and No then I have an idea what the issue might be. If I were to put together a custom patch, do you have the ability to re-build the package and test it out?

Thanks

[mikecrobp]

@mikecrobp I don't have access to Azure DB.

• Do you have LONG (VARCHAR, for example) data columns in your table before the datetime field you are seeing this peculiar behavior for?
• Do you see the issue if you were to only SELECT [this_one_particular_field] FROM table ?

If Yes, and No then I have an idea what the issue might be. If I were to put together a custom patch, do you have the ability to re-build the package and test it out?

Thanks. Indeed I do have VARCHAR (in fact NVARCHAR) columns in the query before the first datetime. And selecting the DATETIME only eliminates the problem.

I am happy to test a custom path. I might need a few instructions to build a new copy of the library from the source - but very happy to.

Mike

[detule]

Mike:

If you get a chance, try building / testing with the branch:

https://github.com/detule/odbc/tree/fixup/null_detection_unbound_types

[mikecrobp]

I had some issues but have now tested my problem dates and they are now showing NA where there is NULL in database
So that fixes the issue.
I can run a more formal test but I can't create new tables to test against.

Installation method: install_github("https://github.com/detule/odbc/tree/fixup/null_detection_unbound_types" )
That gives me odbc_1.3.0.9000

Mike