--echo This is used to demonstrate the use of JSON parameter values with --echo SQL prepared statements. # create table t1(j json); set @int=123; set @dec=3.14; set @flt=3.14E1; set @intstr='123'; set @quotint='"123"'; set @decstr='3.14'; set @fltstr='3.14E1'; set @strstr='xyz'; set @quotstr='"xyz"'; set @ts='2010-12-01 18:44:25'; set @null=null; set @json='{"int" : 123, "dec" : 3.14, "flt" : 3.14E1, "str" : "xyz", "array" : [1, 2, 4]}'; --echo Parameters are treated slightly different when they are resolved as being --echo of type JSON compared to when they are CAST as type JSON. The following --echo two prepared statements are used to demonstrate this: prepare ps_insert from 'insert into t1 values (?)'; prepare ps_insert_cast from 'insert into t1 values (cast(? as json))'; --echo The ps_insert statement fails with integer, decimal, float and datetime --echo values because Field_json::store() is not implemented for such values. --echo Integer parameter: With CAST, the integer value is converted to a --echo scalar JSON value, without cast the parameter is rejected as invald JSON. execute ps_insert_cast using @int; prepare ps_insert from 'insert into t1 values (?)'; --error ER_INVALID_JSON_TEXT execute ps_insert using @int; --echo Decimal parameter: With CAST, the decimal value is converted to a --echo scalar JSON value, without cast the parameter is rejected as invald JSON. execute ps_insert_cast using @dec; prepare ps_insert from 'insert into t1 values (?)'; --error ER_INVALID_JSON_TEXT execute ps_insert using @dec; --echo Float parameter: With CAST, the float value is converted to a --echo scalar JSON value, without cast the parameter is rejected as invald JSON. execute ps_insert_cast using @flt; prepare ps_insert from 'insert into t1 values (?)'; --error ER_INVALID_JSON_TEXT execute ps_insert using @flt; --echo String parameter containing an integer: Both statements accept the --echo parameter as a scalar JSON value. execute ps_insert_cast using @intstr; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @intstr; --echo String parameter containing a decimal: Both statements accept the --echo parameter as a scalar JSON value. execute ps_insert_cast using @decstr; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @decstr; --echo String parameter containing a float: Both statements accept the --echo parameter as a scalar JSON value. execute ps_insert_cast using @fltstr; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @fltstr; --echo String parameter containing a non-quoted string: None of the statements --echo will recognize this: --error ER_INVALID_JSON_TEXT_IN_PARAM execute ps_insert_cast using @strstr; prepare ps_insert from 'insert into t1 values (?)'; --error ER_INVALID_JSON_TEXT execute ps_insert using @strstr; --echo String parameter containing a quoted string: Both statements accept --echo the parameter as a scalar JSON value. execute ps_insert_cast using @quotstr; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @quotstr; --echo String parameter containing a timestamp literal: None of the statements --echo will recognize this: --error ER_INVALID_JSON_TEXT_IN_PARAM execute ps_insert_cast using @ts; prepare ps_insert from 'insert into t1 values (?)'; --error ER_INVALID_JSON_TEXT execute ps_insert using @ts; --echo Parameter that is a NULL value: accepted with both statements: execute ps_insert_cast using @null; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @null; --echo String parameter that is a JSON object: accepted with both statements: execute ps_insert_cast using @json; prepare ps_insert from 'insert into t1 values (?)'; execute ps_insert using @json; DEALLOCATE PREPARE ps_insert_cast; DEALLOCATE PREPARE ps_insert; SELECT * FROM t1; --echo Compare JSON expressions with JSON parameters. --echo First using regular syntax. prepare ps_get_int from "select * from t1 where json_extract(j, '$.int') = ?"; prepare ps_get_dec from "select * from t1 where json_extract(j, '$.dec') = ?"; prepare ps_get_flt from "select * from t1 where json_extract(j, '$.flt') = ?"; prepare ps_get_str from "select * from t1 where json_extract(j, '$.str') = ?"; execute ps_get_int using @int; execute ps_get_int using @intstr; execute ps_get_dec using @dec; execute ps_get_dec using @decstr; execute ps_get_flt using @flt; execute ps_get_flt using @fltstr; execute ps_get_str using @strstr; execute ps_get_str using @quotstr; DEALLOCATE PREPARE ps_get_int; DEALLOCATE PREPARE ps_get_flt; DEALLOCATE PREPARE ps_get_dec; DEALLOCATE PREPARE ps_get_str; --echo Repeat comparison operations using "->" operator. Results should be same. prepare ps_get_int from "select * from t1 where j->'$.int' = ?"; prepare ps_get_dec from "select * from t1 where j->'$.dec' = ?"; prepare ps_get_flt from "select * from t1 where j->'$.flt' = ?"; prepare ps_get_str from "select * from t1 where j->'$.str' = ?"; execute ps_get_int using @int; execute ps_get_int using @intstr; execute ps_get_dec using @dec; execute ps_get_dec using @decstr; execute ps_get_flt using @flt; execute ps_get_flt using @fltstr; execute ps_get_str using @intstr; execute ps_get_str using @strstr; execute ps_get_str using @quotstr; DEALLOCATE PREPARE ps_get_int; DEALLOCATE PREPARE ps_get_flt; DEALLOCATE PREPARE ps_get_dec; DEALLOCATE PREPARE ps_get_str; --echo Repeat comparison operations using "->>" operator, ie return an unquoted --echo value from json_extract(). All executions succeed, since the value from --echo json_extract() is converted to a character string, which can be compared --echo to the parameter value. However, using @quotstr finds no rows. prepare ps_get_int from "select * from t1 where j->>'$.int' = ?"; prepare ps_get_dec from "select * from t1 where j->>'$.dec' = ?"; prepare ps_get_flt from "select * from t1 where j->>'$.flt' = ?"; prepare ps_get_str from "select * from t1 where j->>'$.str' = ?"; execute ps_get_int using @int; execute ps_get_int using @intstr; execute ps_get_dec using @dec; execute ps_get_dec using @decstr; execute ps_get_flt using @flt; execute ps_get_flt using @fltstr; execute ps_get_str using @strstr; execute ps_get_str using @quotstr; DEALLOCATE PREPARE ps_get_int; DEALLOCATE PREPARE ps_get_flt; DEALLOCATE PREPARE ps_get_dec; DEALLOCATE PREPARE ps_get_str; DROP TABLE t1; --echo # Bug#31433168: Infinite recursion when evaluating parameter PREPARE ps FROM 'SELECT CAST(? AS JSON)'; EXECUTE ps USING @undefined_variable; SET @var = '{"a" : 1}'; EXECUTE ps USING @var; EXECUTE ps USING @undefined_variable; DEALLOCATE PREPARE ps; --echo # Bug#31406724: Crash in sql_scalar_to_json after many recursive calls CREATE TABLE t(a INTEGER); INSERT INTO t VALUES (0),(1),(2); PREPARE s FROM 'SELECT * FROM t WHERE JSON_LENGTH(?, a BETWEEN ? AND ?)'; EXECUTE s USING @undefined_variable, @undefined_variable, @undefined_variable; DEALLOCATE PREPARE s; DROP TABLE t; --echo # Bug#31667405: Incorrect prepared statement outcome with numeric strings in JSON # Ensure that parameter within e.g JSON_EXTRACT is treated as a JSON object. PREPARE ps FROM 'SELECT CAST(''"123"'' AS JSON) = JSON_EXTRACT(?, ''$'')'; EXECUTE ps USING @intstr; EXECUTE ps USING @quotint; DEALLOCATE PREPARE ps; PREPARE ps FROM 'SELECT CAST(''"123"'' AS JSON) IN (?, ?)'; EXECUTE ps USING @intstr, @intstr; EXECUTE ps USING @quotint, @quotint; DEALLOCATE PREPARE ps;