summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorunknown <sergefp@foxhole.(none)>2007-12-13 13:39:37 +0300
committerunknown <sergefp@foxhole.(none)>2007-12-13 13:39:37 +0300
commit196f0c751f9a36b34f0dd5e15df4a9fcd51c0b81 (patch)
tree76aef67ae045193e0a22e882dfcd34f3ecb433a2
parent1cdd95f7d1a27723c1ae1d3a6f457a0042b44795 (diff)
parentc6675cd1876a90468ca2e2cb10471af93728aa6c (diff)
downloadmariadb-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.result18
-rw-r--r--mysql-test/t/range.test20
-rw-r--r--sql/field.cc3
-rw-r--r--sql/opt_range.cc27
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)
{