Bug #64667 MYSQL_TYPE_DATE expression when binding does not return result
Submitted: 16 Mar 2012 12:26 Modified: 3 Aug 2012 15:51
Reporter: Erik Remmelzwaal Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.6.4-m7 OS:Any
Assigned to: CPU Architecture:Any
Tags: binding, expression, MYSQL_TYPE_DATE, SELECT, where-clause

[16 Mar 2012 12:26] Erik Remmelzwaal
Description:
Using a query like:
  SELECT 4 FROM tdbtst120000 a0 USE INDEX (PRIMARY) 
  WHERE DATE '2012/03/16' = ( DATE '2012/03/16' + 0 ) AND a0.t_empno = 10
But then executed via the c-api does not return any result.
This does not happen in 5.5. or before, but is only observed in 5.6.4-m7 release.
This does not happen for MYSQL_TYPE_DATETIME, but only MYSQL_TYPE_DATE

Database runs on Windows 64bit 2008 server.

How to repeat:
Test program will be attached. Near the top there are compile instructions for Linux 2.6. and variables for host and user names.

Program will create it's own table for testing.

The output of this program should read (made on MySQL 5.5)
The tests: 4,6 and 8 are failing on 5.6.x
:
SELECT 1 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = ? AND a0.t_empno = ?
        Bind 1 = 2012/3/16 0:0:0
        Bind 2 = 2012/3/16 0:0:0
        Bind 3 = 10
         Returns 1
SELECT 2 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = ? AND a0.t_empno = ?
        Bind 1 = 2012/3/16 10:45:05
        Bind 2 = 2012/3/16 10:45:05
        Bind 3 = 10
         Returns 2
SELECT 3 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = ? AND a0.t_empno = ?
        Bind 1 = 2012/3/16 00:00:00(MYSQL_TYPE_DATE)
        Bind 2 = 2012/3/16 00:00:00(MYSQL_TYPE_DATE)
        Bind 3 = 10
         Returns 3
SELECT 4 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = (? + ?) AND a0.t_empno = ?
        Bind 1 = 2012/3/16 00:00:00(MYSQL_TYPE_DATE)
        Bind 2 = 2012/3/16 00:00:00(MYSQL_TYPE_DATE)
        Bind 3 = 0
        Bind 4 = 10
         Returns 4
SELECT 5 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = (? + ?) AND a0.t_empno = ?
        Bind 1 = 2012/3/16 10:45:05(MYSQL_TYPE_DATETIME)
        Bind 2 = 2012/3/16 10:45:05(MYSQL_TYPE_DATETIME)
        Bind 3 = 0
        Bind 4 = 10
         Returns 5
SELECT 6 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = (? + ?) AND a0.t_empno = ?
        Bind 1 = 2010/9/20 00:00:00 (MYSQL_TYPE_DATE)
        Bind 2 = 2010/9/19 00:00:00 (MYSQL_TYPE_DATE)
        Bind 3 = 1
        Bind 4 = 10
         Returns 6
SELECT 7 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = (? + ?) AND a0.t_empno = ?
        Bind 1 = 2010/9/20 05:43:19 (MYSQL_TYPE_DATETIME)
        Bind 2 = 2010/9/20 05:43:17 (MYSQL_TYPE_DATETIME)
        Bind 3 = 2010
        Bind 4 = 2
         Returns 7
SELECT 8 FROM tdbtst120000 a0 USE INDEX (PRIMARY) WHERE ? = (? + ?) AND a0.t_empno = ?
        Bind 1 = 2010/3/16 00:00:00 (MYSQL_TYPE_DATE)
        Bind 2 = 2010/3/16 00:00:00 (MYSQL_TYPE_DATE)
        Bind 3 = 0
        Bind 4 = 10
         Returns 8
[16 Mar 2012 12:27] Erik Remmelzwaal
Changed typo in synopsis
[19 Mar 2012 10:11] Erik Remmelzwaal
Increased level to S2 because it is a bug in 5.6, that does not happen in 5.5.
[3 Aug 2012 15:51] Paul DuBois
Noted in 5.6.7, 5.7.0 changelogs.

In prepared statements, MYSQL_TYPE_DATE parameters when converted to
an integer were handled as MYSQL_TYPE_DATETIME values and the 
conversion produced incorrect results.