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

JSON_VALUE and JSON_EXTRACT doesn't handle dash (-) as first character in key

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 11.0.2
    • 11.0.5, 11.1.4, 11.2.3
    • JSON
    • None
    • MariaDB 11.0.2 running on MacOS; installed using homebrew

    Description

      11.0.2 doesn't seem to handle dashes when supplied as the first character of the JSON key. In JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is provided using the expected quoting.

      CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
      		 
      	SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'
      		 
      ;
      		 
       
      		 
      SELECT	JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
      		 
      	JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
      		 
      	JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
      		 
      	JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
      		 
      FROM jsonTest
      		 
      ;

      t1_path t1_result t2_path t2_result t3_path t3_result t4_path t4_result
      "$.-1234" NULL "$.12-34" else "$.1234-" and "$.1234" match

      Referencing related tickets that don't quite solve this scenario.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29381 JSON paths containing dashes are reported as syntax errors in procedures

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29586 JSON_VALUE and JSON_EXTRACT doesn't handle special characters

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            shenk Dave Shenk created issue -
            shenk Dave Shenk made changes -
            Field Original Value New Value
            shenk Dave Shenk made changes -
            shenk Dave Shenk made changes -
            Description 11.0.2 doesn't seem to handle dashes when supplied as the first character of the JSON key. In JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is provided using the expected quoting.


            {code:sql}
            CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
            SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'
            ;

            SELECT * FROM jsonTest;

            DROP TABLE IF EXISTS jsonTest;

            SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
            JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
            JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
            JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
            FROM jsonTest
            ;
            {code}


            || t1_path || t1_result || t2_path || t2_result || t3_path || t3_result || t4_path || t4_result |
            | "$.-1234" | NULL | "$.12-34" | else | "$.1234-" | and | "$.1234" | match |

            Referencing related tickets that don't quite solve this scenario.             		11.0.2 doesn't seem to handle dashes when supplied as the first character of the JSON key. In JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is provided using the expected quoting.


            {code:sql}
            CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
            SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'
            ;

            SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
            JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
            JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
            JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
            FROM jsonTest
            ;
            {code}


            || t1_path || t1_result || t2_path || t2_result || t3_path || t3_result || t4_path || t4_result |
            | "$.-1234" | NULL | "$.12-34" | else | "$.1234-" | and | "$.1234" | match |

            Referencing related tickets that don't quite solve this scenario.
            shenk Dave Shenk made changes -
            shenk Dave Shenk made changes -
            shenk Dave Shenk made changes -
            shenk Dave Shenk made changes -
            shenk Dave Shenk made changes -
            Summary JSON_VALUE and JSON_EXTRACT doesn't dash (-) as first character in key JSON_VALUE and JSON_EXTRACT doesn't handle dash (-) as first character in key
            shenk Dave Shenk made changes -
            Description 11.0.2 doesn't seem to handle dashes when supplied as the first character of the JSON key. In JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is provided using the expected quoting.


            {code:sql}
            CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
            SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'
            ;

            SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
            JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
            JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
            JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
            FROM jsonTest
            ;
            {code}


            || t1_path || t1_result || t2_path || t2_result || t3_path || t3_result || t4_path || t4_result |
            | "$.-1234" | NULL | "$.12-34" | else | "$.1234-" | and | "$.1234" | match |

            Referencing related tickets that don't quite solve this scenario.             		11.0.2 doesn't seem to handle dashes when supplied as the first character of the JSON key. In JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is provided using the expected quoting.


            {code:sql}
            CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS
            SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'
            ;

            SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_result,
            JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result,
            JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result,
            JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result
            FROM jsonTest
            ;
            {code}


            || t1_path || t1_result || t2_path || t2_result || t3_path || t3_result || t4_path || t4_result |
            | "$.-1234" | NULL | "$.12-34" | else | "$.1234-" | and | "$.1234" | match |

            Referencing related tickets that don't quite solve this scenario.
            serg Sergei Golubchik made changes -
            Fix Version/s 11.0 [ 28320 ]
            serg Sergei Golubchik made changes -
            Assignee Rucha Deodhar [ rucha174 ]
            rucha174 Rucha Deodhar made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            rucha174 Rucha Deodhar made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            rucha174 Rucha Deodhar made changes -
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            rucha174 Rucha Deodhar added a comment -

            patch: https://github.com/MariaDB/server/commit/9973c6d0898b3369869ec9ee8d84068c8e9c4111

            rucha174 Rucha Deodhar added a comment - patch: https://github.com/MariaDB/server/commit/9973c6d0898b3369869ec9ee8d84068c8e9c4111
            rucha174 Rucha Deodhar made changes -
            Assignee Rucha Deodhar [ rucha174 ] Alexey Botchkov [ holyfoot ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            holyfoot Alexey Botchkov added a comment -

            ok to push.

            holyfoot Alexey Botchkov added a comment - ok to push.
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Rucha Deodhar [ rucha174 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.0 [ 28320 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]
            rucha174 Rucha Deodhar added a comment -

            julien.fritsch , yes they were missing. thanks for adding.

            rucha174 Rucha Deodhar added a comment - julien.fritsch , yes they were missing. thanks for adding.
            dbart Daniel Bartholomew made changes -
            Fix Version/s 11.0.5 [ 29520 ]
            Fix Version/s 11.1.4 [ 29024 ]
            Fix Version/s 11.2.3 [ 29521 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]

            People

              rucha174 Rucha Deodhar
              shenk Dave Shenk
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.