#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
