Description:
MySQL returns an incorrect result in a window-function query when evaluating `expr <= ANY (subquery)` over `TINYTEXT` values that are implicitly converted to numbers. 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 drops one qualifying row and returns only `"X"` instead of the expected `"1,X"`.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c1 TINYTEXT);
INSERT INTO t0 (c1) VALUES ('1'), ('2'), ('X');
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 c1 + c1 <= ANY (SELECT c1 FROM t0)
),
'where_rows',
COALESCE(
(
SELECT GROUP_CONCAT(c1 ORDER BY c1 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 c1 + c1 <= ANY (SELECT c1 FROM t0)
),
''
),
'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 (c1 + c1 <= ANY (SELECT c1 FROM t0))
) 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": "1,X", "window_check_count": 2}
Actual:
{"where_count": 1, "where_rows": "X", "window_check_count": 2}
Description: MySQL returns an incorrect result in a window-function query when evaluating `expr <= ANY (subquery)` over `TINYTEXT` values that are implicitly converted to numbers. 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 drops one qualifying row and returns only `"X"` instead of the expected `"1,X"`. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 (c1 TINYTEXT); INSERT INTO t0 (c1) VALUES ('1'), ('2'), ('X'); 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 c1 + c1 <= ANY (SELECT c1 FROM t0) ), 'where_rows', COALESCE( ( SELECT GROUP_CONCAT(c1 ORDER BY c1 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 c1 + c1 <= ANY (SELECT c1 FROM t0) ), '' ), '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 (c1 + c1 <= ANY (SELECT c1 FROM t0)) ) 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": "1,X", "window_check_count": 2} Actual: {"where_count": 1, "where_rows": "X", "window_check_count": 2}