Bug #120180 NoREC Bug: COUNT(*) and SUM(CASE WHEN ...) return inconsistent results with string-length expression and NULL-sensitive

• Submitted: 30 Mar 13:32
• 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: case when, FUNCTION, Logic bug

[30 Mar 13:32] Jasper Andrew

Description:
A discrepancy is observed between COUNT(*) and its NoREC-rewritten form using SUM(CASE WHEN ... THEN 1 ELSE 0 END +0) for the same predicate. The two queries return different results (3 vs 4), indicating a logical inconsistency.

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

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

-- TRIGGER SQL
-- The following two queries are expected to return the same result

SELECT count(*)
FROM 
  posts AS ref_0
WHERE (CASE 
      WHEN ref_0.rating IS NULL THEN ref_0.id
      ELSE OCTET_LENGTH(REPEAT(
        CASE 
          WHEN (61.16 != 46.58 AND 87.41 > 54.8)
            THEN CASE WHEN 96.63 = 25.33 THEN 'ds8' ELSE 'i2zl' END
          ELSE COALESCE('dutj','p4dco')
        END,
        ref_0.user_id
      ))
    END < ref_0.likes);

-- RESULT: {3}

SELECT SUM(
  CASE WHEN (
    CASE 
      WHEN ref_0.rating IS NULL THEN ref_0.id
      ELSE OCTET_LENGTH(REPEAT(
        CASE 
          WHEN (61.16 != 46.58 AND 87.41 > 54.8)
            THEN CASE WHEN 96.63 = 25.33 THEN 'ds8' ELSE 'i2zl' END
          ELSE COALESCE('dutj','p4dco')
        END,
        ref_0.user_id
      ))
    END < ref_0.likes
  ) IS TRUE THEN 1 ELSE 0 END + 0
)
FROM posts AS ref_0;

-- RESULT: {4}

```