bk commit into 5.0 tree (sergefp:1.2595) BUG#32198

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey 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@stripped, 2007-12-13 13:38:22+03:00, sergefp@stripped +4 -0
  BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
  - Make conditions like "date_col $CMP$ 'datetime-const'" range-sargable

  mysql-test/r/range.result@stripped, 2007-12-13 13:38:19+03:00, sergefp@stripped +18 -0
    BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
    - Testcase

  mysql-test/t/range.test@stripped, 2007-12-13 13:38:19+03:00, sergefp@stripped +20 -0
    BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
    - Testcase

  sql/field.cc@stripped, 2007-12-13 13:38:19+03:00, sergefp@stripped +3 -0
    BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
    - Added comments

  sql/opt_range.cc@stripped, 2007-12-13 13:38:20+03:00, sergefp@stripped +26 -1
    BUG#32198: Comparison of DATE with DATETIME still not using indexes correctly
    - Make conditions like "date_col $CMP$ 'datetime-const'" range-sargable

diff -Nrup a/mysql-test/r/range.result b/mysql-test/r/range.result
--- a/mysql-test/r/range.result	2007-03-31 00:46:27 +04:00
+++ b/mysql-test/r/range.result	2007-12-13 13:38:19 +03:00
@@ -1135,3 +1135,21 @@ item	started	price
 A1	2005-11-01 08:00:00	1000.000
 A1	2005-11-15 00:00:00	2000.000
 DROP TABLE t1;
+
+BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly"
+
+CREATE TABLE t1 (
+id int(11) NOT NULL auto_increment,
+dateval date default NULL,
+PRIMARY KEY  (id),
+KEY dateval (dateval)
+) AUTO_INCREMENT=173;
+INSERT INTO t1 VALUES
+(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
+(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
+(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
+This must use range access:
+explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <=
'2007-01-02 23:59:59';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	dateval	dateval	4	NULL	2	Using where
+drop table t1;
diff -Nrup a/mysql-test/t/range.test b/mysql-test/t/range.test
--- a/mysql-test/t/range.test	2007-03-31 00:46:27 +04:00
+++ b/mysql-test/t/range.test	2007-12-13 13:38:19 +03:00
@@ -935,4 +935,24 @@ SELECT * FROM t1 WHERE item='A1' AND sta
 
 DROP TABLE t1;
 
+--echo
+--echo BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly"
+--echo
+CREATE TABLE t1 (
+  id int(11) NOT NULL auto_increment,
+  dateval date default NULL,
+  PRIMARY KEY  (id),
+  KEY dateval (dateval)
+) AUTO_INCREMENT=173;
+
+INSERT INTO t1 VALUES
+(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
+(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
+(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
+
+--echo This must use range access:
+explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <=
'2007-01-02 23:59:59';
+
+drop table t1;
+
 # End of 5.0 tests
diff -Nrup a/sql/field.cc b/sql/field.cc
--- a/sql/field.cc	2007-11-17 19:23:12 +03:00
+++ b/sql/field.cc	2007-12-13 13:38:19 +03:00
@@ -5259,6 +5259,9 @@ void Field_date::sql_type(String &res) c
     1  Value was cut during conversion
     2  Wrong date string
     3  Datetime value that was cut (warning level NOTE)
+       This is used by opt_range.cc:get_mm_leaf(). Note that there is a
+       nearly-identical class Field_date doesn't ever return 3 from its
+       store function.
 */
 
 int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2007-11-27 18:28:26 +03:00
+++ b/sql/opt_range.cc	2007-12-13 13:38:20 +03:00
@@ -4414,6 +4414,7 @@ get_mm_leaf(PARAM *param, COND *conf_fun
     {
       tree= new (alloc) SEL_ARG(field, 0, 0);
       tree->type= SEL_ARG::IMPOSSIBLE;
+      goto end;
     }
     else
     {
@@ -4422,8 +4423,32 @@ get_mm_leaf(PARAM *param, COND *conf_fun
         for the cases like int_field > 999999999999999999999999 as well.
       */
       tree= 0;
+      if (err == 3 && field->type() == FIELD_TYPE_DATE && 
+          (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC || 
+           type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+      {
+        /*
+          We were saving DATETIME into a DATE column, the conversion went ok
+          but a non-zero time part was cut off.
+
+          In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
+          values. Index over a DATE column uses DATE comparison. Changing 
+          from one comparison to the other is possible:
+
+          datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
+          datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
+
+          datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
+          datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
+
+          but we'll need to convert '>' to '>=' and '<' to '<='. This will
+          be done together with other types at the end of this function
+          (grep for field_is_equal_to_item)
+        */
+      }
+      else
+        goto end;
     }
-    goto end;
   } 
   if (err < 0)
   {