When linking from MSAccess to a table on MariaDB which has a primary key and a second unique key on a field which allows NULL values, the key with the alphabetically lower name is selected. This results into wrong displayed records in MSAccess table view.

Details:
While linking to a table on MariaDB, the ODBC queries MariaDB for possible unique key candidates with the statement

SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, NON_UNIQUE, NULL AS INDEX_QUALIFIER, INDEX_NAME, 3 AS TYPE, SEQ_IN_INDEX AS ORDINAL_POSITION, COLUMN_NAME, COLLATION AS ASC_OR_DESC, CARDINALITY, NULL AS PAGES, NULL AS FILTER_CONDITION FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE IF(DATABASE() IS NOT NULL, DATABASE(), '%') AND TABLE_NAME LIKE 'mytab' ORDER BY NON_UNIQUE, INDEX_NAME, ORDINAL_POSITION}}

It WANTS to have an absolute unique field name in order to retrieve the correct record, but it does not consider that UNIQUE Indexes in MariaDB can contain NULL values (multiple times), only the NON-NULL values have to be unique.
When it happens that the name of the unique key starts with a term lesser than 'PRIMARY KEY', this key is selected thus requesting all records with this key field in the condition.

This results in e.g. MSAccess table view to multiple Queries like 'SELECT * FROM myTab WHERE a_unique_id IS NULL', which returns more than one row, but only the first is displayed.

So, has a table this format and entries:

CREATE TABLE mytab (

  mytab_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  text_1 VARCHAR(100) NOT NULL,

  a_unique_id VARCHAR(20) NULL UNIQUE KEY

);

INSERT INTO mytab (text_1, a_unique_id) VALUES 

  ('first entry', '1st'),

  ('second entry', '2nd'),

  ('third entry', NULL),

  ('fourth entry', NULL);

In Access the table view shows the records:

• third entry
• third entry
• first entry
• second entry