Description:
Hi, I noticed an inconsistency: when using a JSON type in a CASE WHEN clause followed by IS FALSE, the result depends on the specific values. The case when expression with JSON type ultimately returns a Longtext type, but directly doing IS FALSE to Longtext yields different results compared to first using CASE WHEN and then IS FALSE.
MySQL [test]> SELECT data, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END AS case_result, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END Is false AS is_matched FROM course_attributes WHERE id in( 1,2);
+----------------------+-------------+------------+
| data | case_result | is_matched |
+----------------------+-------------+------------+
| {"published": false} | false | 1 |
| {"published": true} | true | 0 |
+----------------------+-------------+------------+
2 rows in set (0.04 sec)
"CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END" will return longtext，However, the result of directly using longtext to do is false below is inconsistent with the above
MySQL [test]> select c1 is false, c2 is false from tt2;
+-------------+-------------+
| c1 is false | c2 is false |
+-------------+-------------+
| 1 | 0 |
| 1 | 0 |
+-------------+-------------+
2 rows in set, 1 warning (0.03 sec)
How to repeat:
Use the following example：
### Case 1: use case when
DROP TABLE IF EXISTS `course_attributes`;
CREATE TABLE `course_attributes` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `course_attributes` VALUES (1, '2', '{\"published\": false}');
INSERT INTO `course_attributes` VALUES (2, '3', '{\"published\": true}');
SELECT data,
CASE WHEN json_contains_path(data, 'one', '$.published')
THEN json_extract(data, '$.published') ELSE true END AS case_result,
CASE WHEN json_contains_path(data, 'one', '$.published')
THEN json_extract(data, '$.published') ELSE true END IS false AS is_matched
FROM course_attributes WHERE id in( 1,2);
### case 2: Direct is fasle
drop table if exists tt2;
create table tt2(c1 longtext, c2 json);
insert into tt2 values('false', 'false');
insert into tt2 values('true', 'true');
select c1 is false, c2 is false from tt2;
Suggested fix:
I can't understand why the results of the above two scenarios are inconsistent. I think the judgment logic of Isfalse should be consistent for the same type in any scenario. So is this a bug? Has Mysql made any special processing for Json internally?
© 2025, Oracle Corporation and/or its affiliates
Description: Hi, I noticed an inconsistency: when using a JSON type in a CASE WHEN clause followed by IS FALSE, the result depends on the specific values. The case when expression with JSON type ultimately returns a Longtext type, but directly doing IS FALSE to Longtext yields different results compared to first using CASE WHEN and then IS FALSE. MySQL [test]> SELECT data, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END AS case_result, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END Is false AS is_matched FROM course_attributes WHERE id in( 1,2); +----------------------+-------------+------------+ | data | case_result | is_matched | +----------------------+-------------+------------+ | {"published": false} | false | 1 | | {"published": true} | true | 0 | +----------------------+-------------+------------+ 2 rows in set (0.04 sec) "CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END" will return longtext，However, the result of directly using longtext to do is false below is inconsistent with the above MySQL [test]> select c1 is false, c2 is false from tt2; +-------------+-------------+ | c1 is false | c2 is false | +-------------+-------------+ | 1 | 0 | | 1 | 0 | +-------------+-------------+ 2 rows in set, 1 warning (0.03 sec) How to repeat: Use the following example： ### Case 1: use case when DROP TABLE IF EXISTS `course_attributes`; CREATE TABLE `course_attributes` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC; INSERT INTO `course_attributes` VALUES (1, '2', '{\"published\": false}'); INSERT INTO `course_attributes` VALUES (2, '3', '{\"published\": true}'); SELECT data, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END AS case_result, CASE WHEN json_contains_path(data, 'one', '$.published') THEN json_extract(data, '$.published') ELSE true END IS false AS is_matched FROM course_attributes WHERE id in( 1,2); ### case 2: Direct is fasle drop table if exists tt2; create table tt2(c1 longtext, c2 json); insert into tt2 values('false', 'false'); insert into tt2 values('true', 'true'); select c1 is false, c2 is false from tt2; Suggested fix: I can't understand why the results of the above two scenarios are inconsistent. I think the judgment logic of Isfalse should be consistent for the same type in any scenario. So is this a bug? Has Mysql made any special processing for Json internally?