bzr commit into mysql-trunk-bugfixing branch (bar:3025) Bug#52849 WL#2649

[Alexander Barkov]

#At file:///home/bar/mysql-bzr/mysql-trunk-bugfixing/ based on revid:dao-gang.qu@stripped

 3025 Alexander Barkov	2010-04-28
      Bug#52849 [Com]: datetime index not work
      Problem: after introduction of "WL#2649 Number-to-string conversions"
      
      This query:
        SET NAMES cp850; -- Or any other non-latin1 ASCII-based character set
        SELECT * FROM t1
        WHERE datetime_column='2010-01-01 00:00:00'
      started to add extra character set conversion: 
        SELECT * FROM t1
        WHERE CONVERT(datetime_column USING cp850)='2010-01-01 00:00:00';
      
      so index on DATETIME column was not used anymore.
      
      Fix:
      avoid convertion of NUMERIC/DATETIME items
      (i.e. those with derivation DERIVATION_NUMERIC).

    modified:
      mysql-test/r/type_datetime.result
      mysql-test/t/type_datetime.test
      sql/item.cc
=== modified file 'mysql-test/r/type_datetime.result'
--- a/mysql-test/r/type_datetime.result	2010-03-24 15:03:44 +0000
+++ b/mysql-test/r/type_datetime.result	2010-04-28 12:04:20 +0000
@@ -638,3 +638,22 @@ CAST(CAST('2008-07-29T10:42:51.1234567' 
 Warnings:
 Warning	1292	Truncated incorrect datetime value: '2008-07-29T10:42:51.1234567'
 End of 5.1 tests
+#
+# Start of 5.5 tests
+#
+#
+# Bug#52849 datetime index not work
+#
+CREATE TABLE t1 (Id INT, AtTime DATETIME, KEY AtTime (AtTime));
+SET NAMES CP850;
+INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12');
+EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	AtTime	AtTime	9	const	1	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = '2010-02-22 18:40:07')
+DROP TABLE t1;
+SET NAMES latin1;
+#
+# End of 5.5 tests
+#

=== modified file 'mysql-test/t/type_datetime.test'
--- a/mysql-test/t/type_datetime.test	2009-02-13 18:07:03 +0000
+++ b/mysql-test/t/type_datetime.test	2010-04-28 12:04:20 +0000
@@ -446,3 +446,21 @@ SELECT CAST(CAST('00000002006-000008-000
 SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7));
 
 --echo End of 5.1 tests
+
+--echo #
+--echo # Start of 5.5 tests
+--echo #
+
+--echo #
+--echo # Bug#52849 datetime index not work
+--echo #
+CREATE TABLE t1 (Id INT, AtTime DATETIME, KEY AtTime (AtTime));
+SET NAMES CP850;
+INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12');
+EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07';
+DROP TABLE t1;
+SET NAMES latin1;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-04-19 08:27:46 +0000
+++ b/sql/item.cc	2010-04-28 12:04:20 +0000
@@ -1796,6 +1796,20 @@ bool agg_item_set_converter(DTCollation 
                                   &dummy_offset))
       continue;
 
+    /*
+      No needs to add converter if an "arg" is NUMERIC or DATETIME
+      value (which is pure ASCII) and at the same time target DTCollation
+      is ASCII-compatible. For example, no needs to rewrite:
+        SELECT * FROM t1 WHERE datetime_field = '2010-01-01';
+      to
+        SELECT * FROM t1 WHERE CONVERT(datetime_field USING cs) = '2010-01-01';
+    */
+    if ((*arg)->collation.derivation == DERIVATION_NUMERIC &&
+        (*arg)->collation.repertoire == MY_REPERTOIRE_ASCII &&
+        !((*arg)->collation.collation->state & MY_CS_NONASCII) &&
+        !(coll.collation->state & MY_CS_NONASCII))
+      continue;
+
     if (!(conv= (*arg)->safe_charset_converter(coll.collation)) &&
         ((*arg)->collation.repertoire == MY_REPERTOIRE_ASCII))
       conv= new Item_func_conv_charset(*arg, coll.collation, 1);


Attachment: [text/bzr-bundle] bzr/[e-mail]