CASE WHEN JSON_EXTRACT numeric comparisons fail

[dengn]

Title

CASE WHEN using JSON_EXTRACT for numeric comparisons fails with ERROR 20203

Environment

• MatrixOne cluster: freetier-01.cn-hangzhou.cluster.cn-dev.matrixone.tech:6001
• Database: industry_radar_test
• Client: mysql CLI
• Test batch: report_test_batch_334_case_when_mix_20251125_144310.md (Case 3335)

Schema & SQL

DROP TABLE IF EXISTS case_when_json_bug;
CREATE TABLE case_when_json_bug (
    id INT PRIMARY KEY,
    profile JSON,
    segment VARCHAR(20)
);
INSERT INTO case_when_json_bug VALUES
(1, '{"age": 35, "city": "Shanghai"}', ''),
(2, '{"age": 24, "city": "Beijing"}', ''),
(3, '{"age": 42, "city": "Shenzhen"}', '');

-- Update with CASE WHEN comparing JSON_EXTRACT result to numeric literals
UPDATE case_when_json_bug
SET segment = CASE
        WHEN JSON_EXTRACT(profile, '$.age') >= 40 THEN 'SENIOR'
        WHEN JSON_EXTRACT(profile, '$.age') BETWEEN 30 AND 39 THEN 'MID'
        ELSE 'YOUNG'
    END;

-- Query with CASE WHEN reading the updated segment
SELECT id,
       JSON_EXTRACT(profile, '$.city') AS city,
       CASE
           WHEN segment = 'SENIOR' THEN 'Priority'
           WHEN segment = 'MID' THEN 'Standard'
           ELSE 'Growth'
       END AS policy
FROM case_when_json_bug
ORDER BY id;

Actual Result

ERROR 20203 (HY000) at line 11: invalid argument operator cast, bad value [JSON BIGINT]

The failure happens inside the UPDATE statement before any row is modified. The same error is emitted for every JSON CASE WHEN scenario that compares a JSON_EXTRACT value to numeric constants (e.g., >= 40, BETWEEN 30 AND 39).

Expected Behavior

MySQL treats JSON_EXTRACT(profile, '$.age') as numeric when the JSON scalar is numeric, allowing comparisons and CASE expressions. The UPDATE should complete successfully and set segment to SENIOR/MID/YOUNG based on the numeric age.

Impact

• Prevents using CASE WHEN with JSON columns for numeric bucketing and segmentation.
• Blocks analytics workloads that rely on JSON attributes for classification or filtering.
• Affects both UPDATE and SELECT statements where CASE WHEN compares JSON scalar values to numeric literals.

Additional Notes

• Plain SELECT * FROM case_when_json_bug WHERE JSON_EXTRACT(profile, '$.age') > 30; fails with the same error, so the root cause appears to be missing numeric casting for JSON scalars.
• This issue is reproducible on every run (6/6 failures across different batches).