#At file:///home/bar/mysql-bzr/mysql-5.5-bugteam.b58190/ based on revid:bar@stripped
3138 Alexander Barkov 2010-11-19
Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
Regression introduced by WL#2649.
Problem: queries with date/datetime columns did not use indexes:
set names non_latin1_charset;
select * from date_index_test
where date_column between '2010-09-01' and '2010-10-01';
before WL#2649 indexes worked fine because charset of
date/datetime
columns was BINARY which always won.
Fix: testing that collation of the operation matches collation
of the field is only needed in case of "real" string data types.
For DATE, DATETIME it's not needed.
@ mysql-test/include/ctype_numconv.inc
@ mysql-test/r/ctype_binary.result
@ mysql-test/r/ctype_cp1251.result
@ mysql-test/r/ctype_latin1.result
@ mysql-test/r/ctype_ucs.result
@ mysql-test/r/ctype_utf8.result
Adding tests
@ sql/field.h
Adding new method Field_str::match_collation_to_optimize_range()
for use in opt_range.cc to distinguish between
"real string" types like CHAR, VARCHAR, TEXT
(Field_string, Field_varstring, Field_blob)
and "almost string" types DATE, TIME, DATETIME
(Field_newdate, Field_datetime, Field_time, Field_timestamp)
@ sql/opt_range.cc
Using new method instead of checking result_type() against STRING result.
Note:
Another part of this problem (which is not regression)
is submitted separately (see bug##58329).
modified:
mysql-test/include/ctype_numconv.inc
mysql-test/r/ctype_binary.result
mysql-test/r/ctype_cp1251.result
mysql-test/r/ctype_latin1.result
mysql-test/r/ctype_ucs.result
mysql-test/r/ctype_utf8.result
sql/field.h
sql/opt_range.cc
=== modified file 'mysql-test/include/ctype_numconv.inc'
--- a/mysql-test/include/ctype_numconv.inc 2010-10-06 12:15:59 +0000
+++ b/mysql-test/include/ctype_numconv.inc 2010-11-19 17:15:47 +0000
@@ -1723,6 +1723,21 @@ DROP TABLE t1;
--echo #
+--echo # Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+--echo #
+SELECT @@collation_connection;
+CREATE TABLE t1 (
+ id INT(11) DEFAULT NULL,
+ date_column DATE DEFAULT NULL,
+ KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+DROP TABLE t1;
+
+
+--echo #
--echo # Bug#52159 returning time type from function and empty left join causes debug assertion
--echo #
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result 2010-10-31 23:23:37 +0000
+++ b/mysql-test/r/ctype_binary.result 2010-11-19 17:15:47 +0000
@@ -2748,6 +2748,25 @@ DROP TABLE t1;
# End of Bug#54916
#
#
+# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+#
+SELECT @@collation_connection;
+@@collation_connection
+binary
+CREATE TABLE t1 (
+id INT(11) DEFAULT NULL,
+date_column DATE DEFAULT NULL,
+KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
+DROP TABLE t1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result 2010-10-31 23:23:37 +0000
+++ b/mysql-test/r/ctype_cp1251.result 2010-11-19 17:15:47 +0000
@@ -2830,6 +2830,25 @@ DROP TABLE t1;
# End of Bug#54916
#
#
+# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+#
+SELECT @@collation_connection;
+@@collation_connection
+cp1251_general_ci
+CREATE TABLE t1 (
+id INT(11) DEFAULT NULL,
+date_column DATE DEFAULT NULL,
+KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
+DROP TABLE t1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result 2010-10-31 23:23:37 +0000
+++ b/mysql-test/r/ctype_latin1.result 2010-11-19 17:15:47 +0000
@@ -3158,6 +3158,25 @@ DROP TABLE t1;
# End of Bug#54916
#
#
+# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+#
+SELECT @@collation_connection;
+@@collation_connection
+latin1_swedish_ci
+CREATE TABLE t1 (
+id INT(11) DEFAULT NULL,
+date_column DATE DEFAULT NULL,
+KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
+DROP TABLE t1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result 2010-10-31 23:23:37 +0000
+++ b/mysql-test/r/ctype_ucs.result 2010-11-19 17:15:47 +0000
@@ -3990,6 +3990,25 @@ DROP TABLE t1;
# End of Bug#54916
#
#
+# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+#
+SELECT @@collation_connection;
+@@collation_connection
+ucs2_general_ci
+CREATE TABLE t1 (
+id INT(11) DEFAULT NULL,
+date_column DATE DEFAULT NULL,
+KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+DROP TABLE t1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result 2010-11-09 08:08:02 +0000
+++ b/mysql-test/r/ctype_utf8.result 2010-11-19 17:15:47 +0000
@@ -4848,6 +4848,25 @@ DROP TABLE t1;
# End of Bug#54916
#
#
+# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf8_general_ci
+CREATE TABLE t1 (
+id INT(11) DEFAULT NULL,
+date_column DATE DEFAULT NULL,
+KEY(date_column));
+INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
+ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
+EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
+DROP TABLE t1;
+#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
=== modified file 'sql/field.h'
--- a/sql/field.h 2010-10-06 17:20:18 +0000
+++ b/sql/field.h 2010-11-19 17:15:47 +0000
@@ -746,6 +746,17 @@ public:
uchar null_bit_arg, utype unireg_check_arg,
const char *field_name_arg, CHARSET_INFO *charset);
Item_result result_type () const { return STRING_RESULT; }
+ /*
+ match_collation_to_optimize_range() is to distinguish in
+ range optimizer (see opt_range.cc) between real string types:
+ CHAR, VARCHAR, TEXT
+ and the other string-alike types with result_type() == STRING_RESULT:
+ DATE, TIME, DATETIME, TIMESTAMP
+ We need it to decide whether to test if collation of the operation
+ matches collation of the field (needed only for real string types).
+ QQ: shouldn't DATE/TIME types have their own XXX_RESULT types eventually?
+ */
+ virtual bool match_collation_to_optimize_range() const=0;
uint decimals() const { return NOT_FIXED_DEC; }
int store(double nr);
int store(longlong nr, bool unsigned_val)=0;
@@ -1227,6 +1238,7 @@ public:
unireg_check_arg, field_name_arg, cs)
{}
enum_field_types type() const { return MYSQL_TYPE_NULL;}
+ bool match_collation_to_optimize_range() const { return FALSE; }
int store(const char *to, uint length, CHARSET_INFO *cs)
{ null[0]=1; return 0; }
int store(double nr) { null[0]=1; return 0; }
@@ -1256,6 +1268,7 @@ public:
Field_timestamp(bool maybe_null_arg, const char *field_name_arg,
CHARSET_INFO *cs);
enum_field_types type() const { return MYSQL_TYPE_TIMESTAMP;}
+ bool match_collation_to_optimize_range() const { return FALSE; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
enum Item_result cmp_type () const { return INT_RESULT; }
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1360,6 +1373,7 @@ public:
:Field_str((uchar*) 0, MAX_DATE_WIDTH, maybe_null_arg ? (uchar*) "": 0,0,
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
enum_field_types type() const { return MYSQL_TYPE_DATE;}
+ bool match_collation_to_optimize_range() const { return FALSE; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
enum Item_result cmp_type () const { return INT_RESULT; }
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1409,6 +1423,7 @@ public:
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
enum_field_types type() const { return MYSQL_TYPE_DATE;}
enum_field_types real_type() const { return MYSQL_TYPE_NEWDATE; }
+ bool match_collation_to_optimize_range() const { return FALSE; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_UINT24; }
enum Item_result cmp_type () const { return INT_RESULT; }
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1448,6 +1463,7 @@ public:
:Field_str((uchar*) 0,8, maybe_null_arg ? (uchar*) "": 0,0,
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
enum_field_types type() const { return MYSQL_TYPE_TIME;}
+ bool match_collation_to_optimize_range() const { return FALSE; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_INT24; }
enum Item_result cmp_type () const { return INT_RESULT; }
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1487,6 +1503,7 @@ public:
:Field_str((uchar*) 0, MAX_DATETIME_WIDTH, maybe_null_arg ? (uchar*) "": 0,0,
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
enum_field_types type() const { return MYSQL_TYPE_DATETIME;}
+ bool match_collation_to_optimize_range() const { return FALSE; }
#ifdef HAVE_LONG_LONG
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONGLONG; }
#endif
@@ -1555,6 +1572,7 @@ public:
orig_table->s->frm_version < FRM_VER_TRUE_VARCHAR ?
MYSQL_TYPE_VAR_STRING : MYSQL_TYPE_STRING);
}
+ bool match_collation_to_optimize_range() const { return TRUE; }
enum ha_base_keytype key_type() const
{ return binary() ? HA_KEYTYPE_BINARY : HA_KEYTYPE_TEXT; }
bool zero_pack() const { return 0; }
@@ -1635,6 +1653,7 @@ public:
}
enum_field_types type() const { return MYSQL_TYPE_VARCHAR; }
+ bool match_collation_to_optimize_range() const { return TRUE; }
enum ha_base_keytype key_type() const;
uint row_pack_length() { return field_length; }
bool zero_pack() const { return 0; }
@@ -1730,6 +1749,7 @@ public:
:Field_longstr((uchar*) 0, 0, (uchar*) "", 0, NONE, "temp", system_charset_info),
packlength(packlength_arg) {}
enum_field_types type() const { return MYSQL_TYPE_BLOB;}
+ bool match_collation_to_optimize_range() const { return TRUE; }
enum ha_base_keytype key_type() const
{ return binary() ? HA_KEYTYPE_VARBINARY2 : HA_KEYTYPE_VARTEXT2; }
int store(const char *to,uint length,CHARSET_INFO *charset);
@@ -1879,6 +1899,7 @@ public:
{ geom_type= geom_type_arg; }
enum ha_base_keytype key_type() const { return HA_KEYTYPE_VARBINARY2; }
enum_field_types type() const { return MYSQL_TYPE_GEOMETRY; }
+ bool match_collation_to_optimize_range() const { return FALSE; }
void sql_type(String &str) const;
int store(const char *to, uint length, CHARSET_INFO *charset);
int store(double nr);
@@ -1910,6 +1931,7 @@ public:
}
Field *new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type);
enum_field_types type() const { return MYSQL_TYPE_STRING; }
+ bool match_collation_to_optimize_range() const { return FALSE; }
enum Item_result cmp_type () const { return INT_RESULT; }
enum Item_result cast_to_int_type () const { return INT_RESULT; }
enum ha_base_keytype key_type() const;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-09-16 12:20:35 +0000
+++ b/sql/opt_range.cc 2010-11-19 17:15:47 +0000
@@ -5796,6 +5796,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
*/
if (field->result_type() == STRING_RESULT &&
+ ((Field_str*) field)->match_collation_to_optimize_range() &&
value->result_type() == STRING_RESULT &&
key_part->image_type == Field::itRAW &&
((Field_str*)field)->charset() != conf_func->compare_collation() &&
Attachment: [text/bzr-bundle] bzr/bar@mysql.com-20101119171547-w9l3nojmahgqabp2.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5-bugteam branch (bar:3138) Bug#58190 WL#2649 | Alexander Barkov | 19 Nov |
