MySQL Bugs: #58329: BETWEEN does not use indexes for date or datetime fields for UCS2+

Bug #58329	BETWEEN does not use indexes for date or datetime fields for UCS2+
Submitted:	19 Nov 2010 16:36	Modified:	19 Nov 2010 16:48
Reporter:	Alexander Barkov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Symptoms are the same with
Bug#58190 BETWEEN no longer uses indexes for date or datetime fields

but this is about UCS2, UTF16, UTF32.

Unlike Bug#58190, this report cannot be considered as regression,
because in 5.1 comparison between a date/time column
and a UCS2/UTF16/UTF32 constant did not work at all - it always returned FALSE.

With WL#2649 added in 5.5 it started to work, but does not use indexes.

Please don't close as a duplicate for Bug#58190.
We need a new report to separate "regression" and "not regression" parts.

The first part is easy to fix, and there is a patch for Bug#58190 already.
The second part needs more efforts.

How to repeat:
mysql> SET NAMES utf8, character_set_connection=ucs2;

Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY 
(date_column)) engine=myisam;

Query OK, 0 rows affected (0.01 sec)

INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01');

Query OK, 2 rows affected (0.00 sec)

mysql> 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    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (convert(`test`.`t1`.`date_column` using ucs2) = '\02\00\01\00\0-\00\09\0-\00\01') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Index is not used because date_column is converted to ucs2,
which is character set of the string literal.

Suggested fix:
It seems conversion should be the other way around:

select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column`
from `test`.`t1`
where (test`.`t1`.`date_column` = convert(_ucs2'\02\00\01\00\0-\00\09\0-\00\01' using latin1))

Verified with current mysql-5.5-security three on Mac OS X:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET NAMES utf8, character_set_connection=ucs2;
Query OK, 0 rows affected (0.01 sec)

mysql>  CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY 
    -> (date_column)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 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    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (convert(`test`.`t1`.`date_column` using ucs2) = '\02\00\01\00\0-\00\09\0-\00\01')
1 row in set (0.00 sec)

mysql> explain select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (`test`.`t1`.`date_column` = convert(_ucs2'\02\00\01\00\0-\00\09\0-\00\01' using
latin1));

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | date_column   | date_column | 4       | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)