MySQL Bugs: #121217: YEAR column is temporalized while YEAR numeric constants are not: opposite answers per context

Bug #121217	YEAR column is temporalized while YEAR numeric constants are not: opposite answers per context
Submitted:	3 Sep 7:30	Modified:	3 Sep 11:55
Reporter:	Chunling Qin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	9.7.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> SELECT (2024) < TIME'00:00:01';    -- 0 (numeric 2024 < 1 → 0)
+-------------------------+
| (2024) < TIME'00:00:01' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT (2024) = DATE'2024-01-01';  -- 0 (numerically unequal)
+---------------------------+
| (2024) = DATE'2024-01-01' |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

Mechanism: CAST(c AS DATE) confirms the YEAR column is DATE-ized to the year's first day (2024 → 2024-01-01), so c = DATE'2024-01-01' falsely matches; comparisons against TIME/TIMESTAMP constants split the same way.

Impact: WHERE c < TIME'00:00:01' matches every row (any YEAR value is "less than" a TIME constant); WHERE c = DATE'2024-01-01' falsely matches YEAR=2024 rows to a date constant.

Root cause direction: the comparator takes the can_compare_as_dates / temporal branch for a YEAR column (temporal family) but numeric type aggregation for a YEAR numeric constant vs temporal constant — asymmetric type decision between column and constant sides.

mysql> 
mysql> -- YEAR column context (temporal semantics — inverted/wrong):
mysql> SELECT c, c < TIME'00:00:01', c = DATE'2024-01-01' FROM y;
+------+--------------------+----------------------+
| c    | c < TIME'00:00:01' | c = DATE'2024-01-01' |
+------+--------------------+----------------------+
| 2024 |                  1 |                    1 |
| 1999 |                  1 |                    0 |
+------+--------------------+----------------------+
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE y (c YEAR);
INSERT INTO y VALUES (2024),(1999);

-- Constant context (numeric semantics — correct):
SELECT (2024) < TIME'00:00:01';    -- 0 (numeric 2024 < 1 → 0)
SELECT (2024) = DATE'2024-01-01';  -- 0 (numerically unequal)

-- YEAR column context (temporal semantics — inverted/wrong):
SELECT c, c < TIME'00:00:01', c = DATE'2024-01-01' FROM y;
-- 2024:  < TIME = 1 (inverted! should be 0)   = DATE = 1 (wrong! should be 0)
-- 1999:  < TIME = 1

Thank you for the bug report.
However, this is not a bug.

The two cases are distinct.

The first statement carries out an operation in TIME comparison context, and the second in a DATE comparison context, since there is no way to distinguish 2024 (the year) from 2024 (the integer). Thus, we attempt to convert 2024 to a TIME, resp. a DATE value, before the comparison takes place.

The last statement involves a year column, which takes priority. We therefore convert TIME'00:00:01' to a year by adding the current date and extracting the year, and for DATE'2024-01-01' we simply extract the year, before carrying out the comparison.