[MDEV-27412] JSON_TABLE doesn't properly unquote strings - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.5
Fix Version/s: 10.6.20, 10.11.10, 11.2.6, 11.4.4
Component/s: JSON
Labels:
None
Environment:
Ubuntu 20.04

Description

I have trouble with the `JSON_TABLE` function in *MariaDB 10.6.5*. This is my query:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT

  data

FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;}}

What I get back is the XML string, but not properly unescaped:

<root language=\"de\"></root>

When I use then `JSON_VALUE` instead:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT

  JSON_VALUE(@DATA, '$[0].Data');

then I get back the correctly unescaped string:

<root language="de"></root>

When I do the same on a MySQL *8.0.26* server, it works as expected:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT

  data

FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

-- correct:

-- <root language="de"></root>

SELECT

  JSON_VALUE(@data, '$[0].Data');

-- correct:

-- <root language="de"></root>

It looks to me that this difference in behaviour is a bug in MariaDB's side.

Attachments

Issue Links

relates to

MDEV-27346 JSON_TABLE() does not handle quoting of strings correctly

Open

Activity

Ascending order - Click to sort in descending order

Louis Brauer created issue - 2022-01-03 11:23

Louis Brauer made changes - 2022-01-03 11:24

Field	Original Value	New Value
Description	I have trouble with the `JSON_TABLE` function in MariaDB 10.6.5. This is my query: {{SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT data FROM JSON_TABLE (@data, '$[]' COLUMNS (data text PATH '$.Data')) AS t; }} What I get back is the XML string, but not properly unescaped: {{<root language=\"de\"></root>}} When I use then `JSON_VALUE` instead: {{SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT JSON_VALUE(@DATA, '$[0].Data');}} then I get back the correctly unescaped string: {{<root language="de"></root>}} When I do the same on a MySQL 8.0.26* server, it works as expected: {{ SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT data FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t; -- correct: -- <root language="de"></root> SELECT JSON_VALUE(@data, '$[0].Data'); -- correct: -- <root language="de"></root> }} It looks to me that this difference in behaviour is a bug in MariaDB's side.	I have trouble with the `JSON_TABLE` function in MariaDB 10.6.5. This is my query: {code:sql} SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT data FROM JSON_TABLE (@data, '$[]' COLUMNS (data text PATH '$.Data')) AS t;}} {code} What I get back is the XML string, but not properly unescaped: {{<root language=\"de\"></root>}} When I use then `JSON_VALUE` instead: {{SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT JSON_VALUE(@DATA, '$[0].Data');}} then I get back the correctly unescaped string: {{<root language="de"></root>}} When I do the same on a MySQL 8.0.26* server, it works as expected: {{ SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT data FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t; -- correct: -- <root language="de"></root> SELECT JSON_VALUE(@data, '$[0].Data'); -- correct: -- <root language="de"></root> }} It looks to me that this difference in behaviour is a bug in MariaDB's side.

Louis Brauer made changes - 2022-01-03 11:25

Description

I have trouble with the `JSON_TABLE` function in **MariaDB 10.6.5**. This is my query:

{code:sql}
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;}}
{code}

What I get back is the XML string, but not properly unescaped:

{{<root language=\"de\"></root>}}

When I use then `JSON_VALUE` instead:

{{SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  JSON_VALUE(@DATA, '$[0].Data');}}

then I get back the correctly unescaped string:

{{<root language="de"></root>}}

When I do the same on a MySQL **8.0.26** server, it works as expected:

{{
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

-- correct:
-- <root language="de"></root>

SELECT
  JSON_VALUE(@data, '$[0].Data');

-- correct:
-- <root language="de"></root>
}}

It looks to me that this difference in behaviour is a bug in MariaDB's side.

I have trouble with the `JSON_TABLE` function in **MariaDB 10.6.5**. This is my query:

{code:sql}
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;}}
{code}

What I get back is the XML string, but not properly unescaped:

{code:xml}
<root language=\"de\"></root>
{code}

When I use then `JSON_VALUE` instead:

{code:sql}
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  JSON_VALUE(@DATA, '$[0].Data');
{code}

then I get back the correctly unescaped string:

{code:xml}
<root language="de"></root>
{code}

When I do the same on a MySQL **8.0.26** server, it works as expected:

{code:sql}
SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT
  data
FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

-- correct:
-- <root language="de"></root>

SELECT
  JSON_VALUE(@data, '$[0].Data');

-- correct:
-- <root language="de"></root>
{code}

It looks to me that this difference in behaviour is a bug in MariaDB's side.

Louis Brauer made changes - 2022-01-04 19:59

Link

This issue relates to MDEV-27346 [ MDEV-27346 ]

Ralf Gebhardt made changes - 2022-01-05 15:55

Assignee

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2022-01-05 16:41

Assignee

Oleksandr Byelkin [ sanja ]

Rucha Deodhar [ rucha174 ]

Sergei Golubchik made changes - 2022-02-07 17:45

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Bernd Liebermann added a comment - 2022-06-19 07:11

This issue is a show stopper for our project at the moment. I hope it gets addressed soon.

Bernd Liebermann added a comment - 2022-06-19 07:11 This issue is a show stopper for our project at the moment. I hope it gets addressed soon.

Julien Fritsch made changes - 2023-03-03 17:26

Fix Version/s

10.7 [ 24805 ]

Ian Gilfillan made changes - 2023-12-27 00:53

Priority

Major [ 3 ]

Critical [ 2 ]

Rucha Deodhar made changes - 2024-02-12 07:06

Priority

Critical [ 2 ]

Major [ 3 ]

Kevin Shenk added a comment - 2024-05-03 18:27

This is a painful bug. Has anyone found a workaround?

Kevin Shenk added a comment - 2024-05-03 18:27 This is a painful bug. Has anyone found a workaround?

Kevin Shenk added a comment - 2024-05-03 18:44 - edited

I guess the best workaround for now is:

SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]';

SELECT

  REPLACE(data, "\\", "") as data

FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t;

Kevin Shenk added a comment - 2024-05-03 18:44 - edited I guess the best workaround for now is: SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]' ; SELECT REPLACE (data, "\\" , "" ) as data FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data' )) AS t;

Louis Brauer added a comment - 2024-07-15 13:13

Gosh, two and a half years later this bug is still present in the newest MariaDB release (11.4.2), not even a comment or an ETA.

I really have a hard time to take MariaDB seriously into consideration for production purposes. Is there anyone at MariaDB who could comment on this issue?

Louis Brauer added a comment - 2024-07-15 13:13 Gosh, two and a half years later this bug is still present in the newest MariaDB release (11.4.2), not even a comment or an ETA. I really have a hard time to take MariaDB seriously into consideration for production purposes. Is there anyone at MariaDB who could comment on this issue?

Sergei Golubchik made changes - 2024-07-15 17:49

Priority

Major [ 3 ]

Critical [ 2 ]

Rucha Deodhar added a comment - 2024-07-23 10:18

Your frustration is totally understandable and I fully acknowledge the importance of this issue. We're currently working on a fix. Expect an update as soon as we make progress on the completion of this work.

Rucha Deodhar added a comment - 2024-07-23 10:18 Your frustration is totally understandable and I fully acknowledge the importance of this issue. We're currently working on a fix. Expect an update as soon as we make progress on the completion of this work.

Rucha Deodhar made changes - 2024-07-23 10:19

Status

Open [ 1 ]

In Progress [ 3 ]

Rucha Deodhar added a comment - 2024-07-23 10:41

Patch: https://github.com/MariaDB/server/commit/7605ea24e5158c42b83194851d300d42817965c9

Rucha Deodhar added a comment - 2024-07-23 10:41 Patch: https://github.com/MariaDB/server/commit/7605ea24e5158c42b83194851d300d42817965c9

Rucha Deodhar made changes - 2024-07-23 10:41

Assignee	Rucha Deodhar [ rucha174 ]	Alexey Botchkov [ holyfoot ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexey Botchkov added a comment - 2024-09-27 14:35

ok to push.

Alexey Botchkov added a comment - 2024-09-27 14:35 ok to push.

Alexey Botchkov made changes - 2024-09-27 14:35

Assignee	Alexey Botchkov [ holyfoot ]	Rucha Deodhar [ rucha174 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Rucha Deodhar made changes - 2024-10-01 10:22

Fix Version/s		10.6.20 [ 29903 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

JiraAutomate made changes - 2024-10-01 10:22

Fix Version/s		10.11.10 [ 29904 ]
Fix Version/s		11.2.6 [ 29906 ]
Fix Version/s		11.4.4 [ 29907 ]

People

Assignee:: Rucha Deodhar

Reporter:: Louis Brauer

Votes:: 5 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2022-01-03 11:23

Updated:: 2024-10-01 10:22

Resolved:: 2024-10-01 10:22

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration