Description:
Prepared statement show different phenomena compared to direct execution SQL.
Direct execution use the expected index whether prepared use another.
How to repeat:
CREATE TABLE `test_json` (
`a` int NOT NULL,
`b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`c` json DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `key_idx_euip` (((cast(json_unquote(json_extract(`c`,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin))));
insert into test_json (a,b,c) value ( 1 , 'name' , '{"euip": "1"}' );
insert into test_json (a,b,c) value ( 2 , 'name1' , '{"euip": "2"}' );
insert into test_json (a,b,c) value ( 3 , 'name2' , '{"euip": "3"}' );
insert into test_json (a,b,c) value ( 4 , 'name4' , '{"euip": "4"}' );
insert into test_json (a,b,c) value ( 5 , 'name5' , '{"euip": "5"}' );
insert into test_json (a,b,c) value ( 10 , 'name' , '{"erip": "1"}' );
insert into test_json (a,b,c) value ( 11 , 'name' , '{"erip": "2"}' );
insert into test_json (a,b,c) value ( 12 , 'name' , '{"erip": "3"}' );
insert into test_json (a,b,c) value ( 13 , 'name4' , '{"erip": "4"}' );
---------------direct execut
explain select * from test_json where json_extract(c,"$.euip")='2' order by a limit 10;
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_json | NULL | ref | key_idx_euip | key_idx_euip | 123 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
---------------prepare unexpected 1
PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,?)=? order by a limit ?';
set @a="$.euip";set @b="2";set @c=10;
EXECUTE stmt1 USING @a,@b,@c;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=1.15 rows=9) (actual time=0.049..0.061 rows=1 loops=1)
-> Filter: (json_extract(test_json.c,'$.euip') = '2') (cost=1.15 rows=9) (actual time=0.048..0.059 rows=1 loops=1)
-> Index scan on test_json using PRIMARY (cost=1.15 rows=9) (actual time=0.027..0.038 rows=9 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
---------------prepare unexpected 2
PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,"$.euip")=? order by a limit ?';
set @b= '2';set @c=10;
EXECUTE stmt1 USING @b, @c;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=0.35 rows=1) (actual time=0.051..0.066 rows=1 loops=1)
-> Filter: ((cast(json_unquote(json_extract(c,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin) = '2') (cost=0.35 rows=1) (actual time=0.049..0.064 rows=1 loops=1)
-> Index scan on test_json using PRIMARY (cost=0.35 rows=9) (actual time=0.038..0.054 rows=9 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
---------------prepare expected
PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,"$.euip")="2" order by a limit ?';
set @c=10;
EXECUTE stmt1 USING @c;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=0.35 rows=1) (actual time=0.038..0.041 rows=1 loops=1)
-> Index lookup on test_json using key_idx_euip ((cast(json_unquote(json_extract(c,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin)='2') (cost=0.35 rows=1) (actual time=0.036..0.039 rows=1 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Description: Prepared statement show different phenomena compared to direct execution SQL. Direct execution use the expected index whether prepared use another. How to repeat: CREATE TABLE `test_json` ( `a` int NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` json DEFAULT NULL, PRIMARY KEY (`a`), KEY `key_idx_euip` (((cast(json_unquote(json_extract(`c`,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin)))); insert into test_json (a,b,c) value ( 1 , 'name' , '{"euip": "1"}' ); insert into test_json (a,b,c) value ( 2 , 'name1' , '{"euip": "2"}' ); insert into test_json (a,b,c) value ( 3 , 'name2' , '{"euip": "3"}' ); insert into test_json (a,b,c) value ( 4 , 'name4' , '{"euip": "4"}' ); insert into test_json (a,b,c) value ( 5 , 'name5' , '{"euip": "5"}' ); insert into test_json (a,b,c) value ( 10 , 'name' , '{"erip": "1"}' ); insert into test_json (a,b,c) value ( 11 , 'name' , '{"erip": "2"}' ); insert into test_json (a,b,c) value ( 12 , 'name' , '{"erip": "3"}' ); insert into test_json (a,b,c) value ( 13 , 'name4' , '{"erip": "4"}' ); ---------------direct execut explain select * from test_json where json_extract(c,"$.euip")='2' order by a limit 10; +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_json | NULL | ref | key_idx_euip | key_idx_euip | 123 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ---------------prepare unexpected 1 PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,?)=? order by a limit ?'; set @a="$.euip";set @b="2";set @c=10; EXECUTE stmt1 USING @a,@b,@c; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 10 row(s) (cost=1.15 rows=9) (actual time=0.049..0.061 rows=1 loops=1) -> Filter: (json_extract(test_json.c,'$.euip') = '2') (cost=1.15 rows=9) (actual time=0.048..0.059 rows=1 loops=1) -> Index scan on test_json using PRIMARY (cost=1.15 rows=9) (actual time=0.027..0.038 rows=9 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ---------------prepare unexpected 2 PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,"$.euip")=? order by a limit ?'; set @b= '2';set @c=10; EXECUTE stmt1 USING @b, @c; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 10 row(s) (cost=0.35 rows=1) (actual time=0.051..0.066 rows=1 loops=1) -> Filter: ((cast(json_unquote(json_extract(c,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin) = '2') (cost=0.35 rows=1) (actual time=0.049..0.064 rows=1 loops=1) -> Index scan on test_json using PRIMARY (cost=0.35 rows=9) (actual time=0.038..0.054 rows=9 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) ---------------prepare expected PREPARE stmt1 FROM 'explain analyze select * from test_json where json_extract(c,"$.euip")="2" order by a limit ?'; set @c=10; EXECUTE stmt1 USING @c; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 10 row(s) (cost=0.35 rows=1) (actual time=0.038..0.041 rows=1 loops=1) -> Index lookup on test_json using key_idx_euip ((cast(json_unquote(json_extract(c,_utf8mb4'$.euip')) as char(30) charset utf8mb4) collate utf8mb4_bin)='2') (cost=0.35 rows=1) (actual time=0.036..0.039 rows=1 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)