Bug #120980 DATE column vs INTEGER column comparison returns different results with column reference vs literal value

• Submitted: 23 Jul 2:09
• Reporter: we 李
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.46
• OS: Any
• CPU Architecture: Any

[23 Jul 2:09] we 李

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.