| Bug #53433 | Charset conversion breaks interpretation of date string with no delimiters | ||
|---|---|---|---|
| Submitted: | 5 May 2010 12:00 | Modified: | 25 May 2010 12:23 |
| Reporter: | Takashi Ichii | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5.3, 5.5.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | charset, conversion, date, datetime, regression | ||
[5 May 2010 13:23]
MySQL Verification Team
Thank you for the bug report.
Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.
C:\DBS>55
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 1
Server version: 5.6.99-m4-Win X64 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `foo` (
-> `col` date NOT NULL
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.14 sec)
mysql 5.5 >INSERT INTO foo VALUES ('2010-05-05');
Query OK, 1 row affected (0.02 sec)
mysql 5.5 >INSERT INTO foo VALUES ('2010-05-06');
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >select * from foo;
+------------+
| col |
+------------+
| 2010-05-05 |
| 2010-05-06 |
+------------+
2 rows in set (0.02 sec)
mysql 5.5 >select * from foo where col = '2010-05-05';
+------------+
| col |
+------------+
| 2010-05-05 |
+------------+
1 row in set (0.00 sec)
mysql 5.5 >select * from foo where col = '20100505';
Empty set (0.00 sec)
mysql 5.5 >EXIT
Bye
C:\DBS>50
C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-Win X64-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > USE TEST
Database changed
mysql 5.0 > CREATE TABLE `foo` (
-> `col` date NOT NULL
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.12 sec)
mysql 5.0 > INSERT INTO foo VALUES ('2010-05-05');
Query OK, 1 row affected (0.02 sec)
mysql 5.0 > INSERT INTO foo VALUES ('2010-05-06');
Query OK, 1 row affected (0.00 sec)
mysql 5.0 > select * from foo;
+------------+
| col |
+------------+
| 2010-05-05 |
| 2010-05-06 |
+------------+
2 rows in set (0.02 sec)
mysql 5.0 > select * from foo where col = '2010-05-05';
+------------+
| col |
+------------+
| 2010-05-05 |
+------------+
1 row in set (0.03 sec)
mysql 5.0 > select * from foo where col = '20100505';
+------------+
| col |
+------------+
| 2010-05-05 |
+------------+
1 row in set (0.00 sec)
mysql 5.0 >
[5 May 2010 13:24]
MySQL Verification Team
C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-Win X64-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 >use test
Database changed
mysql 5.1 >CREATE TABLE `foo` (
-> `col` date NOT NULL
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.09 sec)
mysql 5.1 >INSERT INTO foo VALUES ('2010-05-05');
Query OK, 1 row affected (0.04 sec)
mysql 5.1 >INSERT INTO foo VALUES ('2010-05-06');
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >select * from foo;
+------------+
| col |
+------------+
| 2010-05-05 |
| 2010-05-06 |
+------------+
2 rows in set (0.02 sec)
mysql 5.1 >select * from foo where col = '2010-05-05';
+------------+
| col |
+------------+
| 2010-05-05 |
+------------+
1 row in set (0.00 sec)
mysql 5.1 >select * from foo where col = '20100505';
+------------+
| col |
+------------+
| 2010-05-05 |
+------------+
1 row in set (0.00 sec)
mysql 5.1 >
[5 May 2010 13:51]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=53434 marked as duplicate of this one.
[25 May 2010 9:19]
Guilhem Bichot
sorry, I wanted to work on BUG#53432, and clicked wrongly on this bug.
[25 May 2010 12:23]
Alexander Barkov
This bug is a diplicate for Bug#52849, which is fixed in mysql-trunk-bugfixing (tagged as 5.5.5-m3).
Description: Charset conversion breaks interpretation of date string with no delimiters. See How to repeat for detail. How to repeat: mysql> show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `col` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=ujis 1 row in set (0.00 sec) mysql> select * from foo; +------------+ | col | +------------+ | 2010-05-05 | | 2010-05-06 | +------------+ 2 rows in set (0.00 sec) mysql> select * from foo where col = '2010-05-05'; +------------+ | col | +------------+ | 2010-05-05 | +------------+ 1 row in set (0.00 sec) mysql> select * from foo where col = '20100505'; Empty set (0.00 sec) mysql> explain extended select * from foo where col = '20100505'; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | foo | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> show warnings; +-------+------+------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`foo`.`col` AS `col` from `test`.`foo` where (convert(`test`.`foo`.`col` using ujis) = '20100505') | +-------+------+------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from foo where col = _binary'20100505'; +------------+ | col | +------------+ | 2010-05-05 | +------------+ 1 row in set (0.00 sec) mysql> explain extended select * from foo where col = _binary'20100505'; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | foo | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`foo`.`col` AS `col` from `test`.`foo` where (`test`.`foo`.`col` = _binary'20100505') | +-------+------+-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: workaround: "where col = cast('20100505' as date)" also go well.