Bug #81533 Get different results when comparing YEAR(4) with 2-digit const and 2-digit col

• Submitted: 23 May 2016 4:25
• Modified: 23 May 2016 7:17
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.5.49, 5.6.30, 5.7.12
• OS: Any
• CPU Architecture: Any

[23 May 2016 4:25] Su Dylan

Description:
Output:
===
mysql> create table t1(c1 year(4), c2 bigint);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values('1901', 32);
Query OK, 1 row affected (0.00 sec)

mysql> select c1<=32, c1<=c2 from t1;
+--------+--------+
| c1<=32 | c1<=c2 |
+--------+--------+
|      1 |      0 |
+--------+--------+
1 row in set (0.00 sec)

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

Problem:
===
For const bigint and column bigint values, different results are returned. 
1 is expected for both exprs.

How to repeat:
create table t1(c1 year(4), c2 bigint); 
insert into t1 values('1901', 32);
select c1<=32, c1<=c2 from t1;

Suggested fix:
1 is returned for both exprs.

[23 May 2016 7:17] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.5.49, 5.6.30 and 5.7.12 builds.

Thanks,
Umesh