MySQL Bugs: #62307: Condition "TIME = '19:20'" does not use index in sjis

Bug #62307	Condition "TIME = '19:20'" does not use index in sjis
Submitted:	31 Aug 2011 1:54	Modified:	1 Sep 2011 0:40
Reporter:	Sadao Hiratsuka (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc, sjis

Description:
"TIME = '20:00'" does not use index in sjis.

MySQL 5.5 does not use index, but MySQL 5.1 uses.
So I think it is a serious performance regression bug.

How to repeat:
DROP TABLE IF EXISTS timetest;

CREATE TABLE timetest (
 id INT PRIMARY KEY,
 tm TIME,
 tx VARCHAR(100),
 KEY timetest_idx1 (tm)
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO timetest VALUES (1, '18:10', 'aaaaa');
INSERT INTO timetest VALUES (2, '19:20', 'bbbbb');
INSERT INTO timetest VALUES (3, '20:30', 'ccccc');
INSERT INTO timetest VALUES (4, '21:40', 'ddddd');
INSERT INTO timetest VALUES (5, '22:50', 'eeeee');

SET LOCAL character_set_connection = utf8;
EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
SHOW WARNINGS\G

SET LOCAL character_set_connection = sjis;
EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
SHOW WARNINGS\G

########## MySQL 5.5.15

mysql> EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   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 `scott`.`timetest`.`tx` AS `tx` from `scott`.`timetest` where (`scott`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

mysql> SET LOCAL character_set_connection = sjis;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ALL  | NULL          | NULL | NULL    | NULL |    5 |   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 `scott`.`timetest`.`tx` AS `tx` from `scott`.`timetest` where (convert(`scott`.`timetest`.`tm` using sjis) = '19:20')
1 row in set (0.00 sec)

########## MySQL 5.1.58

mysql> EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   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 `scott`.`timetest`.`tx` AS `tx` from `scott`.`timetest` where (`scott`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

mysql> SET LOCAL character_set_connection = sjis;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   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 `scott`.`timetest`.`tx` AS `tx` from `scott`.`timetest` where (`scott`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

Suggested fix:
From MySQL 5.5, cp932 has MY_CS_NONASCII flag in CHARSET_INFO structure.
And if character set has MY_CS_NONASCII flag,
condition like "TIME = '19:20'" is converted to following expression.

(convert(`scott`.`timetest`.`tm` using sjis) = '19:20')

I think it is good for ucs2,
but sjis is 99% ASCII compatible and can handle DATETIME string format
in the same way as other character sets such as latin1, utf8, cp932.

> From MySQL 5.5, cp932 has MY_CS_NONASCII flag in CHARSET_INFO structure.

From MySQL 5.5, sjis has MY_CS_NONASCII flag in CHARSET_INFO structure.
Sorry.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.17-log Source distribution

Copyright (c) 2000, 2011, 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 5.5 >use test
Database changed
mysql 5.5 >DROP TABLE IF EXISTS timetest;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE timetest (
    ->  id INT PRIMARY KEY,
    ->  tm TIME,
    ->  tx VARCHAR(100),
    ->  KEY timetest_idx1 (tm)
    -> ) ENGINE = InnoDB CHARACTER SET = utf8;
Query OK, 0 rows affected (0.23 sec)

mysql 5.5 >
mysql 5.5 >INSERT INTO timetest VALUES (1, '18:10', 'aaaaa');
Query OK, 1 row affected (0.12 sec)

mysql 5.5 >INSERT INTO timetest VALUES (2, '19:20', 'bbbbb');
Query OK, 1 row affected (0.08 sec)

mysql 5.5 >INSERT INTO timetest VALUES (3, '20:30', 'ccccc');
Query OK, 1 row affected (0.14 sec)

mysql 5.5 >INSERT INTO timetest VALUES (4, '21:40', 'ddddd');
Query OK, 1 row affected (0.15 sec)

mysql 5.5 >INSERT INTO timetest VALUES (5, '22:50', 'eeeee');
Query OK, 1 row affected (0.11 sec)

mysql 5.5 >
mysql 5.5 >SET LOCAL character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

mysql 5.5 >SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`timetest`.`tx` AS `tx` from `test`.`timetest` where (`test`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

mysql 5.5 >
mysql 5.5 >SET LOCAL character_set_connection = sjis;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.5 >SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`timetest`.`tx` AS `tx` from `test`.`timetest` where (convert(`test`.`timetest`.`tm` using sjis) = '19:20')
1 row in set (0.00 sec)

mysql 5.5 >exit
Bye

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.59-Win X64-log Source distribution

Copyright (c) 2000, 2011, 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 5.1 >use test
Database changed
mysql 5.1 >DROP TABLE IF EXISTS timetest;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql 5.1 >
mysql 5.1 >CREATE TABLE timetest (
    ->  id INT PRIMARY KEY,
    ->  tm TIME,
    ->  tx VARCHAR(100),
    ->  KEY timetest_idx1 (tm)
    -> ) ENGINE = InnoDB CHARACTER SET = utf8;
Query OK, 0 rows affected (0.17 sec)

mysql 5.1 >
mysql 5.1 >INSERT INTO timetest VALUES (1, '18:10', 'aaaaa');
Query OK, 1 row affected (0.11 sec)

mysql 5.1 >INSERT INTO timetest VALUES (2, '19:20', 'bbbbb');
Query OK, 1 row affected (0.07 sec)

mysql 5.1 >INSERT INTO timetest VALUES (3, '20:30', 'ccccc');
Query OK, 1 row affected (0.07 sec)

mysql 5.1 >INSERT INTO timetest VALUES (4, '21:40', 'ddddd');
Query OK, 1 row affected (0.07 sec)

mysql 5.1 >INSERT INTO timetest VALUES (5, '22:50', 'eeeee');
Query OK, 1 row affected (0.07 sec)

mysql 5.1 >
mysql 5.1 >SET LOCAL character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

mysql 5.1 >SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`timetest`.`tx` AS `tx` from `test`.`timetest` where (`test`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SET LOCAL character_set_connection = sjis;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >EXPLAIN EXTENDED SELECT tx FROM timetest WHERE tm = '19:20';
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | timetest | ref  | timetest_idx1 | timetest_idx1 | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.1 >SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`timetest`.`tx` AS `tx` from `test`.`timetest` where (`test`.`timetest`.`tm` = 192000)
1 row in set (0.00 sec)

mysql 5.1 >

Thank you for the bug report.

Thank you for your verification.
Additionally, This bug may cause invalid query results
for some TIME format.

mysql> SET LOCAL character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM timetest WHERE tm = '020:30';
+----+----------+-------+
| id | tm       | tx    |
+----+----------+-------+
|  3 | 20:30:00 | ccccc |
+----+----------+-------+
1 row in set (0.00 sec)

mysql> SET LOCAL character_set_connection = sjis;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM timetest WHERE tm = '020:30';
Empty set (0.00 sec)