Bug #58065 join condition handles date columns differently if there's a unique key
Submitted: 8 Nov 2010 21:31 Modified: 10 Nov 2010 12:26
Reporter: Balint Toth Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.51, 5.1.52, 5.0, 5.1, 5.6.99 bzr OS:Linux (x64, 2.6.32)
Assigned to: CPU Architecture:Any
Tags: const date format, unique key

[8 Nov 2010 21:31] Balint Toth
Description:
If you add a unique key to a table with a date and an int column, join filtering behaves differently with date format 'YYYY-MM-DD' and 'YYYYMMDD'

How to repeat:
 CREATE TABLE `t_i` (
  `a` int(10) unsigned NOT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

 CREATE TABLE `t_d` (
  `a` int(10) unsigned NOT NULL,
  `d` date NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into t_i values (1),(2);
insert into t_d values (1,curdate()),(2,curdate());

select * From t_i left join t_d on t_i.a=t_d.a and t_d.d='20101108' where t_i.a=1;
+---+------+------------+
| a | a    | d          |
+---+------+------------+
| 1 |    1 | 2010-11-08 |
+---+------+------------+

alter table t_d add unique (a,d);
 select * From t_i left join t_d on t_i.a=t_d.a and t_d.d='20101108' where t_i.a=1;
+---+------+------+
| a | a    | d    |
+---+------+------+
| 1 | NULL | NULL |
+---+------+------+

but if you change date format: 

select * From t_i left join t_d on t_i.a=t_d.a and t_d.d=20101108 where t_i.a=1;
+---+---+------------+
| a | a | d          |
+---+---+------------+
| 1 | 1 | 2010-11-08 |
+---+---+------------+

 select * From t_i left join t_d on t_i.a=t_d.a and t_d.d='2010-11-08' where t_i.a=1;
+---+---+------------+
| a | a | d          |
+---+---+------------+
| 1 | 1 | 2010-11-08 |
+---+---+------------+
[8 Nov 2010 21:35] Balint Toth
Also tested with InnoDB, same results.
[8 Nov 2010 21:45] Balint Toth
Also interesting: if you remove the where condition, the result is good:

select * From t_i left join t_d on t_i.a=t_d.a and t_d.d='20101108' ;
+---+------+------------+
| a | a    | d          |
+---+------+------------+
| 1 |    1 | 2010-11-08 |
| 2 |    2 | 2010-11-08 |
+---+------+------------+
[9 Nov 2010 21:47] Peter Szekvolgyi
I have the same problem with version 5.1.52.

mysql --version output:
mysql  Ver 14.14 Distrib 5.1.52, for portbld-freebsd8.1 (amd64) using  5.2
[10 Nov 2010 12:26] Sveta Smirnova
Thank you for the report.

Verified as described.