Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.1.1, 10.11.4, 10.4(EOL)
-
None
-
Official Docker Image (Ubuntu 22.04)
Description
A strange bug occurs when using `row_number()` in nested `json_object()` calls.
Below is the reproduction:
CREATE TABLE `Artist1` ( |
`ArtistId` INTEGER PRIMARY KEY, |
`Name` NVARCHAR(255) |
);
|
|
|
CREATE TABLE `Album1` ( |
`AlbumId` INTEGER PRIMARY KEY, |
`Title` NVARCHAR(255),
|
`ArtistId` INTEGER, |
FOREIGN KEY (`ArtistId`) REFERENCES `Artist1` (`ArtistId`) |
);
|
|
|
INSERT INTO `Artist1` (`ArtistId`, `Name`) VALUES (1, 'AC/DC'); |
|
|
INSERT INTO `Album1` (`AlbumId`, `Title`, `ArtistId`) VALUES (1, 'For Those About To Rock We Salute You', 1); |
INSERT INTO `Album1` (`AlbumId`, `Title`, `ArtistId`) VALUES (4, 'Let There Be Rock', 1); |
|
|
select json_object('rows', ( |
select coalesce( |
json_merge_preserve(
|
'[]', |
concat(
|
'[', |
group_concat(json_merge_preserve(
|
'{}', |
json_object('Name', `Name`), |
json_object('ArtistId', `ArtistId`), |
json_object('Albums', ( |
select json_object('rows', ( |
select coalesce( |
json_merge_preserve(
|
'[]', |
concat(
|
'[', |
group_concat(json_merge_preserve(
|
'{}', |
json_object('Title', `Title`), |
json_object('AlbumId', `AlbumId`) |
) separator ','), |
']' |
)
|
),
|
json_array()
|
)
|
from ( |
select |
`Album1`.*,
|
row_number() over (
|
partition by `Album1`.`ArtistId` |
order by `Album1`.`AlbumId` asc |
) as `rn` |
from `Album1` |
where true |
order by `rn` |
) as `Album1` |
where `Artist1`.`ArtistId` = `Album1`.`ArtistId` |
)) as `data` |
))
|
) separator ','), |
']' |
)
|
),
|
json_array()
|
)
|
from ( |
select |
`Artist1`.*,
|
row_number() over () as `rn` |
from `Artist1` |
where true |
order by `rn` |
) as `Artist1` |
)) as `data`; |
Since we have:
```
order by `Album1`.`AlbumId` asc
```
We expect that we'll get albums `(1, 4)`. But instead, we get the opposite sort order:
{
|
"rows": [
|
{
|
"Name": "AC/DC",
|
"ArtistId": 1,
|
"Albums": {
|
"rows": [
|
{
|
"Title": "Let There Be Rock",
|
"AlbumId": 4
|
},
|
{
|
"Title": "For Those About To Rock We Salute You",
|
"AlbumId": 1
|
}
|
]
|
}
|
}
|
]
|
}
|
Running the same query on MySQL 8 produces the correct result:
{
|
"rows": [
|
{
|
"Name": "AC/DC",
|
"Albums": {
|
"rows": [
|
{
|
"Title": "For Those About To Rock We Salute You",
|
"AlbumId": 1
|
},
|
{
|
"Title": "Let There Be Rock",
|
"AlbumId": 4
|
}
|
]
|
},
|
"ArtistId": 1
|
}
|
]
|
}
|
What is interesting is that if you change the definition of the tables to use `TEXT` instead of `NVARCHAR` then it sorts them in the proper order:
CREATE TABLE `Artist1` ( |
`ArtistId` INTEGER PRIMARY KEY, |
`Name` TEXT |
);
|
|
|
CREATE TABLE `Album1` ( |
`AlbumId` INTEGER PRIMARY KEY, |
`Title` TEXT,
|
`ArtistId` INTEGER, |
FOREIGN KEY (`ArtistId`) REFERENCES `Artist1` (`ArtistId`) |
);
|