Bug #120271 Logic error: EXISTS subquery treated as constant TRUE yields different result than literal TRUE

• Submitted: 15 Apr 10:54
• Modified: 16 Apr 21:39
• Reporter: Jasper Andrew
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.43 (MySQL Community Server - GPL)
• OS: Any (Docker container)
• CPU Architecture: Any (x86_64)
• Tags: exists, Logic bug, true

[15 Apr 10:54] Jasper Andrew

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:
```SQL
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:
```SQL
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:
```SQL
-- 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', '[e-mail]', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   '[e-mail]',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  '[e-mail]',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    '[e-mail]',  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}
```

[16 Apr 21:39] Roy Lyseng

Thank you for the bug report.
Verified as described.