MySQL Bugs: #54588: Index on datetime column does not support iso8601.

Bug #54588	Index on datetime column does not support iso8601.
Submitted:	17 Jun 2010 15:51	Modified:	11 Apr 2018 12:17
Reporter:	Jason Frey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.47, 5.1.48	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
iso8601 is supported in queries against datetime columns, unless that column ends up being a part of an index (single or composite).

How to repeat:
Create a table with a datetime column, and insert a row with the datetime value set to '2010-01-01 12:00:00'.

The following queries both return the row:
SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00'
SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z'

Now, create an index on the datetime column.

The following query returns the row:
SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00'
However, this query does not:
SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z'

On the latter, EXPLAIN says "Impossible WHERE noticed after reading const tables"

Please, check with a newer version, 5.1.48, and inform about the results. Works for me on current 5.1.49 from bzr on Mac:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.49-debug 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> create table t1(dt datetime);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into t1 values ('2010-01-01 12:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2010-01-01T12:00:00Z' for column 'dt' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect datetime value: '2010-01-01T12:00:00Z'
2 rows in set (0.00 sec)

mysql> alter table t1 add key(dt);
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

Still happens on 5.1.48...

C:\>mysql -u root -p test
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.48-community MySQL Community Server (GPL)

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> create table t1(dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('2010-01-01 12:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> alter table t1 add key(dt);
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
Empty set, 2 warnings (0.00 sec)

Still do not see it on Mac:

valeriy-kravchuks-macbook-pro:mysql-5.1.48-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48 MySQL Community Server (GPL)

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> create table t1(dt datetime);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values ('2010-01-01 12:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01 12:00:00';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> alter table t1 add key(dt);
Query OK, 1 row affected (0.54 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

Looks like Windows-specific bug.

Not repeatable on Linux with today source too:

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.49              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | slackware-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>

I tried again on a Mac with version 5.1.46 (the only version I had on hand).  I also tried on Linux with 5.0.77 (far back, I know).   I had to adapt the test a little to make it fail.  It seems that if the EXPLAIN shows a type of "system" (only 1 row), then it would work.  I added a second row with a different date value, and it then failed.

insert into t1 values ('2010-01-01 12:00:00'), ('2010-01-01 12:00:01');
SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';

Indeed, you are right. If we continue test case with:

mysql> insert into t1 values ('2010-01-01 12:00:01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
Empty set, 2 warnings (0.00 sec)

mysql> alter table t1 drop key dt;
Query OK, 2 rows affected (0.41 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE dt = '2010-01-01T12:00:00Z';
+---------------------+
| dt                  |
+---------------------+
| 2010-01-01 12:00:00 |
+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.48    |
+-----------+
1 row in set (0.00 sec)

the bug becomes obvious.

still occurs on Windows version 5.1.53...  Any fix coming up? This actually means rewriting our main application for mysql... I really dont like how long this bug takes to get resolved

You can fix this on windows by creating a trigger to go around the 'Z' (for ZULU time) at the end of the string given to you... 

this is my "dirty-fix":
delimiter $$
CREATE TRIGGER fix_iso8601_t1i  BEFORE UPDATE ON t1 FOR EACH ROW BEGIN SET NEW.dt=TRIM(TRAILING 'Z' FROM NEW.dt); END$$

CREATE TRIGGER fix_iso8601_t1u  BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET NEW.dt=TRIM(TRAILING 'Z' FROM NEW.dt); END$$

note you must also do: 
set @@session.sql_mode = 'anything you already have there except 'STRICT_TRANS_TABLES' 
=> you should alter @@global.sql_mode later as well

- there is the problem in that in setting the @@session (or @@global) variables - you force the db to disable certain checks for compatibility (you basically tell it - "if there is something that does not fit the data type, it doesnt matter go on" - you will fix it in your trigger)

Posted by developer:

[11 Feb 2018 4:46] Roy Lyseng

This is not a bug.
MySQL does not handle the 'Z' suffix in the timestamp literal.
Even if it did, it would treat the literal as being in UTC, which is
not in the session's time zone, as the inserted value is.