Skip to content

Commit 18a8755

Browse files
author
Alexander Barkov
committed
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). --BZR-- revision-id: [email protected] property-branch-nick: mysql-5.5-bugteam.b58190 testament3-sha1: b627e5036372fffc52614e4cd434c8eeeb25be14
1 parent 3550b6d commit 18a8755

File tree

8 files changed

+133
-0
lines changed

8 files changed

+133
-0
lines changed

‎mysql-test/include/ctype_numconv.inc‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1722,6 +1722,21 @@ DROP TABLE t1;
17221722
--echo #
17231723

17241724

1725+
--echo #
1726+
--echo # Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
1727+
--echo #
1728+
SELECT @@collation_connection;
1729+
CREATE TABLE t1 (
1730+
id INT(11) DEFAULT NULL,
1731+
date_column DATE DEFAULT NULL,
1732+
KEY(date_column));
1733+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
1734+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
1735+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
1736+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
1737+
DROP TABLE t1;
1738+
1739+
17251740
--echo #
17261741
--echo # Bug#52159 returning time type from function and empty left join causes debug assertion
17271742
--echo #

‎mysql-test/r/ctype_binary.result‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2748,6 +2748,25 @@ DROP TABLE t1;
27482748
# End of Bug#54916
27492749
#
27502750
#
2751+
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
2752+
#
2753+
SELECT @@collation_connection;
2754+
@@collation_connection
2755+
binary
2756+
CREATE TABLE t1 (
2757+
id INT(11) DEFAULT NULL,
2758+
date_column DATE DEFAULT NULL,
2759+
KEY(date_column));
2760+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
2761+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
2762+
id select_type table type possible_keys key key_len ref rows Extra
2763+
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
2764+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
2765+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
2766+
id select_type table type possible_keys key key_len ref rows Extra
2767+
1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
2768+
DROP TABLE t1;
2769+
#
27512770
# Bug#52159 returning time type from function and empty left join causes debug assertion
27522771
#
27532772
CREATE FUNCTION f1() RETURNS TIME RETURN 1;

‎mysql-test/r/ctype_cp1251.result‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2830,6 +2830,25 @@ DROP TABLE t1;
28302830
# End of Bug#54916
28312831
#
28322832
#
2833+
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
2834+
#
2835+
SELECT @@collation_connection;
2836+
@@collation_connection
2837+
cp1251_general_ci
2838+
CREATE TABLE t1 (
2839+
id INT(11) DEFAULT NULL,
2840+
date_column DATE DEFAULT NULL,
2841+
KEY(date_column));
2842+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
2843+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
2844+
id select_type table type possible_keys key key_len ref rows Extra
2845+
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
2846+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
2847+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
2848+
id select_type table type possible_keys key key_len ref rows Extra
2849+
1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
2850+
DROP TABLE t1;
2851+
#
28332852
# Bug#52159 returning time type from function and empty left join causes debug assertion
28342853
#
28352854
CREATE FUNCTION f1() RETURNS TIME RETURN 1;

‎mysql-test/r/ctype_latin1.result‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3158,6 +3158,25 @@ DROP TABLE t1;
31583158
# End of Bug#54916
31593159
#
31603160
#
3161+
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
3162+
#
3163+
SELECT @@collation_connection;
3164+
@@collation_connection
3165+
latin1_swedish_ci
3166+
CREATE TABLE t1 (
3167+
id INT(11) DEFAULT NULL,
3168+
date_column DATE DEFAULT NULL,
3169+
KEY(date_column));
3170+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
3171+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
3172+
id select_type table type possible_keys key key_len ref rows Extra
3173+
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
3174+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
3175+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
3176+
id select_type table type possible_keys key key_len ref rows Extra
3177+
1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
3178+
DROP TABLE t1;
3179+
#
31613180
# Bug#52159 returning time type from function and empty left join causes debug assertion
31623181
#
31633182
CREATE FUNCTION f1() RETURNS TIME RETURN 1;

‎mysql-test/r/ctype_ucs.result‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3990,6 +3990,25 @@ DROP TABLE t1;
39903990
# End of Bug#54916
39913991
#
39923992
#
3993+
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
3994+
#
3995+
SELECT @@collation_connection;
3996+
@@collation_connection
3997+
ucs2_general_ci
3998+
CREATE TABLE t1 (
3999+
id INT(11) DEFAULT NULL,
4000+
date_column DATE DEFAULT NULL,
4001+
KEY(date_column));
4002+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
4003+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
4004+
id select_type table type possible_keys key key_len ref rows Extra
4005+
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
4006+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
4007+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
4008+
id select_type table type possible_keys key key_len ref rows Extra
4009+
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
4010+
DROP TABLE t1;
4011+
#
39934012
# Bug#52159 returning time type from function and empty left join causes debug assertion
39944013
#
39954014
CREATE FUNCTION f1() RETURNS TIME RETURN 1;

‎mysql-test/r/ctype_utf8.result‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4848,6 +4848,25 @@ DROP TABLE t1;
48484848
# End of Bug#54916
48494849
#
48504850
#
4851+
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
4852+
#
4853+
SELECT @@collation_connection;
4854+
@@collation_connection
4855+
utf8_general_ci
4856+
CREATE TABLE t1 (
4857+
id INT(11) DEFAULT NULL,
4858+
date_column DATE DEFAULT NULL,
4859+
KEY(date_column));
4860+
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
4861+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
4862+
id select_type table type possible_keys key key_len ref rows Extra
4863+
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using where
4864+
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
4865+
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
4866+
id select_type table type possible_keys key key_len ref rows Extra
4867+
1 SIMPLE t1 range date_column date_column 9 NULL 1 Using where
4868+
DROP TABLE t1;
4869+
#
48514870
# Bug#52159 returning time type from function and empty left join causes debug assertion
48524871
#
48534872
CREATE FUNCTION f1() RETURNS TIME RETURN 1;

‎sql/field.h‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -746,6 +746,17 @@ class Field_str :public Field {
746746
uchar null_bit_arg, utype unireg_check_arg,
747747
const char *field_name_arg, CHARSET_INFO *charset);
748748
Item_result result_type () const { return STRING_RESULT; }
749+
/*
750+
match_collation_to_optimize_range() is to distinguish in
751+
range optimizer (see opt_range.cc) between real string types:
752+
CHAR, VARCHAR, TEXT
753+
and the other string-alike types with result_type() == STRING_RESULT:
754+
DATE, TIME, DATETIME, TIMESTAMP
755+
We need it to decide whether to test if collation of the operation
756+
matches collation of the field (needed only for real string types).
757+
QQ: shouldn't DATE/TIME types have their own XXX_RESULT types eventually?
758+
*/
759+
virtual bool match_collation_to_optimize_range() const=0;
749760
uint decimals() const { return NOT_FIXED_DEC; }
750761
int store(double nr);
751762
int store(longlong nr, bool unsigned_val)=0;
@@ -1227,6 +1238,7 @@ class Field_null :public Field_str {
12271238
unireg_check_arg, field_name_arg, cs)
12281239
{}
12291240
enum_field_types type() const { return MYSQL_TYPE_NULL;}
1241+
bool match_collation_to_optimize_range() const { return FALSE; }
12301242
int store(const char *to, uint length, CHARSET_INFO *cs)
12311243
{ null[0]=1; return 0; }
12321244
int store(double nr) { null[0]=1; return 0; }
@@ -1256,6 +1268,7 @@ class Field_timestamp :public Field_str {
12561268
Field_timestamp(bool maybe_null_arg, const char *field_name_arg,
12571269
CHARSET_INFO *cs);
12581270
enum_field_types type() const { return MYSQL_TYPE_TIMESTAMP;}
1271+
bool match_collation_to_optimize_range() const { return FALSE; }
12591272
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
12601273
enum Item_result cmp_type () const { return INT_RESULT; }
12611274
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1360,6 +1373,7 @@ class Field_date :public Field_str {
13601373
:Field_str((uchar*) 0, MAX_DATE_WIDTH, maybe_null_arg ? (uchar*) "": 0,0,
13611374
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
13621375
enum_field_types type() const { return MYSQL_TYPE_DATE;}
1376+
bool match_collation_to_optimize_range() const { return FALSE; }
13631377
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; }
13641378
enum Item_result cmp_type () const { return INT_RESULT; }
13651379
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1409,6 +1423,7 @@ class Field_newdate :public Field_str {
14091423
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
14101424
enum_field_types type() const { return MYSQL_TYPE_DATE;}
14111425
enum_field_types real_type() const { return MYSQL_TYPE_NEWDATE; }
1426+
bool match_collation_to_optimize_range() const { return FALSE; }
14121427
enum ha_base_keytype key_type() const { return HA_KEYTYPE_UINT24; }
14131428
enum Item_result cmp_type () const { return INT_RESULT; }
14141429
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1448,6 +1463,7 @@ class Field_time :public Field_str {
14481463
:Field_str((uchar*) 0,8, maybe_null_arg ? (uchar*) "": 0,0,
14491464
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
14501465
enum_field_types type() const { return MYSQL_TYPE_TIME;}
1466+
bool match_collation_to_optimize_range() const { return FALSE; }
14511467
enum ha_base_keytype key_type() const { return HA_KEYTYPE_INT24; }
14521468
enum Item_result cmp_type () const { return INT_RESULT; }
14531469
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
@@ -1487,6 +1503,7 @@ class Field_datetime :public Field_str {
14871503
:Field_str((uchar*) 0, MAX_DATETIME_WIDTH, maybe_null_arg ? (uchar*) "": 0,0,
14881504
NONE, field_name_arg, cs) { flags|= BINARY_FLAG; }
14891505
enum_field_types type() const { return MYSQL_TYPE_DATETIME;}
1506+
bool match_collation_to_optimize_range() const { return FALSE; }
14901507
#ifdef HAVE_LONG_LONG
14911508
enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONGLONG; }
14921509
#endif
@@ -1555,6 +1572,7 @@ class Field_string :public Field_longstr {
15551572
orig_table->s->frm_version < FRM_VER_TRUE_VARCHAR ?
15561573
MYSQL_TYPE_VAR_STRING : MYSQL_TYPE_STRING);
15571574
}
1575+
bool match_collation_to_optimize_range() const { return TRUE; }
15581576
enum ha_base_keytype key_type() const
15591577
{ return binary() ? HA_KEYTYPE_BINARY : HA_KEYTYPE_TEXT; }
15601578
bool zero_pack() const { return 0; }
@@ -1635,6 +1653,7 @@ class Field_varstring :public Field_longstr {
16351653
}
16361654

16371655
enum_field_types type() const { return MYSQL_TYPE_VARCHAR; }
1656+
bool match_collation_to_optimize_range() const { return TRUE; }
16381657
enum ha_base_keytype key_type() const;
16391658
uint row_pack_length() { return field_length; }
16401659
bool zero_pack() const { return 0; }
@@ -1730,6 +1749,7 @@ class Field_blob :public Field_longstr {
17301749
:Field_longstr((uchar*) 0, 0, (uchar*) "", 0, NONE, "temp", system_charset_info),
17311750
packlength(packlength_arg) {}
17321751
enum_field_types type() const { return MYSQL_TYPE_BLOB;}
1752+
bool match_collation_to_optimize_range() const { return TRUE; }
17331753
enum ha_base_keytype key_type() const
17341754
{ return binary() ? HA_KEYTYPE_VARBINARY2 : HA_KEYTYPE_VARTEXT2; }
17351755
int store(const char *to,uint length,CHARSET_INFO *charset);
@@ -1879,6 +1899,7 @@ class Field_geom :public Field_blob {
18791899
{ geom_type= geom_type_arg; }
18801900
enum ha_base_keytype key_type() const { return HA_KEYTYPE_VARBINARY2; }
18811901
enum_field_types type() const { return MYSQL_TYPE_GEOMETRY; }
1902+
bool match_collation_to_optimize_range() const { return FALSE; }
18821903
void sql_type(String &str) const;
18831904
int store(const char *to, uint length, CHARSET_INFO *charset);
18841905
int store(double nr);
@@ -1910,6 +1931,7 @@ class Field_enum :public Field_str {
19101931
}
19111932
Field *new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type);
19121933
enum_field_types type() const { return MYSQL_TYPE_STRING; }
1934+
bool match_collation_to_optimize_range() const { return FALSE; }
19131935
enum Item_result cmp_type () const { return INT_RESULT; }
19141936
enum Item_result cast_to_int_type () const { return INT_RESULT; }
19151937
enum ha_base_keytype key_type() const;

‎sql/opt_range.cc‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5796,6 +5796,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
57965796
57975797
*/
57985798
if (field->result_type() == STRING_RESULT &&
5799+
((Field_str*) field)->match_collation_to_optimize_range() &&
57995800
value->result_type() == STRING_RESULT &&
58005801
key_part->image_type == Field::itRAW &&
58015802
((Field_str*)field)->charset() != conf_func->compare_collation() &&

0 commit comments

Comments
 (0)