Logic Error: EXISTS subquery treated as constant TRUE yields different result than literal TRUE

[Jasper0209]

Description:
When replacing a constant EXISTS predicate with literal TRUE, the query result changes unexpectedly, even though the EXISTS expression evaluates to a constant truth value.

In particular, the following expression:

EXISTS (
    SELECT 1
    FROM users AS ref_2
    WHERE '4' <= 'epozic'
)

is equivalent to a constant expression (always TRUE in the tested environment). This can be verified by wrapping it in a SELECT:

SELECT EXISTS (
    SELECT 1
    FROM users AS ref_2
    WHERE '4' <= 'epozic'
);

which returns 1 (TRUE).

However, replacing this expression directly with TRUE in the original query leads to different results.

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

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

-- TRIGGER SQL
SELECT COUNT(*) 
FROM (
    SELECT subq_0.c0 AS c0
    FROM (
        SELECT ref_0.title AS c0
        FROM posts AS ref_0
        WHERE '8' <> 'uc0'
    ) AS subq_0
    WHERE EXISTS (
        SELECT 1
        FROM users AS ref_2
        WHERE '4' <= 'epozic'
    )
) AS subq_1
RIGHT JOIN comments AS ref_3
    ON EXISTS (
        SELECT 1
        FROM comments AS ref_4
        WHERE subq_1.c0 IS NOT NULL
    );

-- RESULT: {64}

SELECT COUNT(*) 
FROM (
    SELECT subq_0.c0 AS c0
    FROM (
        SELECT ref_0.title AS c0
        FROM posts AS ref_0
        WHERE '8' <> 'uc0'
    ) AS subq_0
    WHERE TRUE
) AS subq_1
RIGHT JOIN comments AS ref_3
    ON EXISTS (
        SELECT 1
        FROM comments AS ref_4
        WHERE subq_1.c0 IS NOT NULL
    );

-- RESULT: {16}

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)