[ODBC-361] MS Access will get wrong data if unique index on nullable column is used Created: 2022-05-23  Updated: 2022-06-20  Resolved: 2022-05-30

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.1.15
Fix Version/s: 3.1.16

Type: Bug Priority: Major
Reporter: CHARLIE CHU Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 10 x64, MS Access in OFFICE 365, using MariaDB ODBC connector 3.1.15

Attachments: JPEG File MariaDB_Connector_BUG.jpg     File f10-e1.sql    

 Description   

MS Access as front end app, using MariaDB ODBC connector 3.1.15 to connect MariaDB server.
A table named: f10-e1
I can not get correct data.
If I use MYSQL ODBC connector, the result will be correct.
Please see attachment.

Is this table name conflict with something?

Charlie

 Comments   
Comment by Lawrin Novitsky [ 2022-05-23 ]

Thank you for your report. Could you please provide structure of the table f10-e1?

Comment by Lawrin Novitsky [ 2022-05-23 ]

And if possible - some minimal test content of the table that exposes the problem

Comment by CHARLIE CHU [ 2022-05-23 ]

f10-e1.sql shows the structure and data in my db. Nothing special.
There are several tables with same structure in the db, only this table can not be correctly handled.

Comment by Lawrin Novitsky [ 2022-05-30 ]

Unique indexes with nullable (part) column were returned by
catalog functions as unique. While they are in fact cannot uniquely
identify a row(if nullable column of the key is null). That could causeerrors in Access for linked tables with such indexes.

As workaround one can rename the unique index to have the name > 'PRIMARY', and in this way to be returned after PRIMARY key in the list of indexes. I think Access just taking first index marked as unique.

Generated at Thu Feb 08 03:28:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.