bzr commit into mysql-next-mr-bugfixing branch (bar:3169) Bug#52849

[Alexander Barkov]

#At file:///home/bar/mysql-bzr/mysql-next-mr-bugfixing/ based on revid:horst.hunger@stripped

 3169 Alexander Barkov	2010-05-05 [merge]
      Merging bug#52849 from mysql-trunk-bugfixing

    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-05-05 09:28:37 +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-05-05 09:28:37 +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:29:52 +0000
+++ b/sql/item.cc	2010-05-05 09:34:28 +0000
@@ -1796,6 +1796,24 @@ 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';
+      
+      TODO: avoid conversion of any values with
+      repertoire ASCII and 7bit-ASCII-compatible,
+      not only numeric/datetime origin.
+    */
+    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]