Bug #79916 Comparison gives inconsistent result for time(1) column and explicit cast result

• Submitted: 11 Jan 2016 9:34
• Modified: 15 Jan 2016 4:25
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[11 Jan 2016 9:34] Su Dylan

Description:
Output:
=====
mysql> create table t1(c1 time(1));
Query OK, 0 rows affected (0.00 sec)

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

mysql> select c1, c1 in ('10:00:00.000001'), cast('10:00:00.0' as time(1)) in ('10:00:00.000001') from t1;
+------------+---------------------------+------------------------------------------------------+
| c1         | c1 in ('10:00:00.000001') | cast('10:00:00.0' as time(1)) in ('10:00:00.000001') |
+------------+---------------------------+------------------------------------------------------+
| 10:00:00.0 |                         1 |                                                    0 |
+------------+---------------------------+------------------------------------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
Consistent result is expeccted.

How to repeat:

drop table if exists t1;
create table t1(c1 time(1));
insert into t1 values('10:00:00.0');
select c1, c1 in ('10:00:00.000001'), cast('10:00:00.0' as time(1)) in ('10:00:00.000001') from t1;

Suggested fix:
Return consistent result.

[11 Jan 2016 9:43] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 are affected.

Thanks,
Umesh

[15 Jan 2016 4:25] Su Dylan

Not only in OR operator, the = operator also shows this inconsistency:

drop table if exists t1;
create table t1(c1 time);
insert into t1 values('10:00:00');
select c1, c1 = '10:00:00.000001', cast('10:00:00' as time) = '10:00:00.000001' from t1;

mysql> create table t1(c1 time);
Query OK, 0 rows affected (0.00 sec)

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

c1 = '10:00:00mysql> select c1, c1 = '10:00:00.000001', cast('10:00:00' as time) = '10:00:00.000001' from t1;
+----------+------------------------+----------------------------------------------+
| c1       | c1 = '10:00:00.000001' | cast('10:00:00' as time) = '10:00:00.000001' |
+----------+------------------------+----------------------------------------------+
| 10:00:00 |                      1 |                                            0 |
+----------+------------------------+----------------------------------------------+
1 row in set (0.00 sec)