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, 49 insertions, 0 deletions

diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index a4d3056b66e..eeabd2641d8 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3797,4 +3797,53 @@ SELECT 1 FROM t2 JOIN t1 ON 1=1
 DROP TABLE t1,t2;
 
 
+--echo #
+--echo # Bug #49199: Optimizer handles incorrectly: 
+--echo # field='const1' AND field='const2' in some cases
+--echo
+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';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+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';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+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';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+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';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+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';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+
+SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+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';
+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';
+
+
 --echo End of 5.0 tests