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 :-)
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 :-)