Bug #117232 Unexpected results with YEAR data type comparisons in query predicates.

• Submitted: 18 Jan 5:48
• Modified: 20 Jan 5:26
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 8.4.1,9.1.0, 8.4.0, 8.4.3
• OS: Windows (windows 11)
• CPU Architecture: x86

[18 Jan 5:48] wang jack

Description:
From my understanding, the first query below should return two rows, but it is actually return one row.

mysql> SELECT t0.c0 FROM t0 WHERE ((t0.c0) IS NOT NULL) < (t0.c0); -- 1 row
+------+
| c0   |
+------+
| 2045 |
+------+
1 row in set (0.00 sec)

mysql> SELECT ((t0.c0) IS NOT NULL) < (t0.c0) FROM t0;             -- 2 rows: {1, 1}
+---------------------------------+
| ((t0.c0) IS NOT NULL) < (t0.c0) |
+---------------------------------+
|                               1 |
|                               1 |
+---------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 9.1.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 YEAR  PRIMARY KEY UNIQUE) ;
INSERT INTO t0(c0) VALUES('1937');
INSERT INTO t0(c0) VALUES('2045');

SELECT t0.c0 FROM t0 WHERE ((t0.c0) IS NOT NULL) < (t0.c0); -- 1 row
SELECT ((t0.c0) IS NOT NULL) < (t0.c0) FROM t0;             -- 2 rows: {1, 1}

[20 Jan 5:26] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.
Verified as described.

regards,
Umesh