From: Date: November 3 2005 11:53am
Subject: bk commit into 5.0 tree (evgen:1.1949) BUG#14093
List-Archive: http://lists.mysql.com/internals/31872
X-Bug: 14093
Message-Id: <20051103105355.383AA22D3B5@localhost.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1949 05/11/03 13:53:49 evgen@stripped +5 -0
  Fix bug #14093 Query takes a lot of time when date format is not valid
  
  Invalid date like 2000-02-32 wasn't converted to int, which lead to not
  using index and comparison with field as astring, which results in slow
  query execution.
  
  convert_constatn_item() and get_mm_leaf() now forces MODE_INVALID_DATES to
  allow such conversion.

  mysql-test/t/select.test
    1.89 05/11/03 13:52:54 evgen@stripped +18 -0
    Test case for bug#14093 Query takes a lot of time when date format is not valid

  mysql-test/r/select.result
    1.108 05/11/03 13:52:10 evgen@stripped +40 -0
    Test case for bug#14093 Query takes a lot of time when date format is not valid

  sql/opt_range.cc
    1.194 05/11/03 13:51:53 evgen@stripped +8 -1
     Fix bug #14093 Query takes a lot of time when date format is not valid
    get_mm_leaf() modified so it allows index usage for comparing fields with invalid dates like 2000-01-32.

  sql/item_cmpfunc.cc
    1.184 05/11/03 13:51:41 evgen@stripped +5 -0
     Fix bug #14093 Query takes a lot of time when date format is not valid
    convert_constant_item() now allows conversion of invalid dates like 2000-01-32 to int to make it possible to use index when comparing fields with such dates.

  sql/item.h
    1.177 05/11/03 13:51:28 evgen@stripped +1 -0
    Fix bug #14093 Query takes a lot of time when date format is not valid
    To Item_int_with_ref added method real_item() which returns ref.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/14093-bug-5.0-mysql

--- 1.176/sql/item.h	2005-10-20 10:55:25 +04:00
+++ 1.177/sql/item.h	2005-11-03 13:51:28 +03:00
@@ -1749,6 +1749,7 @@
     return ref->save_in_field(field, no_conversions);
   }
   Item *new_item();
+  virtual Item *real_item() { return ref; }
 };
 
 

--- 1.183/sql/item_cmpfunc.cc	2005-10-21 05:01:31 +04:00
+++ 1.184/sql/item_cmpfunc.cc	2005-11-03 13:51:41 +03:00
@@ -186,13 +186,18 @@
 {
   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;
 }

--- 1.193/sql/opt_range.cc	2005-10-19 12:41:23 +04:00
+++ 1.194/sql/opt_range.cc	2005-11-03 13:51:53 +03:00
@@ -3921,13 +3921,20 @@
       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;

--- 1.107/mysql-test/r/select.result	2005-10-25 10:01:42 +04:00
+++ 1.108/mysql-test/r/select.result	2005-11-03 13:52:10 +03:00
@@ -3193,3 +3193,43 @@
 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;

--- 1.88/mysql-test/t/select.test	2005-10-25 10:01:42 +04:00
+++ 1.89/mysql-test/t/select.test	2005-11-03 13:52:54 +03:00
@@ -2702,3 +2702,21 @@
 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;