| Bug #106180 | case expression not equivalent to if expression for a json | ||
|---|---|---|---|
| Submitted: | 16 Jan 2022 20:50 | Modified: | 18 Jan 2022 8:17 |
| Reporter: | Dave Pullin (Basic Quality Contributor) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7.31, 8.0.27, 5.7.36 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[17 Jan 2022 6:44]
MySQL Verification Team
Hello Dave, Thank you for the report and test case. Verified as described. regards, Umesh
[17 Jan 2022 13:14]
Roy Lyseng
Posted by developer: Notice that this is a problem in 5.7 only. But also consider that the expression "json->'$.test' is true" may not give the result that you expect. Also consider replacing JSON_EXTRACT() with JSON_VALUE(), which has improved control over NULL value handling and type conversions in general.
[18 Jan 2022 8:17]
Knut Anders Hatlen
This bug was fixed in MySQL 8.0.1 with the following commit message:
Bug#22887227: ASSERTION FAILED: THIS_TYPE != JSON_DOM::J_ERROR
Problem:
Item_func_case::val_json() did not always set the null_value flag when
the CASE expression evaluated to NULL.
Fix:
Update Item_func_case's null_value flag after evaluating the THEN or
ELSE clause of the CASE expression.
As Roy mentioned, the result on 8.0 may still not be quite what you had expected. That's because "WL#12358: Ensure that all predicates in SQL conditions are complete" adds an internal rewrite of "json->'$.test' is true" to "(json->'$.test' <> 0) is true". Combined with the somewhat puzzling handling of the JSON null literal in JSON_EXTRACT (I think we have other bug reports for that) this makes IS TRUE evaluate to TRUE even when the operand is a JSON null literal:
MySQL 8.0 now returns the following result:
+----------+----------------+-------+----------------+
| testcase | orig_json | truth | new_json |
+----------+----------------+-------+----------------+
| when1 | {"test": null} | null | {"test": true} |
| when2 | {"test": true} | true | {"test": true} |
| if1 | {"test": null} | null | {"test": true} |
| if2 | {"test": true} | true | {"test": true} |
+----------+----------------+-------+----------------+
4 rows in set, 4 warnings (0,00 sec)
Warning (Code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
Warning (Code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
Warning (Code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
Warning (Code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
As suggested by Roy, and by the warnings, JSON_VALUE behaves better in this respect.
Description: case when X then Y else Z end ought to give the same result as if(X,Y,Z) They do not, in the following peculiar example. I think it is related to the JSON operations, in particular the JSON_OBJECT() should not return null no matter what the result of the case expression is. mysql> select "when1" as testcase ,json as orig_json,json->'$.test' as truth, json_object( -> "test",case when json->'$.test' is true then cast(true as json) else null end -> ) as new_json -> from (select json_object('test',null) as json) as data -> union all -> select "when2" as testcase ,json as orig_json,json->'$.test' as truth, json_object( -> "test",case when json->'$.test' is true then cast(true as json) else null end -> ) as new_json -> from (select json_object('test',true) as json) as data -> union all -> select "if1" as testcase,json as orig_json,json->'$.test' as truth, json_object( -> "test",if( json->'$.test' is true , cast(true as json),null) -> ) as new_json -> from (select json_object('test',null) as json) as data -> union all -> select "if2" as testcase,json as orig_json,json->'$.test' as truth, json_object( -> "test",if( json->'$.test' is true , cast(true as json) ,null) -> ) as new_json -> from (select json_object('test',true) as json) as data; +----------+----------------+-------+----------------+ | testcase | orig_json | truth | new_json | +----------+----------------+-------+----------------+ | when1 | {"test": null} | null | NULL | | when2 | {"test": true} | true | {"test": true} | | if1 | {"test": null} | null | {"test": null} | | if2 | {"test": true} | true | {"test": true} | +----------+----------------+-------+----------------+ 4 rows in set, 2 warnings (0.00 sec) How to repeat: select "when1" as testcase ,json as orig_json,json->'$.test' as truth, json_object( "test",case when json->'$.test' is true then cast(true as json) else null end ) as new_json from (select json_object('test',null) as json) as data union all select "when2" as testcase ,json as orig_json,json->'$.test' as truth, json_object( "test",case when json->'$.test' is true then cast(true as json) else null end ) as new_json from (select json_object('test',true) as json) as data union all select "if1" as testcase,json as orig_json,json->'$.test' as truth, json_object( "test",if( json->'$.test' is true , cast(true as json),null) ) as new_json from (select json_object('test',null) as json) as data union all select "if2" as testcase,json as orig_json,json->'$.test' as truth, json_object( "test",if( json->'$.test' is true , cast(true as json) ,null) ) as new_json from (select json_object('test',true) as json) as data;