Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17018

JSON_SEARCH and User-Defined Variables

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.2.16, 10.3.9
    • Fix Version/s: 10.2.18
    • Component/s: JSON, Variables
    • Labels:
      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)
      

        Attachments

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            wchiquito William Chiquito
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: