Description:
A logical inconsistency is observed between SELECT, DELETE, and UPDATE statements when evaluating an identical EXISTS predicate involving a LEFT JOIN with a correlated subquery.
The SELECT COUNT(*) query reports that at least one row satisfies the EXISTS condition (returns 1), indicating that the predicate evaluates to true for some rows in posts. However, executing semantically equivalent DELETE and UPDATE statements with the same WHERE EXISTS clause affects zero rows.
This suggests that the database engine evaluates the EXISTS predicate differently depending on the query type (read vs write), which violates expected SQL semantic consistency.
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
LEFT JOIN (
SELECT posts.id AS c7
FROM posts AS ref_1
WHERE posts.likes IS NULL
) AS subq_0
ON true
WHERE subq_0.c7 IS NOT NULL
);
-- RESULT: {1}
DELETE FROM posts
WHERE EXISTS (
SELECT 1
FROM orders AS ref_0
LEFT JOIN (
SELECT posts.id AS c7
FROM posts AS ref_1
WHERE posts.likes IS NULL
) AS subq_0
ON true
WHERE subq_0.c7 IS NOT NULL
);
-- affected_rows=0
UPDATE posts
SET views = posts.user_id,
likes = posts.views,
created_at = posts.created_at,
rating = posts.rating
WHERE EXISTS (
SELECT 1
FROM orders AS ref_0
LEFT JOIN (
SELECT posts.id AS c7
FROM posts AS ref_1
WHERE posts.likes IS NULL
) AS subq_0
ON true
WHERE subq_0.c7 IS NOT NULL
);
-- affected_rows=0
```
Description: A logical inconsistency is observed between SELECT, DELETE, and UPDATE statements when evaluating an identical EXISTS predicate involving a LEFT JOIN with a correlated subquery. The SELECT COUNT(*) query reports that at least one row satisfies the EXISTS condition (returns 1), indicating that the predicate evaluates to true for some rows in posts. However, executing semantically equivalent DELETE and UPDATE statements with the same WHERE EXISTS clause affects zero rows. This suggests that the database engine evaluates the EXISTS predicate differently depending on the query type (read vs write), which violates expected SQL semantic consistency. 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 LEFT JOIN ( SELECT posts.id AS c7 FROM posts AS ref_1 WHERE posts.likes IS NULL ) AS subq_0 ON true WHERE subq_0.c7 IS NOT NULL ); -- RESULT: {1} DELETE FROM posts WHERE EXISTS ( SELECT 1 FROM orders AS ref_0 LEFT JOIN ( SELECT posts.id AS c7 FROM posts AS ref_1 WHERE posts.likes IS NULL ) AS subq_0 ON true WHERE subq_0.c7 IS NOT NULL ); -- affected_rows=0 UPDATE posts SET views = posts.user_id, likes = posts.views, created_at = posts.created_at, rating = posts.rating WHERE EXISTS ( SELECT 1 FROM orders AS ref_0 LEFT JOIN ( SELECT posts.id AS c7 FROM posts AS ref_1 WHERE posts.likes IS NULL ) AS subq_0 ON true WHERE subq_0.c7 IS NOT NULL ); -- affected_rows=0 ```