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

[25 Oct 2007 7:55] David Mobbs
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.
[25 Oct 2007 8:54] Sveta Smirnova
Thank you for the report.

From description this can be ODBC problem.

Please provide output of same queries running in mysql command line client.
[25 Oct 2007 9:24] David Mobbs
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
[25 Oct 2007 9:42] Sveta Smirnova
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.