MySQL Bugs: #120152: Unexpected behavior of LEFT JOIN + empty subquery with NULL comparison in WHERE clause

Bug #120152	Unexpected behavior of LEFT JOIN + empty subquery with NULL comparison in WHERE clause
Submitted:	25 Mar 8:50	Modified:	4 Jun 8:38
Reporter:	Jasper Andrew	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.43 (MySQL Community Server - GPL)	OS:	Any (Docker container)
Assigned to:		CPU Architecture:	Any (x86_64)
Tags:	comparison, left join, null, reproducible, row filtering, SQL Standard, subquery, where clause

Description:
When performing a LEFT JOIN with an empty subquery and applying a WHERE condition that compares a column from the left table to a column from the empty subquery, MySQL incorrectly keeps the row even though the comparison involves NULL. According to the SQL standard, any comparison with NULL evaluates to UNKNOWN, and rows for which the WHERE condition is UNKNOWN should be filtered out.

How to repeat:
Schema：
```SQL
CREATE TABLE users (
    id           INT,
    username     VARCHAR(100),
    email        VARCHAR(255),
    age          INT,
    status       VARCHAR(20),
    created_at   TIMESTAMP NULL,
    score        DOUBLE
);

CREATE TABLE posts (
    id          INT,
    user_id     INT,
    title       VARCHAR(255),
    content     VARCHAR(1000),
    views       INT,
    likes       INT,
    created_at  TIMESTAMP NULL,
    rating      DOUBLE
);

INSERT INTO users VALUES
(1, 'alice', 'alice@test.com', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   'bob@test.com',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  'dave@test.com',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    'null@test.com',  18, 'inactive','2022-01-05 14:00:00', 70.0);

INSERT INTO posts VALUES
(1, 1, 'Hello World', 'First post', 100, 10, '2022-01-10 10:00:00', 4.5),
(2, 1, 'Another Post', NULL,        150, 20, '2022-01-11 11:00:00', 3.0),
(3, 2, 'Bob Post',     'Content',   NULL,  5, '2022-01-12 12:00:00', NULL),
(4, 3, NULL,           'Empty',     50,   2, '2022-01-13 13:00:00', 5.0),
(5, 4, 'Last Post',    'Last',      300,  30,'2022-01-14 14:00:00', 4.9);
```

Trigger SQL:

```SQL
select  
  15 as c0
from 
  (select  
        ref_0.score as c0, 
        subq_0.c0 as c1, 
        subq_0.c9 as c2, 
        ref_0.id as c3, 
        subq_0.c4 as c4
      from 
        users as ref_0
          left join (select  
                57 as c0, 
                ref_1.views as c1, 
                ref_1.user_id as c2, 
                55 as c3, 
                3 as c4, 
                39 as c5, 
                ref_1.views as c6, 
                ref_1.id as c7, 
                ref_1.content as c8, 
                ref_1.created_at as c9, 
                ref_1.rating as c10
              from 
                posts as ref_1
              where false) as subq_0
          on (true)
      where true) as subq_1
where subq_1.c3 <= subq_1.c4;

```

Actual Result(MySQL 8.0.43):
```RESULT
c0
15
```

Expected Result (PostgreSQL / SQL standard):
```RESULT
(empty result set)
```

Thank you for the bug report.
Verified as described.

Minimal reproducible SQL:

```SQL
SELECT  
  15 as c0
FROM 
  (SELECT  
        ref_0.id as c3, 
        subq_0.c4 as c4
   FROM users as ref_0
   LEFT JOIN (
        SELECT 3 as c4
        FROM posts as ref_1
        WHERE FALSE
   ) as subq_0
   ON TRUE
) as subq_1
WHERE subq_1.c3 <= subq_1.c4;

```