List:Commits« Previous MessageNext Message »
From:Alexander Barkov Date:November 19 2010 7:09pm
Subject:bzr push into mysql-5.5-bugteam branch (bar:3137 to 3138) Bug#58190 WL#2649
View as plain text  
 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
 3137 Alexander Barkov	2010-11-19
      Bug#58175 xml functions read initialized bytes when conversions happen
      
      Problem:
      
       nr_of_decimals could read behind the end of the buffer
       in case of a non-null-terminated string, which caused
       valgring warnings.
      
      Fix:
      
        fixing nr_of_decimals not to read behind the "end" pointer.
      
      modified:
      
        @ mysql-test/r/xml.result
        @ mysql-test/t/xml.test
        @ sql/item.cc

    modified:
      mysql-test/r/xml.result
      mysql-test/t/xml.test
      sql/item.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 push into mysql-5.5-bugteam branch (bar:3137 to 3138) Bug#58190 WL#2649Alexander Barkov19 Nov