List:Commits« Previous MessageNext Message »
From:Alexander Barkov Date:November 19 2010 2:49pm
Subject:bzr commit into mysql-5.5-bugteam branch (bar:3137) Bug#58190
View as plain text  
#At file:///home/bar/mysql-bzr/mysql-5.5-bugteam.b58190/ based on revid:bar@stripped

 3137 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.
      
      Note, the problem remains with UCS2, UTF16, UTF32 (index is not used).
      But this is not a regressions, as in 5.1 comparison between
      DATE/DATETIME and UCS2/UTF16/UTF32 did not work at all (always returned FALSE).
      
        @ 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 to distinguish between "real string" types
        like CHAR, VARCHAR, TEXT, and "almost string" types, like DATE, DATETIME.
      
        @ sql/opt_range.cc
        Checking collation only for "real string" types.

    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 14:35:02 +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 14:35:02 +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 14:35:02 +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 14:35:02 +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 14:35:02 +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 14:35:02 +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 14:35:02 +0000
@@ -243,6 +243,7 @@ public:
   virtual bool binary() const { return 1; }
   virtual bool zero_pack() const { return 1; }
   virtual enum ha_base_keytype key_type() const { return HA_KEYTYPE_BINARY; }
+  virtual bool match_collation_to_optimize_range() const { return FALSE; }
   virtual uint32 key_length() const { return pack_length(); }
   virtual enum_field_types type() const =0;
   virtual enum_field_types real_type() const { return type(); }
@@ -1557,6 +1558,7 @@ public:
   }
   enum ha_base_keytype key_type() const
     { return binary() ? HA_KEYTYPE_BINARY : HA_KEYTYPE_TEXT; }
+  bool match_collation_to_optimize_range() const { return TRUE; }
   bool zero_pack() const { return 0; }
   int reset(void)
   {
@@ -1636,6 +1638,7 @@ public:
 
   enum_field_types type() const { return MYSQL_TYPE_VARCHAR; }
   enum ha_base_keytype key_type() const;
+  bool match_collation_to_optimize_range() const { return TRUE; }
   uint row_pack_length() { return field_length; }
   bool zero_pack() const { return 0; }
   int  reset(void) { bzero(ptr,field_length+length_bytes); return 0; }
@@ -1732,6 +1735,7 @@ public:
   enum_field_types type() const { return MYSQL_TYPE_BLOB;}
   enum ha_base_keytype key_type() const
     { return binary() ? HA_KEYTYPE_VARBINARY2 : HA_KEYTYPE_VARTEXT2; }
+  bool match_collation_to_optimize_range() const { return TRUE; }
   int  store(const char *to,uint length,CHARSET_INFO *charset);
   int  store(double nr);
   int  store(longlong nr, bool unsigned_val);

=== 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 14:35:02 +0000
@@ -5795,7 +5795,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
        WHERE latin1_swedish_ci_colimn = BINARY 'a '
 
   */
-  if (field->result_type() == STRING_RESULT &&
+  if (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-20101119143502-x6l1x9uwxobbuofu.bundle
Thread
bzr commit into mysql-5.5-bugteam branch (bar:3137) Bug#58190Alexander Barkov19 Nov
  • Re: bzr commit into mysql-5.5-bugteam branch (bar:3137) Bug#58190Alexander Nozdrin19 Nov
    • Re: bzr commit into mysql-5.5-bugteam branch (bar:3137) Bug#58190Alexander Barkov19 Nov