MySQL Bugs: #72262: Wrong "DATETIME" result (MyISAM-table) via ODBC and Access 2010

Bug #72262	Wrong "DATETIME" result (MyISAM-table) via ODBC and Access 2010
Submitted:	7 Apr 2014 10:26	Modified:	15 Apr 2014 3:05
Reporter:	a a	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	Connector/ODBC 5.2.6 (32bit)	OS:	Windows (Windows 7)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	connector, myisam, ODBC

Description:
A via ODBC (5.2.6 Connector) to MS Access connected MyISAM-table delivers wrong results for a date column(data_type "DATETIME").
The bug occurs when a filter is set to a column and the date-column is already ordered. The result is a filtered result with wrong date values.

This error does not occur when the table is set to InnoDB.

This could also be a bug in Microsoft Access.

How to repeat:
Create a MyISAM table:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| PID   | int(11)  | NO   | MUL | NULL    |       |
| WID   | int(11)  | NO   | MUL | NULL    |       |
| HID   | int(11)  | NO   | MUL | NULL    |       |
| LID   | char(3)  | NO   | MUL | NULL    |       |
| CID   | char(2)  | NO   |     | NULL    |       |
| Date  | datetime | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+

- add some data to the table
- Connect (link) database via ODBC connector (5.2.6. 32bit) to MS Access
- order by date
- filter by WID

result: date does not have the correct value.

Suggested fix:
date should have the correct ordered values.

Please provide the show create table table_name output ans some insert data
commands which makes repeatable this issue. Thanks.

I added a dump file for this table (visible for developers only)

Are you tried adding a primary key and timestamp columns i.e:

mysql> alter table table_name add column ID INT NOT NULL AUTO_INCREMENT PRIMARY
KEY;
Query OK, 67724 rows affected (0.81 sec)
Records: 67724  Duplicates: 0  Warnings: 0

mysql> alter table table_name add column TM TIMESTAMP;
Query OK, 67724 rows affected (0.70 sec)
Records: 67724  Duplicates: 0  Warnings: 0

Yes, with a primary key the bug does not occur but we don’t want to use a primary key or a timestamp column.

When you create a linked table in MS Access it prompts to select a column, which could be used as a unique identifier. In other words it wants a primary key or a unique index. Which one should I select?

Selecting a wrong column may cause Access to break the data integrity.
The only non-repeatable column values I could see in the Date column, so I selected it as the unique identifier.

So, I ordered data by the Date column and applied a filter to WID.
Please check the screenshot (access_date_sort.png) in the next post. The table does not look wrong to me.

The issue you had is the result of violation of the relational database design basic principles.
It does not look like a bug to me.

I am waiting for your reply, otherwise the report will get "not a bug" status.

For the linked access-table we selected all columns except the data column. Unfortunately this selection does not lead to a unique identifier. After adding the data column to the selection everything works as excepted.

So you can set this request to “not a bug”. Thanks for your help.

Thanks for your reply.
Setting "not a bug" status.