Bug #120308 Wrong result for string-vs-`BIT` comparison in a window-function query

• Submitted: 21 Apr 14:03
• Modified: 22 Apr 4:27
• Reporter: QiFan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6
• OS: Linux (Observed on Linux via the `mysql:9.6` )
• CPU Architecture: Any

[21 Apr 14:03] QiFan Liu

Description:
`Description *:`
MySQL returns an incorrect result in a window-function query when comparing a numeric string literal to a `BIT(7)` column that has a `UNIQUE` key. In the reproduced case, the same window-driven workload reports two qualifying rows in a row-by-row validation branch, but the main filtered branch returns only one row.

The reproduced query uses a CTE, a named window, `ROW_NUMBER()`, and `LAG()` to drive predicate evaluation. Under that window-function setup, MySQL keeps only `"1100011"` even though both rows should satisfy the predicate.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c2 BIT(7), UNIQUE (c2));
INSERT INTO t0 (c2) VALUES (b'1100011'), (b'0010110');

WITH _wf_cte AS (
  SELECT
    seed.grp_id,
    seed.ord_id,
    ROW_NUMBER() OVER w_main AS rn,
    LAG(seed.ord_id, 1, NULL) OVER w_main AS wf_probe_1
  FROM (
    SELECT 7 AS grp_id, 1 AS ord_id
    UNION ALL
    SELECT 7, 2
    UNION ALL
    SELECT 9, 1
  ) AS seed
  WINDOW w_main AS (PARTITION BY seed.grp_id ORDER BY seed.ord_id)
)
SELECT JSON_OBJECT(
  'where_count',
  (
    SELECT COUNT(*)
    FROM t0
    WHERE EXISTS (
      SELECT 1
      FROM _wf_cte AS w
      WHERE w.grp_id = 7
        AND w.rn = 1
        AND w.wf_probe_1 IS NULL
    )
      AND NOT (('9') >= c2)
  ),
  'where_rows',
  COALESCE(
    (
      SELECT GROUP_CONCAT(BIN(c2) ORDER BY BIN(c2) SEPARATOR ',')
      FROM t0
      WHERE EXISTS (
        SELECT 1
        FROM _wf_cte AS w
        WHERE w.grp_id = 7
          AND w.rn = 1
          AND w.wf_probe_1 IS NULL
      )
        AND NOT (('9') >= c2)
    ),
    ''
  ),
  'window_check_count',
  (
    SELECT SUM(flag)
    FROM (
      SELECT (
        EXISTS (
          SELECT 1
          FROM _wf_cte AS w
          WHERE w.grp_id = 7
            AND w.rn = 1
            AND w.wf_probe_1 IS NULL
        )
        AND (NOT (('9') >= c2))
      ) IS TRUE AS flag
      FROM t0
    ) AS q
  )
) AS observation
FROM (
  SELECT 1 AS anchor
  FROM _wf_cte
  WHERE grp_id = 7 AND rn = 1
  LIMIT 1
) AS _wf_anchor;

Expected:
{"where_count": 2, "where_rows": "1100011,10110", "window_check_count": 2}

Actual:
{"where_count": 1, "where_rows": "1100011", "window_check_count": 2}

[22 Apr 4:27] Chaithra Marsur Gopala Reddy

Hi QiFan Liu,

Thank you for the test case. Verified as described.