Description:
When executing a query involving a LEFT JOIN with a constant FALSE condition, MySQL returns 1 row, while PostgreSQL and DuckDB correctly return 0. The difference occurs when filtering on columns that become NULL due to the join, where comparison predicates involving NULL appear to be evaluated inconsistently in MySQL.
How to repeat:
```SQL
-- Schema
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
WITH jennifer_2 AS (
SELECT
ref_0.likes AS c0,
subq_3.c1 AS c1,
ref_0.rating AS c2
FROM posts ref_0
LEFT JOIN (
SELECT
ref_1.created_at AS c0,
76 AS c1
FROM users ref_1
WHERE true
) subq_3
ON (false)
WHERE true
)
SELECT COUNT(*)
FROM jennifer_2 ref_2
WHERE (ref_2.c1 > ref_2.c0) AND (ref_2.c2 IS NULL);
-- Result: {1} wrong!
```
Description: When executing a query involving a LEFT JOIN with a constant FALSE condition, MySQL returns 1 row, while PostgreSQL and DuckDB correctly return 0. The difference occurs when filtering on columns that become NULL due to the join, where comparison predicates involving NULL appear to be evaluated inconsistently in MySQL. How to repeat: ```SQL -- Schema 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 WITH jennifer_2 AS ( SELECT ref_0.likes AS c0, subq_3.c1 AS c1, ref_0.rating AS c2 FROM posts ref_0 LEFT JOIN ( SELECT ref_1.created_at AS c0, 76 AS c1 FROM users ref_1 WHERE true ) subq_3 ON (false) WHERE true ) SELECT COUNT(*) FROM jennifer_2 ref_2 WHERE (ref_2.c1 > ref_2.c0) AND (ref_2.c2 IS NULL); -- Result: {1} wrong! ```