Bug #120190 TLP Bug: COUNT(*) returns 20, while TLP-partitioned queries return 0 under RIGHT JOIN with LATERAL subqueries
Submitted: 31 Mar 13:04
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: LATERAL subqueries, Logic bug, RIGHT JOIN

[31 Mar 13:04] Jasper Andrew 
Description:
A logic inconsistency is observed when applying a TLP (Three-Valued Logic Partitioning) transformation to a query involving RIGHT JOIN, EXISTS, and LATERAL subqueries.

The original query:

SELECT COUNT(*)
FROM (
    SELECT ref_0.user_id AS c0
    FROM orders AS ref_0
    RIGHT JOIN comments AS ref_1
        ON EXISTS ( ... )
    WHERE TRUE
) AS subq_2;

returns 20 rows.

After applying TLP, the query is rewritten into three disjoint predicates:

(subq_2.c0 >= subq_2.c0)
NOT (subq_2.c0 >= subq_2.c0)
(subq_2.c0 >= subq_2.c0) IS NULL

and the results are summed:

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

However, the transformed query returns 0, which is inconsistent with the original result (20).

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 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 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.user_id AS c0
    FROM orders AS ref_0
    RIGHT JOIN comments AS ref_1
        ON EXISTS (
            SELECT 1
            FROM orders AS ref_2,
                 LATERAL (
                     SELECT  
                         subq_0.c0 AS c0, 
                         subq_0.c0 AS c1
                     FROM users AS ref_3,
                          LATERAL (
                              SELECT  
                                  (SELECT user_id FROM orders) AS c0
                              FROM users AS ref_4
                              WHERE ref_4.status IS NULL
                          ) AS subq_0
                     WHERE ref_3.age IS NULL
                 ) AS subq_1
            WHERE TRUE
        )
    WHERE TRUE
) AS subq_2;

-- RESULT: {20}

SELECT (
SELECT count(*)
FROM 
  (SELECT  
        ref_0.user_id AS c0
    FROM orders AS ref_0
    RIGHT JOIN comments AS ref_1
        ON EXISTS (
            SELECT 1
            FROM orders AS ref_2,
                 LATERAL (
                     SELECT  
                         subq_0.c0 AS c0, 
                         subq_0.c0 AS c1
                     FROM users AS ref_3,
                          LATERAL (
                              SELECT  
                                  (SELECT user_id FROM orders) AS c0
                              FROM users AS ref_4
                              WHERE ref_4.status IS NULL
                          ) AS subq_0
                     WHERE ref_3.age IS NULL
                 ) AS subq_1
            WHERE TRUE
        )
    WHERE TRUE) AS subq_2
WHERE (subq_2.c0 >= subq_2.c0)
) + (
SELECT count(*)
FROM 
  (SELECT  
        ref_0.user_id AS c0
    FROM orders AS ref_0
    RIGHT JOIN comments AS ref_1
        ON EXISTS (
            SELECT 1
            FROM orders AS ref_2,
                 LATERAL (
                     SELECT  
                         subq_0.c0 AS c0, 
                         subq_0.c0 AS c1
                     FROM users AS ref_3,
                          LATERAL (
                              SELECT  
                                  (SELECT user_id FROM orders) AS c0
                              FROM users AS ref_4
                              WHERE ref_4.status IS NULL
                          ) AS subq_0
                     WHERE ref_3.age IS NULL
                 ) AS subq_1
            WHERE TRUE
        )
    WHERE TRUE) AS subq_2
WHERE (not (subq_2.c0 >= subq_2.c0))
) + (
SELECT count(*)
FROM 
  (SELECT  
        ref_0.user_id AS c0
    FROM orders AS ref_0
    RIGHT JOIN comments AS ref_1
        ON EXISTS (
            SELECT 1
            FROM orders AS ref_2,
                 LATERAL (
                     SELECT  
                         subq_0.c0 AS c0, 
                         subq_0.c0 AS c1
                     FROM users AS ref_3,
                          LATERAL (
                              SELECT  
                                  (SELECT user_id FROM orders) AS c0
                              FROM users AS ref_4
                              WHERE ref_4.status IS NULL
                          ) AS subq_0
                     WHERE ref_3.age IS NULL
                 ) AS subq_1
            WHERE TRUE
        )
    WHERE TRUE) AS subq_2
WHERE ((subq_2.c0 >= subq_2.c0) is null)
);

-- RESULT: {0}

```