Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.16, 10.3.9, 10.2(EOL), 10.3(EOL)
-
None
Description
Unexpected behavior when searching with a user-defined variable.
MariaDB (see dbfiddle):
MariaDB [(none)]> SELECT VERSION(); |
+------------------------------------------+ |
| VERSION() |
|
+------------------------------------------+ |
| 10.3.9-MariaDB-1:10.3.9+maria~bionic-log |
|
+------------------------------------------+ |
1 row in set (0.000 sec) |
|
MariaDB [(none)]> SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', |
-> @`value` := 'AB'; |
Query OK, 0 rows affected (0.000 sec) |
|
MariaDB [(none)]> SELECT JSON_SEARCH(@`json`, 'one', @`value`); |
+---------------------------------------+ |
| JSON_SEARCH(@`json`, 'one', @`value`) | |
+---------------------------------------+ |
| NULL | |
+---------------------------------------+ |
1 row in set, 1 warning (0.000 sec) |
|
MariaDB [(none)]> SHOW WARNINGS;
|
+---------+------+---------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+---------------------------------------------------------------------------------+ |
| Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_search' at position 1 | |
+---------+------+---------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
MariaDB [(none)]> SELECT JSON_SEARCH(JSON_UNQUOTE(@`json`), 'one', @`value`); |
+-----------------------------------------------------+ |
| JSON_SEARCH(JSON_UNQUOTE(@`json`), 'one', @`value`) | |
+-----------------------------------------------------+ |
| "$[2].C" | |
+-----------------------------------------------------+ |
1 row in set (0.000 sec) |
MySQL (see dbfiddle):
mysql> SELECT VERSION(); |
+-----------+ |
| VERSION() |
|
+-----------+ |
| 8.0.12 |
|
+-----------+ |
1 row in set (0.000 sec) |
|
mysql> SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', |
-> @`value` := 'AB'; |
Query OK, 0 rows affected (0.000 sec) |
|
mysql> SELECT JSON_SEARCH(@`json`, 'one', @`value`); |
+---------------------------------------+ |
| JSON_SEARCH(@`json`, 'one', @`value`) | |
+---------------------------------------+ |
| "$[2].C" | |
+---------------------------------------+ |
1 row in set (0.000 sec) |
|
mysql> SHOW WARNINGS;
|
Empty set (0.000 sec) |