Bug #121420 Adding OR FALSE to an IS NULL condition changes query result

• Submitted: 1 Oct 18:30
• Reporter: jinxin gui
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.0, 26.10.0-er
• OS: Linux
• CPU Architecture: Any
• Tags: OR, predicate

[1 Oct 18:30] jinxin gui

Description:
Adding an always-false OR condition to an IS NULL predicate changes the query result.
The two conditions:
t1.col_date_key IS NULL

and
t1.col_date_key IS NULL OR FALSE

should be return inconsistent results in my test case. However, the second query returns no rows, while the original query returns two rows.
The issue only occurs when the DATE column contains zero dates ('0000-00-00'). The same query with AND TRUE returns the expected result.

How to repeat:
CREATE TABLE t1 (
  pk INT NOT NULL,
  d DATE NOT NULL,
  PRIMARY KEY (pk)
) ENGINE=MyISAM;

CREATE TABLE t2 (
  pk INT NOT NULL
);

INSERT IGNORE INTO t1 VALUES
(1, '0000-00-00'),
(2, '2020-01-01'),
(3, '0000-00-00');

INSERT INTO t2 VALUES (1);

SELECT * FROM t2, t1 WHERE t1.d IS NULL;

SELECT * FROM t2, t1 WHERE t1.d IS NULL OR FALSE;

SELECT * FROM t2, t1 WHERE t1.d IS NULL AND TRUE;

Actual result:
The first and third query returns:
pk | pk | col_int_key | col_date_key
3  | 14 | 4           | 0000-00-00
3  | 17 | 3           | 0000-00-00

The second query returns an empty result set.
The third query returns the same two rows as the first query.
Expected result:
The all three queries should return the same result set because adding OR FALSE does not change the logical condition.