Bug #120189 TLP Bug: COUNT(*) on orders returns 5, but equivalent TLP-style with complex NULLIF and CASE expressions return 10

• Submitted: 31 Mar 12:43
• Reporter: Jasper Andrew
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.43 (MySQL Community Server - GPL)
• OS: Any (Docker container)
• CPU Architecture: Any (x86_64)
• Tags: Logic bug

[31 Mar 12:43] Jasper Andrew

Description:
When executing a simple query:

SELECT COUNT(*) FROM orders;

the result is 5.

However, when using a logically equivalent query generated by TLP-style transformation, which splits the counting into three subqueries with NULLIF, CASE WHEN, SIGN, CEILING, and STDDEV expressions:

SELECT 
    (SELECT COUNT(*) FROM orders WHERE ... ) +
    (SELECT COUNT(*) FROM orders WHERE NOT (...) ) +
    (SELECT COUNT(*) FROM orders WHERE ... IS NULL);

the result is 10, which is inconsistent with the original COUNT(*).

This indicates a logic bug in the optimizer or NoREC/TLP evaluation, where constant expressions and NULL-sensitive comparisons inside CASE or NULLIF affect the SUM calculation differently than a straightforward COUNT(*).

How to repeat:
```SQL
-- SCHEMA 
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 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
  orders AS ref_0;

-- RESULT: {5}

SELECT 
    (
        SELECT COUNT(*)
        FROM orders AS ref_0
        WHERE (
            NULLIF(
                SIGN(59.54),
                CASE 
                    WHEN ('ukhus3' != 'sa') 
                         OR (74.12 <> CASE WHEN ref_0.status IS NULL THEN 36.44 ELSE 79.17 END)
                    THEN CEILING(
                        CASE WHEN true THEN 5.65 ELSE (SELECT VAR_SAMP(id) FROM comments) END
                    )
                    ELSE (SELECT STDDEV_SAMP(id) FROM posts)
                END
            ) >= CASE 
                    WHEN ref_0.id > ref_0.user_id 
                    THEN (SELECT STDDEV_SAMP(id) FROM comments)
                    ELSE COALESCE(
                        (SELECT STDDEV_SAMP(id) FROM comments),
                        ROUND(56.99, (SELECT id FROM comments))
                    )
                END
        )
    )
    +
    (
        SELECT COUNT(*)
        FROM orders AS ref_0
        WHERE NOT (
            NULLIF(
                SIGN(59.54),
                CASE 
                    WHEN ('ukhus3' != 'sa') 
                         OR (74.12 <> CASE WHEN ref_0.status IS NULL THEN 36.44 ELSE 79.17 END)
                    THEN CEILING(
                        CASE WHEN true THEN 5.65 ELSE (SELECT VAR_SAMP(id) FROM comments) END
                    )
                    ELSE (SELECT STDDEV_SAMP(id) FROM posts)
                END
            ) >= CASE 
                    WHEN ref_0.id > ref_0.user_id 
                    THEN (SELECT STDDEV_SAMP(id) FROM comments)
                    ELSE COALESCE(
                        (SELECT STDDEV_SAMP(id) FROM comments),
                        ROUND(56.99, (SELECT id FROM comments))
                    )
                END
        )
    )
    +
    (
        SELECT COUNT(*)
        FROM orders AS ref_0
        WHERE (
            NULLIF(
                SIGN(59.54),
                CASE 
                    WHEN ('ukhus3' != 'sa') 
                         OR (74.12 <> CASE WHEN ref_0.status IS NULL THEN 36.44 ELSE 79.17 END)
                    THEN CEILING(
                        CASE WHEN true THEN 5.65 ELSE (SELECT VAR_SAMP(id) FROM comments) END
                    )
                    ELSE (SELECT STDDEV_SAMP(id) FROM posts)
                END
            ) >= CASE 
                    WHEN ref_0.id > ref_0.user_id 
                    THEN (SELECT STDDEV_SAMP(id) FROM comments)
                    ELSE COALESCE(
                        (SELECT STDDEV_SAMP(id) FROM comments),
                        ROUND(56.99, (SELECT id FROM comments))
                    )
                END
        ) IS NULL
    );

-- RESULT: {10}

```