Description:
If the parameters of the json_search function contain a scalar subquery, and the subquery_to_derived switch is turned on, the result is incorrect。
How to repeat:
create table t_doc (id int, doc JSON );
insert into t_doc values
(1, '{"match11": "eleven", "match12": "element", "notMatch": "elven" }' ),
(2, '{"match21": "eleven", "match22": "element", "notMatch": "elven" }' ),
(3, '{"match31": "tw%elve", "match32": "tw%ilight", "notMatch": "twitter" }' );
create table t_search_string (id int, search_string varchar(20) );
insert into t_search_string values
(1, 'ele%' ),
(2, 'ele%' ),
(3, 'tw|%%' );
select t.id, json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' )
from t_doc t order by id;
+------+-------------------------------------------------------------------------------------------------+
| id | json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) |
+------+-------------------------------------------------------------------------------------------------+
| 1 | ["$.match11", "$.match12"] |
| 2 | ["$.match21", "$.match22"] |
| 3 | ["$.match31", "$.match32"] |
+------+-------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
set optimizer_switch='subquery_to_derived=on';
mysql> select t.id, json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) from t_doc t order by id;
+------+-------------------------------------------------------------------------------------------------+
| id | json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) |
+------+-------------------------------------------------------------------------------------------------+
| 1 | NULL |
| 1 | NULL |
| 2 | NULL |
| 2 | NULL |
| 3 | NULL |
| 3 | NULL |
+------+-------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
Description: If the parameters of the json_search function contain a scalar subquery, and the subquery_to_derived switch is turned on, the result is incorrect。 How to repeat: create table t_doc (id int, doc JSON ); insert into t_doc values (1, '{"match11": "eleven", "match12": "element", "notMatch": "elven" }' ), (2, '{"match21": "eleven", "match22": "element", "notMatch": "elven" }' ), (3, '{"match31": "tw%elve", "match32": "tw%ilight", "notMatch": "twitter" }' ); create table t_search_string (id int, search_string varchar(20) ); insert into t_search_string values (1, 'ele%' ), (2, 'ele%' ), (3, 'tw|%%' ); select t.id, json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) from t_doc t order by id; +------+-------------------------------------------------------------------------------------------------+ | id | json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) | +------+-------------------------------------------------------------------------------------------------+ | 1 | ["$.match11", "$.match12"] | | 2 | ["$.match21", "$.match22"] | | 3 | ["$.match31", "$.match32"] | +------+-------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) set optimizer_switch='subquery_to_derived=on'; mysql> select t.id, json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) from t_doc t order by id; +------+-------------------------------------------------------------------------------------------------+ | id | json_search( doc, 'all', (select search_string from t_search_string s where s.id = t.id), '|' ) | +------+-------------------------------------------------------------------------------------------------+ | 1 | NULL | | 1 | NULL | | 2 | NULL | | 2 | NULL | | 3 | NULL | | 3 | NULL | +------+-------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)