Logic Error: NoREC Test - COUNT(*) vs SUM(CASE...) produces inconsistent results with RIGHT JOIN + LEFT JOIN

[Jasper0209]

Description:
During NoREC testing, a query using COUNT(*) returns 5, but its semantically equivalent rewritten form using SUM(CASE WHEN ... THEN 1 ELSE 0 END) returns 0.

How to repeat:

-- 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}

version info:

MySQL [test]> select version();
+----------------------------------+
| version()                        |
+----------------------------------+
| 8.0.32-X-Cluster-8.4.19-20250825 |
+----------------------------------+
1 row in set (0.00 sec)

MySQL [test]> select polardb_version();
+----------------------------------------------------------+
| polardb_version()                                        |
+----------------------------------------------------------+
| PolarDB V2.0_2.4.2_8.4.19-20250825 (Distributed Edition) |
+----------------------------------------------------------+
1 row in set (0.00 sec)