Fix for bug#49199: Optimizer handles incorrectly:

field='const1' AND field='const2' in some cases

Building multiple equality predicates containing
a constant which is compared as a datetime (with a field)
we should take this fact into account and compare the 
constant with another possible constatns as datetimes 
as well.

E.g. for the
SELECT ... WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'
we should compare '2001-01-01' with '2001-01-01 00:00:00' as
datetimes but not as strings.


mysql-test/r/select.result:
  Fix for bug#49199: Optimizer handles incorrectly: 
  field='const1' AND field='const2' in some cases
    - test result.
mysql-test/t/select.test:
  Fix for bug#49199: Optimizer handles incorrectly: 
  field='const1' AND field='const2' in some cases
    - test case.
sql/item_cmpfunc.cc:
  Fix for bug#49199: Optimizer handles incorrectly: 
  field='const1' AND field='const2' in some cases
    - adding a constant to Item_equal compare it as
  a datetime value with stored one if there's a 
  date[time] field in a equality predicate.
sql/item_cmpfunc.h:
  Fix for bug#49199: Optimizer handles incorrectly: 
  field='const1' AND field='const2' in some cases
    - adding a constant to Item_equal compare it as
  a datetime value with stored one if there's a 
  date[time] field in a equality predicate.
sql/sql_select.cc:
  Fix for bug#49199: Optimizer handles incorrectly: 
  field='const1' AND field='const2' in some cases
    - adding a constant to Item_equal compare it as
  a datetime value with stored one if there's a 
  date[time] field in a equality predicate.

1 files changed, 84 insertions, 0 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index ff59eadab0c..1b2533eec89 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4456,4 +4456,88 @@ SELECT 1 FROM t2 JOIN t1 ON 1=1
 WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a);
 1
 DROP TABLE t1,t2;
+#
+# Bug #49199: Optimizer handles incorrectly: 
+# field='const1' AND field='const2' in some cases
+
+CREATE TABLE t1(a DATETIME NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+a	b
+2001-01-01 00:00:00	2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+a	b
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 0
+SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+a	b
+2001-01-01 00:00:00	2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT x.a, y.a, z.a FROM t1 x 
+JOIN t1 y ON x.a=y.a 
+JOIN t1 z ON y.a=z.a 
+WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+a	a	a
+2001-01-01 00:00:00	2001-01-01 00:00:00	2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 
+JOIN t1 y ON x.a=y.a 
+JOIN t1 z ON y.a=z.a 
+WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	y	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	z	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from `test`.`t1` `x` join `test`.`t1` `y` join `test`.`t1` `z` where 1
 End of 5.0 tests