Merge epotemkin@bk-internal.mysql.com:/home/bk/mysql-5.0

into moonbone.local:/work/14093-bug-5.0-mysql


mysql-test/r/select.result:
  Auto merged
mysql-test/t/select.test:
  Auto merged
sql/item.h:
  Auto merged

5 files changed, 72 insertions, 1 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 76a8b5a11e7..318cf8e7b65 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3201,3 +3201,43 @@ a	b	c
 select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
 a	b	c
 drop table t1, t2 ,t3;
+create table t1(f1 int, f2 date);
+insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
+(4,'2005-10-01'),(5,'2005-12-30');
+select * from t1 where f2 >= 0;
+f1	f2
+1	2005-01-01
+2	2005-09-01
+3	2005-09-30
+4	2005-10-01
+5	2005-12-30
+select * from t1 where f2 >= '0000-00-00';
+f1	f2
+1	2005-01-01
+2	2005-09-01
+3	2005-09-30
+4	2005-10-01
+5	2005-12-30
+select * from t1 where f2 >= '2005-09-31';
+f1	f2
+4	2005-10-01
+5	2005-12-30
+select * from t1 where f2 >= '2005-09-3a';
+f1	f2
+4	2005-10-01
+5	2005-12-30
+Warnings:
+Warning	1292	Incorrect date value: '2005-09-3a' for column 'f2' at row 1
+select * from t1 where f2 <= '2005-09-31';
+f1	f2
+1	2005-01-01
+2	2005-09-01
+3	2005-09-30
+select * from t1 where f2 <= '2005-09-3a';
+f1	f2
+1	2005-01-01
+2	2005-09-01
+3	2005-09-30
+Warnings:
+Warning	1292	Incorrect date value: '2005-09-3a' for column 'f2' at row 1
+drop table t1;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index d38371577d9..e7e6d899a66 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2711,3 +2711,21 @@ select * from t1 join t2 left join t3 on (t1.a=t3.c);
 select * from t1 join t2 right join t3 on (t1.a=t3.c);
 select * from t1 join t2 straight_join t3 on (t1.a=t3.c);
 drop table t1, t2 ,t3;
+
+#
+# Bug #14093 Query takes a lot of time when date format is not valid
+# fix optimizes execution. so here we just check that returned set is
+# correct.
+create table t1(f1 int, f2 date);
+insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
+  (4,'2005-10-01'),(5,'2005-12-30');
+# should return all records
+select * from t1 where f2 >= 0;
+select * from t1 where f2 >= '0000-00-00';
+# should return 4,5
+select * from t1 where f2 >= '2005-09-31';
+select * from t1 where f2 >= '2005-09-3a';
+# should return 1,2,3
+select * from t1 where f2 <= '2005-09-31';
+select * from t1 where f2 <= '2005-09-3a';
+drop table t1;
diff --git a/sql/item.h b/sql/item.h
index 2f753564009..8bc659c3060 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1750,6 +1750,7 @@ public:
     return ref->save_in_field(field, no_conversions);
   }
   Item *new_item();
+  virtual Item *real_item() { return ref; }
 };
 
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 761d15c8a3e..06cb83a7101 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -186,13 +186,18 @@ static bool convert_constant_item(THD *thd, Field *field, Item **item)
 {
   if ((*item)->const_item())
   {
+    /* For comparison purposes allow invalid dates like 2000-01-32 */
+    ulong orig_sql_mode= field->table->in_use->variables.sql_mode;
+    field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
     if (!(*item)->save_in_field(field, 1) && !((*item)->null_value))
     {
       Item *tmp=new Item_int_with_ref(field->val_int(), *item);
+      field->table->in_use->variables.sql_mode= orig_sql_mode;
       if (tmp)
         thd->change_item_tree(item, tmp);
       return 1;					// Item was replaced
     }
+    field->table->in_use->variables.sql_mode= orig_sql_mode;
   }
   return 0;
 }
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ff2b14a27ee..4a9e2556df7 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3921,13 +3921,20 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
       value->result_type() != STRING_RESULT &&
       field->cmp_type() != value->result_type())
     goto end;
-
+  /* For comparison purposes allow invalid dates like 2000-01-32 */
+  ulong orig_sql_mode= field->table->in_use->variables.sql_mode;
+  if (value->real_item()->type() == Item::STRING_ITEM &&
+      (field->type() == FIELD_TYPE_DATE ||
+       field->type() == FIELD_TYPE_DATETIME))
+    field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
   if (value->save_in_field_no_warnings(field, 1) < 0)
   {
+    field->table->in_use->variables.sql_mode= orig_sql_mode;
     /* This happens when we try to insert a NULL field in a not null column */
     tree= &null_element;                        // cmp with NULL is never TRUE
     goto end;
   }
+  field->table->in_use->variables.sql_mode= orig_sql_mode;
   str= (char*) alloc_root(alloc, key_part->store_length+1);
   if (!str)
     goto end;