diff options
author | unknown <sergefp@foxhole.(none)> | 2007-12-13 13:39:37 +0300 |
---|---|---|
committer | unknown <sergefp@foxhole.(none)> | 2007-12-13 13:39:37 +0300 |
commit | 196f0c751f9a36b34f0dd5e15df4a9fcd51c0b81 (patch) | |
tree | 76aef67ae045193e0a22e882dfcd34f3ecb433a2 | |
parent | 1cdd95f7d1a27723c1ae1d3a6f457a0042b44795 (diff) | |
parent | c6675cd1876a90468ca2e2cb10471af93728aa6c (diff) | |
download | mariadb-git-196f0c751f9a36b34f0dd5e15df4a9fcd51c0b81.tar.gz |
Merge spetrunia@bk-internal.mysql.com:/home/bk/mysql-5.0-opt
into mysql.com:/home/psergey/mysql-5.0-bug32198
-rw-r--r-- | mysql-test/r/range.result | 18 | ||||
-rw-r--r-- | mysql-test/t/range.test | 20 | ||||
-rw-r--r-- | sql/field.cc | 3 | ||||
-rw-r--r-- | sql/opt_range.cc | 27 |
4 files changed, 67 insertions, 1 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 58f8d077ac5..e0084b53320 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -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 --git a/mysql-test/t/range.test b/mysql-test/t/range.test index b8d4b91f03d..87ba3510326 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -935,4 +935,24 @@ SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 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 --git a/sql/field.cc b/sql/field.cc index 86853389c64..955694933a0 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -5259,6 +5259,9 @@ void Field_date::sql_type(String &res) const 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 --git a/sql/opt_range.cc b/sql/opt_range.cc index 7a51dbbe76c..5d8bba69422 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -4414,6 +4414,7 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part, { 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_func, Field *field, KEY_PART *key_part, 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) { |