| Bug #77785 | Wrong results caused by path leg popping in JSON functions | ||
|---|---|---|---|
| Submitted: | 20 Jul 2015 15:51 | Modified: | 17 Aug 2015 15:08 |
| Reporter: | Rick Hillegas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S2 (Serious) |
| Version: | 5.7.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jul 2015 17:38]
Rick Hillegas
Posted by developer:
Here's another instance of this bug...
##########################################################
#
# item_json_insert.cc:Item_func_json_insert::val_json() line 2210
#
# if(args[i + 1]->null_value)
# {
# null_value= true;
# return false;
# }
#
##########################################################
create table tdoc( id int, doc json, new_value varchar( 10 ) );
insert into tdoc values
( 1, '{ "a": { "b": true } }', null ),
( 2, '{ "a": { "b": true } }', 'abc' );
# good
select id, json_insert( doc, '$.a.c', new_value ) from tdoc where id = 2;
# bad second row
select id, json_insert( doc, '$.a.c', new_value ) from tdoc order by id;
A cursory glance at the code did not turn up any problems in JSON_SET(), JSON_REPLACE(), JSON_ARRAY_INSERT(), or JSON_SEARCH().
[20 Jul 2015 17:52]
Rick Hillegas
Posted by developer: Add json tag.
[22 Jul 2015 19:07]
Rick Hillegas
Posted by developer: Add SR57RC and SRGAQUA tags because these tags are on https://clustra.no.oracle.com/orabugs/bug.php?id=21442624 and the fix for that bug will depend on this bugfix.
[17 Aug 2015 15:08]
Paul DuBois
Noted in 5.7.9, 5.8.0 changelogs. Certain JSON functions could return incorrect results when used in prepared statements which had path expression constants.
Description: The review of Bug#21450084 raised the concern that leg-popping is a brittle practice and there appear to be cases where leg-popping causes us to return bad results. The following code block in JSON_INSERT() is an example: if (hits.size() < 1) { // no unique object found at parent position, so bail out continue; } Leg-popping is performed by JSON_INSERT(), JSON_ARRAY_INSERT(), JSON_SET(), JSON_REPLACE(), and JSON_SEARCH(). We should fix these bugs. How to repeat: The code block cited above gives rise to the following wrong result: # standalone behavior is correct select json_insert( '{ "a": { "b": true } }', '$.a.c', false ); create table tdoc( id int, doc json ); insert into tdoc values ( 1, '[]' ), ( 2, '{ "a": { "b": true } }' ); # the second row should look like the standalone result above. but it doesn't select id, json_insert( doc, '$.a.c', false ) from tdoc order by id; The results of the standalone query are... +---------------------------------------------------------+ | json_insert( '{ "a": { "b": true } }', '$.a.c', false ) | +---------------------------------------------------------+ | {"a": {"b": true, "c": false}} | +---------------------------------------------------------+ ...but the corresponding result from the last SELECT is... | 2 | {"a": {"b": true}} | Suggested fix: Perhaps we should use a less brittle approach than leg-popping.