MySQL Bugs: #79261: time compared with varchar, got different result for value in col or const

Bug #79261	time compared with varchar, got different result for value in col or const
Submitted:	13 Nov 2015 7:37	Modified:	25 Nov 2015 9:34
Reporter:	Su Dylan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> drop table t1; create table t1(a time(3), b varchar(100)) character set binary;
Query OK, 0 rows affected (0.01 sec)

nsert iQuery OK, 0 rows affected (0.00 sec)

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

mysql> select a, b, a = b, a = '12:00:00' from t1;
+--------------+----------+-------+----------------+
| a            | b        | a = b | a = '12:00:00' |
+--------------+----------+-------+----------------+
| 12:00:00.000 | 12:00:00 |     0 |              1 |
+--------------+----------+-------+----------------+
1 row in set (0.00 sec)

Problem:
========
True(1) is returned for the following two expr:
a = b, a = '12:00:00'

How to repeat:
drop table t1; create table t1(a time(3), b varchar(100)) character set binary;
insert into t1 values ('12:00:00', '12:00:00');
select a, b, a = b, a = '12:00:00' from t1;

Suggested fix:
True(1) is returned for the following two expr:
a = b, a = '12:00:00'

5.6 behaves as expected.

-- Peter
-- not an Oracle/MySQL person

It seems that "SELECT a = b FROM table" compares both a and b as chars internally:

SELECT((SELECT CAST((SELECT a FROM t10) AS CHAR))=(SELECT CAST((SELECT b FROM t10) AS CHAR))); -- 0
SELECT((SELECT CAST((SELECT a FROM t10) AS DATETIME))=(SELECT CAST((SELECT b FROM t10) AS DATETIME))); -- 1
SELECT((SELECT CAST((SELECT a FROM t10) AS CHAR))=(SELECT CAST((SELECT b FROM t10) AS DATETIME))); -- 1
SELECT((SELECT CAST((SELECT a FROM t10) AS DATETIME))=(SELECT CAST((SELECT b FROM t10) AS CHAR))) -- 1

Is there any progress on analysis of this issue?

Looks !bug for me please check below the time(3) and b column insert without the seconds precision:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.10 Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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.7 > use test
Database changed
mysql 5.7 > drop table t1; create table t1(a time(3), b varchar(100)) character set binary;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into t1 values ('12:00:00', '12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > select a, b, a = b, a = '12:00:00' from t1;
+--------------+----------+-------+----------------+
| a            | b        | a = b | a = '12:00:00' |
+--------------+----------+-------+----------------+
| 12:00:00.000 | 12:00:00 |     0 |              1 |
+--------------+----------+-------+----------------+
1 row in set (0.00 sec)

mysql 5.7 > select length(a), length(b) from t1;
+-----------+-----------+
| length(a) | length(b) |
+-----------+-----------+
|        12 |         8 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > drop table t1; create table t1(a time, b varchar(100)) character set binary;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into t1 values ('12:00:00', '12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > select a, b, a = b, a = '12:00:00' from t1;
+----------+----------+-------+----------------+
| a        | b        | a = b | a = '12:00:00' |
+----------+----------+-------+----------------+
| 12:00:00 | 12:00:00 |     1 |              1 |
+----------+----------+-------+----------------+
1 row in set (0.00 sec)

mysql 5.7 > select length(a), length(b) from t1;
+-----------+-----------+
| length(a) | length(b) |
+-----------+-----------+
|         8 |         8 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql 5.7 > drop table t1; create table t1(a time(3), b varchar(100)) character set binary;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into t1 values ('12:00:00', '12:00:00.000');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > select a, b, a = b, a = '12:00:00' from t1;
+--------------+--------------+-------+----------------+
| a            | b            | a = b | a = '12:00:00' |
+--------------+--------------+-------+----------------+
| 12:00:00.000 | 12:00:00.000 |     1 |              1 |
+--------------+--------------+-------+----------------+
1 row in set (0.00 sec)

mysql 5.7 > select length(a), length(b) from t1;
+-----------+-----------+
| length(a) | length(b) |
+-----------+-----------+
|        12 |        12 |
+-----------+-----------+
1 row in set (0.00 sec)

Actually 12:00:00.000 != 12:00:00

Hi Miguel,

Thanks for your reply.

Since column b with type varchar has the same string as const '12:00:00', it is normal for a user to expect the same result for:
a=b and a='12:00:00'.

I'm fully agree with Miguel, this is an expected behavior and not a bug.

-- with string comparison it matches, with column comparison it fails

root@localhost:test>select a, b, a = b, a = '12:00:00' from t1;
+--------------+----------+-------+----------------+
| a            | b        | a = b | a = '12:00:00' |
+--------------+----------+-------+----------------+
| 12:00:00.000 | 12:00:00 |     0 |              1 |
+--------------+----------+-------+----------------+
1 row in set (0.00 sec)

- works when b is specified as time
root@localhost:test>select a, b, a = cast(b as time), a = '12:00:00' from t1;
+--------------+----------+---------------------+----------------+
| a            | b        | a = cast(b as time) | a = '12:00:00' |
+--------------+----------+---------------------+----------------+
| 12:00:00.000 | 12:00:00 |                   1 |              1 |
+--------------+----------+---------------------+----------------+
1 row in set (0.00 sec)

-- also, with strcmp

root@localhost:test>select a, b, strcmp(a,b), a = '12:00:00' from t1;
+--------------+----------+-------------+----------------+
| a            | b        | strcmp(a,b) | a = '12:00:00' |
+--------------+----------+-------------+----------------+
| 12:00:00.000 | 12:00:00 |           1 |              1 |
+--------------+----------+-------------+----------------+
1 row in set (0.00 sec)

Explained here for the behavior:

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

Hi Umesh and Miguel,

Thank you both.
The documentation Umesh posted explained the situation quite clearly.