List:Commits« Previous MessageNext Message »
From:Alexander Barkov Date:March 18 2011 12:46pm
Subject:bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329
Bug#11765369
View as plain text  
#At file:///home/bar/mysql-bzr/mysql-trunk.b58329/ based on revid:alexander.nozdrin@stripped

 3302 Alexander Barkov	2011-03-18
      Bug#11765369 - Bug#58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+ 
      Problem:
        WHERE date_column = 'ucs2 literal'
      was rewritten to
        WHERE CONVERT(date_column USING ucs2) = 'ucs2 literal'
      so index on date_column did not work any more.
      
      Fix: rewrite to
        WHERE date_column = CONVERT('ucs2 literal' USING latin1)
      
      modified:
        @ mysql-test/r/ctype_ucs.result
        @ mysql-test/t/ctype_ucs.test
        Adding tests
      
        @ sql/item_cmpfunc.cc
        Adding special character set aggregation rule for DATE/TIME

    modified:
      mysql-test/r/ctype_ucs.result
      mysql-test/t/ctype_ucs.test
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result	2011-03-04 14:59:32 +0000
+++ b/mysql-test/r/ctype_ucs.result	2011-03-18 12:43:19 +0000
@@ -4684,5 +4684,18 @@ DO CAST(CONVERT('' USING ucs2) AS UNSIGN
 Warnings:
 Warning	1292	Truncated incorrect INTEGER value: ''
 #
+# Bug#11765369 - 58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+
+#
+SET NAMES utf8, character_set_connection=ucs2;
+CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY (date_column)) engine=myisam;
+INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01');
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE date_column= '2010-09-01';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	date_column	date_column	4	const	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (`test`.`t1`.`date_column` = '2010-09-01')
+DROP TABLE t1;
+SET NAMES latin1;
+#
 # End of 5.6 tests
 #

=== modified file 'mysql-test/t/ctype_ucs.test'
--- a/mysql-test/t/ctype_ucs.test	2011-03-04 14:59:32 +0000
+++ b/mysql-test/t/ctype_ucs.test	2011-03-18 12:43:19 +0000
@@ -779,5 +779,15 @@ DO IFNULL(CHAR(NULL USING ucs2), '');
 DO CAST(CONVERT('' USING ucs2) AS UNSIGNED);
 
 --echo #
+--echo # Bug#11765369 - 58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+
+--echo #
+SET NAMES utf8, character_set_connection=ucs2;
+CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY (date_column)) engine=myisam;
+INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01');
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE date_column= '2010-09-01';
+DROP TABLE t1;
+SET NAMES latin1;
+
+--echo #
 --echo # End of 5.6 tests
 --echo #

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-03-17 11:33:17 +0000
+++ b/sql/item_cmpfunc.cc	2011-03-18 12:43:19 +0000
@@ -484,10 +484,31 @@ void Item_bool_func2::fix_length_and_dec
   
   DTCollation coll;
   if (args[0]->result_type() == STRING_RESULT &&
-      args[1]->result_type() == STRING_RESULT &&
-      agg_arg_charsets_for_comparison(coll, args, 2))
-    return;
-    
+      args[1]->result_type() == STRING_RESULT)
+  {
+    if (args[0]->is_datetime() || args[1]->is_datetime())
+    {
+      /*
+        For DATE/TIME we always convert to my_charset_numeric (latin1),
+        to make this:
+          WHERE date_column = 'ucs2 literal';
+
+        rewrite to
+          WHERE date_column = CONVERT('ucs2 literal' USING latin1);
+
+        instead of 
+          WHERE CONVERT(date_column USING ucs2) = 'ucs2 literal';
+
+        This makes it possible to use index on date_column for optimization.
+      */
+      coll.set(&my_charset_numeric);
+      if (agg_item_set_converter(coll, func_name(), args, 2, 0, 1))
+        return;
+    }
+    else if (agg_arg_charsets_for_comparison(coll, args, 2))
+      return;
+  }
+
   args[0]->cmp_context= args[1]->cmp_context=
     item_cmp_type(args[0]->result_type(), args[1]->result_type());
   // Make a special case of compare with fields to get nicer DATE comparisons


Attachment: [text/bzr-bundle] bzr/alexander.barkov@oracle.com-20110318124319-04nv7f0pr7hoq043.bundle
Thread
bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329Bug#11765369Alexander Barkov18 Mar
  • Re: bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329Bug#11765369Guilhem Bichot1 Apr
    • Re: bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329Bug#11765369Alexander Barkov1 Apr
      • Re: bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329Bug#11765369Guilhem Bichot4 Apr