Bug #94548 Optimizer error evaluating JSON_Extract
Submitted: 4 Mar 15:31 Modified: 5 Mar 10:02
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7, 5.7.25, 8.0.15 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[4 Mar 15:31] Dave Pullin
Description:
a select does not evaluate a function if the column the function occurs it is not used, at least in the particular case below. However the function may have side effects and therefore needs to be evaluated regardless of whether the column is used.

// create a function
mysql> create function do_something(args longtext) returns longtext return "something";
Query OK, 0 rows affected (0.02 sec)

// run a select that does uses the column objectElement 

mysql> select object,"do something with " 
    -> /**/
    -> ,objectElement 
    -> /**/
    -> from (
    ->      select object
    ->      ,do_something(
    ->              json_extract(
    ->                     object
    ->                     ,'$.'||json_unquote(
    ->                               json_extract(
    ->                                    json_keys(object),'$['||0||']'
    ->                                    )
    ->                               )
    ->                          )
    ->                     )
    ->         as objectElement 
    ->         
    ->      from (
    ->         select json_object("A bad key","irrevant") as object 
    ->      ) as sample_data
    ->      
    ->  ) as myObject;
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 4.

// it failed because the path "$.A Bad Key" is invalid.
// now run the same select without refering the column.

mysql> select object,"do something with " 
    -> /**
   /*> ,objectElement 
   /*> /**/
    -> from (
    ->      select object
    ->      ,do_something(
    ->              json_extract(
    ->                     object
    ->                     ,'$.'||json_unquote(
    ->                               json_extract(
    ->                                    json_keys(object),'$['||0||']'
    ->                                    )
    ->                               )
    ->                          )
    ->                     )
    ->         as objectElement 
    ->         
    ->      from (
    ->         select json_object("A bad key","irrevant") as object 
    ->      ) as sample_data
    ->      
    ->  ) as myObject;
+---------------------------+--------------------+
| object                    | do something with  |
+---------------------------+--------------------+
| {"A bad key": "irrevant"} | do something with  |
+---------------------------+--------------------+
1 row in set (0.00 sec)

// It succeeded, because (it appears) the JSON_EXTRACT was not evualated
// and therefore the function do_something was not called and didn't have the // opportunity to do its side effect.

How to repeat:

create function do_something(args longtext) returns longtext return "something";

select object,"do something with " 
/**/
,objectElement 
/**/
from (
     select object
     ,do_something(
             json_extract(
                    object
                    ,'$.'||json_unquote(
                              json_extract(
                                   json_keys(object),'$['||0||']'
                                   )
                              )
                         )
                    )
        as objectElement 
        
     from (
        select json_object("A bad key","irrevant") as object 
     ) as sample_data
     
 ) as myObject;

select object,"do something with " 
/** commented out ...
,objectElement 
/**/
from (
     select object
     ,do_something(
             json_extract(
                    object
                    ,'$.'||json_unquote(
                              json_extract(
                                   json_keys(object),'$['||0||']'
                                   )
                              )
                         )
                    )
        as objectElement 
        
     from (
        select json_object("A bad key","irrevant") as object 
     ) as sample_data
     
 ) as myObject;

Suggested fix:

While I like the optimization for lots of other cases where I have functions that don't need to executed in analogous circumstances, I dont like this particular instance, mostly because it was hard to debug the real circumstance that this occurred it. 

And I realize that I can fix the code that builds the path that handles the case of a key with a space in it, but that's not the point.

So I don't know what you should do about it :-)
[5 Mar 10:02] Umesh Shastry
Hello Dave Pullin,

Thank you for the report and test case.
Verified as described with 5.7.25 and 8.0.15 builds.

regards,
Umesh