MySQL Bugs: #120980: DATE column vs INTEGER column comparison returns different results with column reference vs literal value

Bug #120980	DATE column vs INTEGER column comparison returns different results with column reference vs literal value
Submitted:	23 Jul 2:09	Modified:	3 Aug 8:22
Reporter:	we 李	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.46	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a DATE column is compared with an INTEGER column (containing NULL) using NOT BETWEEN, MySQL produces different results depending on whether the left operand is a column reference or a literal value, even though they are logically equivalent.

Query 1 (column reference) returns 1 row, while Query 2 (literal value) returns 0 rows, despite O_ORDERDATE = '1996-12-01' making them logically identical.

How to repeat:
CREATE TABLE t (
    date_col DATE,
    int_col INTEGER
);

INSERT INTO t VALUES ('1996-12-01', NULL);

-- Query 1: using column reference (returns 1)
SELECT COUNT(*) FROM t 
WHERE date_col = '1996-12-01' 
  AND date_col NOT BETWEEN '1992-03-04' AND int_col;

-- Query 2: using literal value (returns 0)
SELECT COUNT(*) FROM t 
WHERE date_col = '1996-12-01' 
  AND '1996-12-01' NOT BETWEEN '1992-03-04' AND int_col;

Suggested fix:
MySQL should apply consistent type inference for column references and literal values in BETWEEN comparisons. The type conversion path should be the same regardless of whether the operand is a column or a literal.

Hello, has there been any update on this bug? It can cause incorrect query results in production. Thank you

Hi we 李,

Thank you for the test case. Verified as described.