Description:
During NoREC testing, a query using COUNT(*) returns 5, but when rewritten using SUM(CASE WHEN ... THEN 1 ELSE 0 END), the result becomes 0.
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 (
SELECT ref_0.likes AS c3,
CASE WHEN 55.32 = 38.4 THEN (select var_samp(id) from posts) ELSE (select avg(id) from orders) END AS c13
FROM posts AS ref_0
WHERE ref_0.views <> ref_0.likes
) AS subq_0
RIGHT JOIN orders AS ref_1
LEFT JOIN posts AS ref_2
ON ref_1.created_at = ref_2.created_at
ON subq_0.c3 = ref_2.id
WHERE (('x8zgv' != 'v')
AND (subq_0.c13 = subq_0.c13));
-- result: {5}
SELECT sum(CASE WHEN (('x8zgv' != 'v') AND (subq_0.c13 = subq_0.c13)) is true THEN 1 ELSE 0 END + 0)
FROM (
SELECT ref_0.likes AS c3,
CASE WHEN 55.32 = 38.4 THEN (select var_samp(id) from posts) ELSE (select avg(id) from orders) END AS c13
FROM posts AS ref_0
WHERE ref_0.views <> ref_0.likes
) AS subq_0
RIGHT JOIN orders AS ref_1
LEFT JOIN posts AS ref_2
ON ref_1.created_at = ref_2.created_at
ON subq_0.c3 = ref_2.id;
-- result: {0}
```
Description: During NoREC testing, a query using COUNT(*) returns 5, but when rewritten using SUM(CASE WHEN ... THEN 1 ELSE 0 END), the result becomes 0. 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 ( SELECT ref_0.likes AS c3, CASE WHEN 55.32 = 38.4 THEN (select var_samp(id) from posts) ELSE (select avg(id) from orders) END AS c13 FROM posts AS ref_0 WHERE ref_0.views <> ref_0.likes ) AS subq_0 RIGHT JOIN orders AS ref_1 LEFT JOIN posts AS ref_2 ON ref_1.created_at = ref_2.created_at ON subq_0.c3 = ref_2.id WHERE (('x8zgv' != 'v') AND (subq_0.c13 = subq_0.c13)); -- result: {5} SELECT sum(CASE WHEN (('x8zgv' != 'v') AND (subq_0.c13 = subq_0.c13)) is true THEN 1 ELSE 0 END + 0) FROM ( SELECT ref_0.likes AS c3, CASE WHEN 55.32 = 38.4 THEN (select var_samp(id) from posts) ELSE (select avg(id) from orders) END AS c13 FROM posts AS ref_0 WHERE ref_0.views <> ref_0.likes ) AS subq_0 RIGHT JOIN orders AS ref_1 LEFT JOIN posts AS ref_2 ON ref_1.created_at = ref_2.created_at ON subq_0.c3 = ref_2.id; -- result: {0} ```