Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.9.1, 10.11.11, 10.11, 11.4, 11.8
Description
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
SET @JSON=' |
{
|
"SZ": [
|
{
|
"NAME": "S0",
|
"OFFERS": [
|
{
|
"NAME": "S0A0"
|
}
|
]
|
},
|
{
|
"NAME": "S1",
|
"OFFERS": [
|
{
|
"NAME": "S1A0"
|
},
|
{
|
"NAME": "S1A1"
|
}
|
]
|
},
|
{
|
"NAME": "S2",
|
"OFFERS": [
|
{
|
"NAME": "S2A0"
|
}
|
]
|
},
|
{
|
"NAME": "S3",
|
"OFFERS": [
|
{
|
"NAME": "S3A0"
|
}
|
]
|
},
|
{
|
"NAME": "S4",
|
"OFFERS": [
|
{
|
"NAME": "S4A0"
|
}
|
]
|
},
|
{
|
"NAME": "S5",
|
"OFFERS": [
|
{
|
"NAME": "S5A0"
|
}
|
]
|
}
|
]
|
}
|
|
'
|
;
|
|
#Good result S0A0 this ok in mariadb 10.11 |
SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' |
COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; |
#Good result no result, wrong result in 10.11 |
SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' |
COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; |
|
#Good result S1A1 wrong result in 10.11 |
SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' |
COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; |
|
#Good result in mariadb 10.11 |
select JSON_VALUE(@json, '$.SZ[1].OFFERS[1].NAME'); |
|
Attachments
Issue Links
- is caused by
-
MDEV-27911 Implement range notation for json path
-
- Closed
-
Activity
Field | Original Value | New Value |
---|---|---|
Summary | Wron result json_table | Wrong result json_table |
Component/s | JSON [ 13908 ] |
Description |
cd ~/sql_schema2/forras_sql/
/opt/skeema init -H 192.168.30.18 -u csenki2 -p --schema=pworkflow -d ~/sql_schema2/forras_sql/pworkflow /opt/skeema init -H 127.0.0.1 -u root -p --schema=pworkflow -d ~/sql_schema2/forras_sql/pworkflow /opt/skeema diff development /opt/skeema init -h 127.0.0.1 -u root -p --schema=pworkflow -d ~/sql_schema2/forras_sql/pworkflow /opt/skeema push development -p /opt/skeema diff -H 192.168.30.18 -u csenki2 -p --schema=pworkflow SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; In mariadb 10.6 the result was good |
SET @JSON='
{ "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; In mariadb 10.6 the result was good |
Description |
SET @JSON='
{ "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; In mariadb 10.6 the result was good |
{code:mysql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good |
Description |
{code:mysql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good |
{code:sql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good |
Description |
{code:sql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good |
{code:sql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result |
Description |
{code:sql}
SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result {code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} |
Description |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result {code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
{code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 this ok in mariadb 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result, wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} |
Description |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
{code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 this ok in mariadb 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result, wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; {code} |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
{code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 this ok in mariadb 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result, wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result in in mariadb 10.11 select JSON_VALUE(@json, '$.SZ[1].OFFERS[1].NAME'); {code} |
Description |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
{code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 this ok in mariadb 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result, wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result in in mariadb 10.11 select JSON_VALUE(@json, '$.SZ[1].OFFERS[1].NAME'); {code} |
In mariadb 10.6 the result was good, in mariadb 10.11 give wrong result
{code:sql} SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; #Good result S0A0 this ok in mariadb 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[0]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result no result, wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result S1A1 wrong result in 10.11 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; #Good result in mariadb 10.11 select JSON_VALUE(@json, '$.SZ[1].OFFERS[1].NAME'); {code} |
Affects Version/s | 10.11 [ 27614 ] | |
Affects Version/s | 11.4 [ 29301 ] | |
Affects Version/s | 11.8 [ 29921 ] |
Fix Version/s | 10.11 [ 27614 ] | |
Fix Version/s | 11.4 [ 29301 ] | |
Fix Version/s | 11.8 [ 29921 ] |
Affects Version/s | 10.9.1 [ 27114 ] |
Link |
This issue is caused by |
Assignee | Rucha Deodhar [ rucha174 ] |
Status | Open [ 1 ] | Confirmed [ 10101 ] |
Labels | regression-10.9 |