MySQL Bugs: #74449: Incorrect datetime casting with charcter_set

Bug #74449	Incorrect datetime casting with charcter_set_connection=sjis
Submitted:	20 Oct 2014 9:39	Modified:	20 Oct 2014 10:44
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5.40, 5.6.21, 5.7.5, 5.6.22	OS:	Linux (CentOS 6.3)
Assigned to:		CPU Architecture:	Any
Tags:	cast, datetime, INDEX

Description:
When character_set_connection= sjis, MySQL can't use index on datetime type column correctly.
The query which can use range scan under character_set_connection=utf8, but the query executes whole index scan under character_set_connection=sjis.

This maybe relate these.

#68531: incorrect interpret single digits in time values http://bugs.mysql.com/bug.php?id=68531 

#68557: sjis changes in 5.5 break date parsing 
http://bugs.mysql.com/bug.php?id=68557 

How to repeat:
mysql> CREATE TABLE t1 (dt datetime not null, key(dt));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES ('2010-01-01'), ('2011-01-01'), ('2012-01-01'), ('2013-01-01'), ('2014-01-01');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SHOW SESSION VARIABLES LIKE 'character%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | utf8                              |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | utf8                              |
| character_set_system     | utf8                              |
| character_sets_dir       | /usr/mysql/5.6.21/share/charsets/ |
+--------------------------+-----------------------------------+
8 rows in set (0.15 sec)

mysql> explain SELECT * FROM t1 WHERE dt < '2012-12-31';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | dt            | dt   | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.24 sec)

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

mysql> explain SELECT * FROM t1 WHERE dt < '2012-12-31';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | dt   | 5       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Hello tsubasa,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.22

mysql> CREATE TABLE t1 (dt datetime not null, key(dt));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES ('2010-01-01'), ('2011-01-01'), ('2012-01-01'), ('2013-01-01'), ('2014-01-01');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SHOW SESSION VARIABLES LIKE 'character%';
+--------------------------+-------------------------------------------------------------+
| Variable_name            | Value                                                       |
+--------------------------+-------------------------------------------------------------+
| character_set_client     | utf8                                                        |
| character_set_connection | utf8                                                        |
| character_set_database   | latin1                                                      |
| character_set_filesystem | binary                                                      |
| character_set_results    | utf8                                                        |
| character_set_server     | latin1                                                      |
| character_set_system     | utf8                                                        |
| character_sets_dir       | /data/ushastry/server/mysql-advanced-5.6.22/share/charsets/ |
+--------------------------+-------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> explain SELECT * FROM t1 WHERE dt < '2012-12-31';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | dt            | dt   | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

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

mysql> explain SELECT * FROM t1 WHERE dt < '2012-12-31';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | dt   | 5       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Hello Umesh,
Thank you for verifying.

This bug brings our environment serious performance issue :(

Just information for people facing same problem,
workaround is using CAST function to avoid implicit cast.

mysql56> SELECT @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| sjis                       |
+----------------------------+
1 row in set (0.00 sec)

mysql56> explain SELECT * FROM t1 WHERE dt < '2012-12-31';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | dt   | 5       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql56> explain SELECT * FROM t1 WHERE dt < CAST('2012-12-31' AS datetime);
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | dt            | dt   | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

And I found this maybe duplicate of that one.

#62307: Condition "TIME = '19:20'" does not use index in sjis
http://bugs.mysql.com/bug.php?id=62307