Description:
There is an inconsistency between DELETE and SELECT when using EXISTS with a LATERAL subquery that references the same table (posts).
- DELETE correctly reports 3 affected rows.
- SELECT returns only 2 rows.
This indicates a possible bug in MySQL query planning or row visibility when the same table appears in the subquery and main query.
How to repeat:
```SQL
-- SCHEMA
CREATE TABLE posts (
id INT,
user_id INT,
title VARCHAR(255),
content VARCHAR(1000),
views INT,
likes INT,
created_at TIMESTAMP NULL,
rating DOUBLE
);
CREATE TABLE orders (
id INT,
user_id INT,
amount DOUBLE,
status VARCHAR(20),
created_at TIMESTAMP NULL
);
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);
INSERT INTO orders VALUES
(1, 1, 100.00, 'paid', '2022-02-01 09:00:00'),
(2, 1, 200.50, 'shipped', '2022-02-02 10:00:00'),
(3, 2, NULL, 'failed', '2022-02-03 11:00:00'),
(4, 3, 50.00, 'paid', '2022-02-04 12:00:00'),
(5, 5, 999.99, 'paid', '2022-02-05 13:00:00');
-- TRIGGER SQL
SELECT COUNT(*)
FROM posts
WHERE EXISTS (
SELECT 1
FROM orders AS ref_0,
LATERAL (
SELECT
ref_1.likes AS c0,
posts.content AS c1,
posts.likes AS c2
FROM posts AS ref_1
WHERE ref_1.likes < posts.id
AND 3.9 <= 83.47
) AS subq_0
WHERE subq_0.c1 IS NOT NULL
);
-- RESULT: {3}
DELETE FROM posts
WHERE EXISTS (
SELECT 1
FROM orders AS ref_0,
LATERAL (
SELECT
ref_1.likes AS c0,
posts.content AS c1,
posts.likes AS c2
FROM posts AS ref_1
WHERE ref_1.likes < posts.id
AND 3.9 <= 83.47
) AS subq_0
WHERE subq_0.c1 IS NOT NULL
);
-- affected row: {2}
```
Description: There is an inconsistency between DELETE and SELECT when using EXISTS with a LATERAL subquery that references the same table (posts). - DELETE correctly reports 3 affected rows. - SELECT returns only 2 rows. This indicates a possible bug in MySQL query planning or row visibility when the same table appears in the subquery and main query. How to repeat: ```SQL -- SCHEMA CREATE TABLE posts ( id INT, user_id INT, title VARCHAR(255), content VARCHAR(1000), views INT, likes INT, created_at TIMESTAMP NULL, rating DOUBLE ); CREATE TABLE orders ( id INT, user_id INT, amount DOUBLE, status VARCHAR(20), created_at TIMESTAMP NULL ); 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); INSERT INTO orders VALUES (1, 1, 100.00, 'paid', '2022-02-01 09:00:00'), (2, 1, 200.50, 'shipped', '2022-02-02 10:00:00'), (3, 2, NULL, 'failed', '2022-02-03 11:00:00'), (4, 3, 50.00, 'paid', '2022-02-04 12:00:00'), (5, 5, 999.99, 'paid', '2022-02-05 13:00:00'); -- TRIGGER SQL SELECT COUNT(*) FROM posts WHERE EXISTS ( SELECT 1 FROM orders AS ref_0, LATERAL ( SELECT ref_1.likes AS c0, posts.content AS c1, posts.likes AS c2 FROM posts AS ref_1 WHERE ref_1.likes < posts.id AND 3.9 <= 83.47 ) AS subq_0 WHERE subq_0.c1 IS NOT NULL ); -- RESULT: {3} DELETE FROM posts WHERE EXISTS ( SELECT 1 FROM orders AS ref_0, LATERAL ( SELECT ref_1.likes AS c0, posts.content AS c1, posts.likes AS c2 FROM posts AS ref_1 WHERE ref_1.likes < posts.id AND 3.9 <= 83.47 ) AS subq_0 WHERE subq_0.c1 IS NOT NULL ); -- affected row: {2} ```