Bug #79936 "c1(NULL) > 1 && c2('00:00:00.0') = '00:00:00.000001'" returns NULL, not 0

• Submitted: 12 Jan 2016 15:34
• Modified: 13 Jan 2016 7:19
• 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

[12 Jan 2016 15:34] Su Dylan

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

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

mysql> select c1, c2, c1 > 1 && c2 = '00:00:00.000001', null > 1 && '00:00:00.0' = '00:00:00.000001' from t1;
+------+------------+----------------------------------+----------------------------------------------+
| c1   | c2         | c1 > 1 && c2 = '00:00:00.000001' | null > 1 && '00:00:00.0' = '00:00:00.000001' |
+------+------------+----------------------------------+----------------------------------------------+
| NULL | 00:00:00.0 |                             NULL |                                            0 |
+------+------------+----------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
"c1 > 1 && c2 = '00:00:00.000001'" is expected to return 0, not NULL.

How to repeat:

drop table if exists t1;
create table t1(c1 int, c2 time(1));
insert into t1 values(null, '00:00:00.0');
select c1, c2, c1 > 1 && c2 = '00:00:00.000001', null > 1 && '00:00:00.0' = '00:00:00.000001' from t1;

Suggested fix:
"c1 > 1 && c2 = '00:00:00.000001'" returns 0.

[13 Jan 2016 7:19] 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