MySQL Bugs: #31835: Incorrect handling of dates when used in an IN condition in the WHERE clause

Bug #31835	Incorrect handling of dates when used in an IN condition in the WHERE clause
Submitted:	25 Oct 2007 7:55	Modified:	25 Oct 2007 9:42
Reporter:	David Mobbs	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	MySQL Server 4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am using ODBC driver 3.51.21.00 and am querying a MySQL 4.0 database.
I am trying to run a query of the form SELECT x FROM table WHERE x in (value1, value2, ...)
When x is a date field and I use ODBC escape sequences to describe the date, no result is returned.

How to repeat:
Have a database with a SALES table containing an INVOICE_DATE field.
Run the following query :
SELECT
  sales.INVOICE_DATE
FROM
  sales
WHERE
  sales.INVOICE_DATE  =  {d '1993-01-12 00:00:00'}
If you have populated your table with the date value, you get a result set with 1 line.
Now try the following query :
SELECT
  sales.INVOICE_DATE
FROM
  sales
WHERE
  sales.INVOICE_DATE  IN  ({d '1993-01-12 00:00:00'})
An empty result set is returned. We should get the same result as the first query.

Thank you for the report.

From description this can be ODBC problem.

Please provide output of same queries running in mysql command line client.

Output of the queries using WinSQL :

First query (using = operator) :
INVOICE_DATE 
------------ 
1993-01-12   
 
1 Row(s) affected 

Second query (using IN operator) :
INVOICE_DATE 
------------ 
 
0 Row(s) affected

Thank you for the feedback.

This is duplicate of bug #28133

According to http://www.mysql.com/company/legal/lifecycle/#calendar active support for MySQL 4.0 is finished. Please consider to upgrade to newer version.