Bug #15773 Wrong between results
Submitted: 15 Dec 2005 12:00 Modified: 6 Jun 2007 11:57
Reporter: Felix Wederz Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51 OS:Microsoft Windows (Windows XP)
Assigned to: Jim Winstead CPU Architecture:Any

[15 Dec 2005 12:00] Felix Wederz
If you have a query using BETWEEN (for example: "SELECT * FROM test WHERE ( ?dDatum ) BETWEEN b AND c" and dDatum equals {^2005-12-15}) then we get a result with b = 2005-12-24 and c = 2008-05-12. b is already greater then the Date we inserted.

This bug appearing using MySQL ODBC 3.51 Driver with Visual FoxPro.

How to repeat:
Here is our test-table:

  `a` varchar(255) NOT NULL default '',
  `b` datetime NOT NULL default '0000-00-00 00:00:00',
  `c` datetime NOT NULL default '0000-00-00 00:00:00'

INSERT INTO `test` VALUES ('a', '2005-12-24 00:00:00', '2008-05-12 00:00:00');
INSERT INTO `test` VALUES ('b', '2004-01-01 00:00:00', '2005-01-01 00:00:00');
INSERT INTO `test` VALUES ('c', '2004-12-12 00:00:00', '2005-12-12 00:00:00');

After we made this table, we made following Query:
SELECT * FROM test WHERE {^2005-12-15} BETWEEN b AND c
[15 Dec 2005 17:16] Valeriy Kravchuk
Thank you for a problem report. Please, inform about the exact version of MyODBC and MySQL server used. Send also the real result you get from the query. Have you tried to use mysql command line client. Do you have the same result in it?
[16 Dec 2005 7:51] Felix Wederz
We are using MyODBC with MySQL 5.0.16.

The Result we get from the query is exactly 1 row.
Value in Field A: "a"
Value in Field B: 2005-12-24
Value in Field C: 2008-05-12

The executed query is: "SELECT * FROM test WHERE ( ?dDatum ) BETWEEN b AND c" while the variable dDatum equals 2005-12-15

If we execute this on the command-line client, we get the right result (0 rows).

We need an answer urgently! These querys are use to do critical calculations!!
[16 Dec 2005 10:27] Valeriy Kravchuk
Thank you for the additional information. For urgent support requests we provide a commercial support. Here we have to create a test case to be able to repeat the bug easily. 

So, if it is urgent for you, please, provide a simple, but complete test case that shows the behaviour you described. You can also simply switch on the tracing for the ODBC data source used and upload a trace file using the File tab.

It can be a bug in MyODBC, but it can be also some problem in your application. That is why I am asking you for all these.
[16 Dec 2005 11:17] Felix Wederz
MySQL - Logfile

Attachment: mysql.log (application/octet-stream, text), 1.01 KiB.

[16 Dec 2005 11:24] Felix Wederz
Ive added the mysql.log file, because the ODBC-Driver is not creating any logfile (even with logging turned on).

For a test case follow the infos of the first contact (testtable with testdata) and execute the commands of the logfile. Then you will see you get a wrong result.

When do you think the problem will be fixed??
[16 Dec 2005 14:19] Sebastien Caisse
I tested this through a MS Access 2003, using the MyODBC 3.51.12-2 driver with MySQL 5.0.16. The request returned no records as expected, and here is the query log of the server:
		      2 Query       SELECT `test`.`a` FROM `test` WHERE ({d '2005-12-15'} BETWEEN `b` AND `c` )

So well, it's not ODBC specific, it has something to do with FoxPro I would guess...
[16 Dec 2005 16:46] Vasily Kishkin
I was able to reproduce the bug on C test case. Result of query is :

a 2005-12-24 2008-5-12

My test case is attached.
[16 Dec 2005 16:46] Vasily Kishkin
Test case

Attachment: test.c (text/plain), 4.56 KiB.

[3 Jun 2007 12:04] Georg Richter
Can't repeat with 3.51.16 (looks like it was fixed already in 3.51.14).

Added test case in my_datetime.c
[4 Jun 2007 23:22] Jim Winstead
This is actually sensitive to the server version, or at least Vasily's test-case is (as is the one that Georg added to the repository). I'm not sure which version of 5.0 it is fixed in, but it was sometime after 5.0.26.

The problem is that the Connector/ODBC driver is sending the date to the server as '20051215' and that is being handled incorrectly. If the driver sent '2005-12-15', it would be handled correctly in this case.

A workaround is to add DATE() around the date using in the BETWEEN clause, so it ends up being "WHERE DATE('20051215') BETWEEN b AND c".
[4 Jun 2007 23:56] Jim Winstead
Include seperators in date/time types when sending to the server

Attachment: bug15773.diff (text/plain), 2.59 KiB.

[5 Jun 2007 0:06] Mark Matthews
Looks fine to me, other than it won't support fractional seconds whenever _that_ happens, but I suppose we all cross that bridge when it comes.
[5 Jun 2007 0:25] Jim Winstead
The fix for this issue has been committed to the source repository, and will be part of the 3.51.16 release. Thanks for the bug report.
[6 Jun 2007 11:57] MC Brown
A note has been added to the 3.51.16 changelog.