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

JSON_TABLE() does not handle quoting of strings correctly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.4
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:
      windows and linux

      Description

      There is no way to quote a string with quotes in it correctly.

      Suppose you're working with the string:

      The book's title is "Lord of the Rings"

      SELECT 
      *
      FROM 
      JSON_TABLE(
      	'["The book\'s title is \"Lord of the Rings\""]',
      	'$[*]' 
      	COLUMNS (
      		`text` TEXT PATH '$[0]'
      	)
      ) AS t
      

      Results in a syntax error

      SELECT 
      *
      FROM 
      JSON_TABLE(
      	'["The book\'s title is \\\"Lord of the Rings\\\""]',
      	'$[*]' 
      	COLUMNS (
      		`text` TEXT PATH '$[0]'
      	)
      ) AS t
      

      Returns
      The book's title is \"Lord of the Rings\"

      SELECT 
      *
      FROM 
      JSON_TABLE(
      	'[\"The book\'s title is \\\"Lord of the Rings\\\"\"]',
      	'$[*]' 
      	COLUMNS (
      		`text` TEXT PATH '$[0]'
      	)
      ) AS t
      

      Also returns
      The book's title is \"Lord of the Rings\"

      I believe the previous one is most syntactically correct

      And even worse, this happens

      SELECT 
      *
      FROM 
      JSON_TABLE(
      	'[{\"text\":\"The book\'s title is \\\"Lord of the Rings\\\"\"}]',
      	'$[*]' 
      	COLUMNS (
      		`text` VARCHAR(39) PATH '$.text'
      	)
      ) AS t
      

      Returns the `text` field as:
      The book's title is \"Lord of the Rings

      (Yes, the trailing quote is missing)


      It seems like this is somewhat related to other JSON related bugs such as MDEV-13701 and MDEV-27018. Ultimately it seems like there is weirdness with how "strings" and "json" data are handled internally. For example a traditional string vs a json string. From a developer's point of view, if I have a string, I don't care whether it's JSON or not (other than escaping). When I get it back in my output, I need it in it's raw format with no escaping chars.... doesn't matter if it's a text field in a normal select or a property of a JSON object (I still expect to have to decode the JSON object normally, but once that's done on the top level object all sub properties should be in their raw states). It seems like this would be the case too internally. Sometimes a JSON strings need switched out for raw strings and vice versa. It shouldn't be up to the developer because one of the big selling points of JSON is that it's data of unknown shape. So I don't always know if something is a string or not ahead of time. But I digress. In the examples above, as far as I can tell, there is no way to formulate a query to put the data in a usable form.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              noyearzero Ryan Leadenham
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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