Logic Error: TLP Test - COUNT(*) returns 100, while equivalent TLP-partitioned queries return 200 under RIGHT JOIN with LATERAL subqueries

Description:
A logic inconsistency is observed when applying a TLP (Three-Valued Logic Partitioning) transformation to a query involving RIGHT JOIN, LATERAL subqueries and CASE WHEN clause.

The original query returns 100 rows.

After applying TLP, the query is rewritten into three disjoint predicates and the results are summed:

COUNT(P) + COUNT(NOT P) + COUNT(P IS NULL)

However, the transformed query returns 200, which is inconsistent with the original result (100).

How to repeat:

-- 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
);

CREATE TABLE comments (
    id          INT,
    post_id     INT,
    user_id     INT,
    content     VARCHAR(1000),
    is_spam     INT,
    created_at  TIMESTAMP NULL
);

CREATE TABLE orders (
    id          INT,
    user_id     INT,
    amount      DOUBLE,
    status      VARCHAR(20),
    created_at  TIMESTAMP NULL
);

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);

INSERT INTO comments VALUES
(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'),
(2, 1, 3, 'Spam here', 1,  '2022-01-21 11:00:00'),
(3, 2, 1, 'Thanks',    0, '2022-01-22 12:00:00'),
(4, 4, 5, NULL,        0, '2022-01-23 13:00:00');

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.id AS c5,
    subq_0.c0 AS c6
  FROM orders AS ref_0
  RIGHT JOIN orders AS ref_1
    RIGHT JOIN orders AS ref_2
      ON (
        (ref_2.id <> ref_2.id)
        OR (
          (SELECT STDDEV_SAMP(id) FROM orders) <> 35.87
        )
      )
    ON FALSE,
    LATERAL (
      SELECT
        (SELECT user_id FROM comments LIMIT 1 OFFSET 3) AS c0
      FROM comments AS ref_3
      WHERE TRUE
    ) AS subq_0
  WHERE TRUE) AS subq_1;

-- RESULT: {100}

WITH subq_1 AS (
  SELECT
    ref_0.id AS c5,
    subq_0.c0 AS c6
  FROM orders AS ref_0
  RIGHT JOIN orders AS ref_1
    RIGHT JOIN orders AS ref_2
      ON (
        (ref_2.id <> ref_2.id)
        OR (
          (SELECT STDDEV_SAMP(id) FROM orders) <> 35.87
        )
      )
    ON FALSE,
    LATERAL (
      SELECT
        (SELECT user_id FROM comments LIMIT 1 OFFSET 3) AS c0
      FROM comments AS ref_3
      WHERE TRUE
    ) AS subq_0
  WHERE TRUE
)

SELECT

(
  SELECT COUNT(*)
  FROM subq_1
  WHERE NULLIF(
    COALESCE(17.33, 91.87),
    CASE
      WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
      THEN 71.48
      ELSE ABS(
        CASE
          WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
          THEN (SELECT STDDEV_POP(id) FROM posts)
          ELSE 75.77
        END
      )
    END
  ) <> 68.7
)

+

(
  SELECT COUNT(*)
  FROM subq_1
  WHERE NOT (
    NULLIF(
      COALESCE(17.33, 91.87),
      CASE
        WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
        THEN 71.48
        ELSE ABS(
          CASE
            WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
            THEN (SELECT STDDEV_POP(id) FROM posts)
            ELSE 75.77
          END
        )
      END
    ) <> 68.7
  )
)

+

(
  SELECT COUNT(*)
  FROM subq_1
  WHERE (
    NULLIF(
      COALESCE(17.33, 91.87),
      CASE
        WHEN RPAD(LPAD('fz71k', c6, 'bwdpd'), c5, ' ') >= 'wr1x2b'
        THEN 71.48
        ELSE ABS(
          CASE
            WHEN 85.86 = (SELECT VAR_SAMP(id) FROM posts)
            THEN (SELECT STDDEV_POP(id) FROM posts)
            ELSE 75.77
          END
        )
      END
    ) <> 68.7
  ) IS NULL
);

-- RESULT: {200}

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)

No source files or tests are named. Reproduce the original and TLP-transformed queries on the reported MySQL and PolarDB-X versions, then trace the differing row counts through the RIGHT JOIN and LATERAL subqueries. Done means the inconsistency is isolated and a regression check or documented expected behavior covers the result.