Repository navigation
wrong null rejective with nulleq and is not null #58793
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 TEXT(227));
CREATE TABLE t1 LIKE t0;
INSERT INTO t1 VALUES ('');
SELECT COUNT(*) FROM t1 LEFT JOIN t0 ON t0.c0 <> t1.c0 WHERE (NULL AND t1.c0) <=> (t0.c0 IS NOT NULL);2. What did you expect to see? (Required)
see the following case:
3. What did you see instead (Required)
// MySQL 8.4.1
MySQL [test]> SELECT COUNT(*) FROM t1 LEFT JOIN t0 ON t0.c0 <> t1.c0 WHERE (NULL AND t1.c0) <=> (t0.c0 IS NOT NULL);
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
// TiDB
mysql> SELECT COUNT(*) FROM t1 LEFT JOIN t0 ON t0.c0 <> t1.c0 WHERE (NULL AND t1.c0) <=> (t0.c0 IS NOT NULL);
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.04 sec)4. What is your TiDB version? (Required)
TiDB v8.5.0
Activity
curry-oss commentedon Jan 8, 2025
/label affects-8.5
winoros commentedon Jan 22, 2025
The wrongly null rejective checking causes it.
The outer join transformed to the inner join wrongly.
[-]Bug Case in Left Join[/-][+]wrong null rejective with nulleq and is not null[/+]planner: replace outer-join null-reject evaluation with structural pr…