Bug #120191 TLP bug: Incorrect result under Ternary Logic Partitioning (TLP) with complex RIGHT JOIN chains
Submitted: 31 Mar 13:32
Reporter: Jasper Andrew Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.43 (MySQL Community Server - GPL) OS:Any (Docker container)
Assigned to: CPU Architecture:Any (x86_64)
Tags: COALESCE, join, Logic bug, NULLIF, subquery

[31 Mar 13:32] Jasper Andrew 
Description:
A logic bug is exposed by Ternary Logic Partitioning (TLP) on a query involving multiple nested RIGHT JOINs and constant join conditions.

The original query returns a count of 25. However, when applying TLP by partitioning on the predicate (ref_4.status IS NULL) into three mutually exclusive conditions:

(ref_4.status IS NULL)
NOT (ref_4.status IS NULL)
(ref_4.status IS NULL) IS NULL

and summing the results of the three subqueries, the total result becomes 2525, which significantly deviates from the original result.

The query includes several unusual but valid constructs that may contribute to the issue:

Deeply nested RIGHT JOIN chains
Join conditions that are constant expressions (ON false, ON true)
EXISTS predicates independent of outer query columns
Mixed join types (LEFT, RIGHT, INNER) in the same join tree

According to TLP principles, the sum of the partitioned queries should be equal to the result of the original query. The observed discrepancy indicates a violation of logical equivalence, suggesting an error in query planning or execution, possibly related to join reordering, predicate pushdown, or null-handling in outer joins.

This demonstrates a correctness bug in handling complex join trees under three-valued logic semantics.

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

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 c0, ref_0.created_at AS c1, ref_0.id AS c2 FROM comments AS ref_0 WHERE 'x' >= 'rw') AS subq_0
RIGHT JOIN users AS ref_1
    LEFT JOIN users AS ref_2
        LEFT JOIN users AS ref_3 ON (ref_2.username = ref_3.username)
        INNER JOIN orders AS ref_4 ON (ref_2.id <> 4)
    ON (EXISTS (SELECT 1 FROM orders AS ref_5 WHERE TRUE))
    LEFT JOIN users AS ref_6 ON (ref_4.id IS NOT NULL)
ON (subq_0.c0 = ref_1.id)
RIGHT JOIN posts AS ref_7
    RIGHT JOIN posts AS ref_8
        RIGHT JOIN orders AS ref_9
            INNER JOIN comments AS ref_10 ON (false)
        ON (true)
    ON (false)
    RIGHT JOIN users AS ref_16
        ON (EXISTS (SELECT 1 FROM posts AS ref_17 WHERE '9lcg' <> '7u'))
ON (NULLIF('l6y6', 't6q6d6') != COALESCE(COALESCE('k', 'uac'), 'bj9'));

-- RESULT: {25}

SELECT
(
    SELECT COUNT(*)
    FROM (SELECT ref_0.id AS c0, ref_0.created_at AS c1, ref_0.id AS c2 FROM comments AS ref_0 WHERE 'x' >= 'rw') AS subq_0
    RIGHT JOIN users AS ref_1
        LEFT JOIN users AS ref_2
            LEFT JOIN users AS ref_3 ON (ref_2.username = ref_3.username)
            INNER JOIN orders AS ref_4 ON (ref_2.id <> 4)
        ON (EXISTS (SELECT 1 FROM orders AS ref_5 WHERE TRUE))
        LEFT JOIN users AS ref_6 ON (ref_4.id IS NOT NULL)
    ON (subq_0.c0 = ref_1.id)
    RIGHT JOIN posts AS ref_7
        RIGHT JOIN posts AS ref_8
            RIGHT JOIN orders AS ref_9
                INNER JOIN comments AS ref_10 ON (false)
            ON (true)
        ON (false)
        RIGHT JOIN users AS ref_16 ON (EXISTS (SELECT 1 FROM posts AS ref_17 WHERE '9lcg' <> '7u'))
    ON (NULLIF('l6y6', 't6q6d6') != COALESCE(COALESCE('k', 'uac'), 'bj9'))
    WHERE (ref_4.status IS NULL)
)
+
(
    SELECT COUNT(*)
    FROM (SELECT ref_0.id AS c0, ref_0.created_at AS c1, ref_0.id AS c2 FROM comments AS ref_0 WHERE 'x' >= 'rw') AS subq_0
    RIGHT JOIN users AS ref_1
        LEFT JOIN users AS ref_2
            LEFT JOIN users AS ref_3 ON (ref_2.username = ref_3.username)
            INNER JOIN orders AS ref_4 ON (ref_2.id <> 4)
        ON (EXISTS (SELECT 1 FROM orders AS ref_5 WHERE TRUE))
        LEFT JOIN users AS ref_6 ON (ref_4.id IS NOT NULL)
    ON (subq_0.c0 = ref_1.id)
    RIGHT JOIN posts AS ref_7
        RIGHT JOIN posts AS ref_8
            RIGHT JOIN orders AS ref_9
                INNER JOIN comments AS ref_10 ON (false)
            ON (true)
        ON (false)
        RIGHT JOIN users AS ref_16 ON (EXISTS (SELECT 1 FROM posts AS ref_17 WHERE '9lcg' <> '7u'))
    ON (NULLIF('l6y6', 't6q6d6') != COALESCE(COALESCE('k', 'uac'), 'bj9'))
    WHERE (NOT (ref_4.status IS NULL))
)
+
(
    SELECT COUNT(*)
    FROM (SELECT ref_0.id AS c0, ref_0.created_at AS c1, ref_0.id AS c2 FROM comments AS ref_0 WHERE 'x' >= 'rw') AS subq_0
    RIGHT JOIN users AS ref_1
        LEFT JOIN users AS ref_2
            LEFT JOIN users AS ref_3 ON (ref_2.username = ref_3.username)
            INNER JOIN orders AS ref_4 ON (ref_2.id <> 4)
        ON (EXISTS (SELECT 1 FROM orders AS ref_5 WHERE TRUE))
        LEFT JOIN users AS ref_6 ON (ref_4.id IS NOT NULL)
    ON (subq_0.c0 = ref_1.id)
    RIGHT JOIN posts AS ref_7
        RIGHT JOIN posts AS ref_8
            RIGHT JOIN orders AS ref_9
                INNER JOIN comments AS ref_10 ON (false)
            ON (true)
        ON (false)
        RIGHT JOIN users AS ref_16 ON (EXISTS (SELECT 1 FROM posts AS ref_17 WHERE '9lcg' <> '7u'))
    ON (NULLIF('l6y6', 't6q6d6') != COALESCE(COALESCE('k', 'uac'), 'bj9'))
    WHERE ((ref_4.status IS NULL) IS NULL)
);

-- RESULT: {2525}

```