Bug #79409 compare type for datetime column and varchar column is inconsistent with doc
Submitted: 25 Nov 2015 10:45 Modified: 30 Nov 2015 8:08
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.7.9, 5.5.48, 5.6.29 OS:Any
Assigned to: CPU Architecture:Any

[25 Nov 2015 10:45] Su Dylan
Description:
Output:
=======
mysql> drop table t1; create table t1(a datetime, b varchar(30));
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('1989-11-13 05:21:55', '891113052155');
Query OK, 1 row affected (0.00 sec)

mysql> select a, b, a = b, a = '891113052155', a < '891113052160' from t1 order by a;
+---------------------+--------------+-------+--------------------+--------------------+
| a                   | b            | a = b | a = '891113052155' | a < '891113052160' |
+---------------------+--------------+-------+--------------------+--------------------+
| 1989-11-13 05:21:55 | 891113052155 |     1 |                  1 |                  1 |
+---------------------+--------------+-------+--------------------+--------------------+
1 row in set, 1 warning (0.00 sec)

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

Problem:
========
In the result, a('1989-11-13 05:21:55') = b('891113052155 ') returns 1, which tells that the compare type for datetime column and varchar column is datetime.
This result is inconsistent with the documentation:

When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the <, <=, =, >=, >, or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparison (and also for a bit more “relaxed” string checking). However, this conversion is subject to the following exceptions:

- When you compare two columns

https://dev.mysql.com/doc/refman/5.7/en/using-date.html

How to repeat:

drop table t1; create table t1(a datetime, b varchar(30));
insert into t1 values ('1989-11-13 05:21:55', '891113052155');
select a, b, a = b, a = '891113052155', a < '891113052160' from t1 order by a;

Suggested fix:
Compare type for "a('1989-11-13 05:21:55') = b('891113052155 ')" should be consistent with documentation.
[30 Nov 2015 8:08] Umesh Shastry
Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh