MySQL Bugs: #58065: join condition handles date columns differently if there's a unique key

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

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 |
+---+---+------------+

Also tested with InnoDB, same results.

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 |
+---+------+------------+

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

Thank you for the report.

Verified as described.