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`) |
);
|
Thanks for the bug report with test case;
anayze format=json query
ANALYZE: {"query_block": {"select_id": 1,"table": {"message": "No tables used"},"subqueries": [{"query_block": {"select_id": 2,"r_loops": 1,"r_total_time_ms": 0.6671,"table": {"table_name": "<derived6>","access_type": "ALL","r_loops": 1,"rows": 2,"r_rows": 1,"r_total_time_ms": 0.0269,"filtered": 100,"r_filtered": 100,"materialized": {"query_block": {"select_id": 6,"r_loops": 1,"r_total_time_ms": 0.3645,"filesort": {"sort_key": "row_number() over ()","r_loops": 1,"r_total_time_ms": 0.008,"r_used_priority_queue": false,"r_output_rows": 1,"r_buffer_size": "345","window_functions_computation": {"sorts": {"filesort": {"sort_key": "ArtistId","r_loops": 1,"r_total_time_ms": 0.0336,"r_used_priority_queue": false,"r_output_rows": 1,"r_buffer_size": "300"}},"temporary_table": {"table": {"table_name": "Artist1","access_type": "ALL","r_loops": 1,"rows": 1,"r_rows": 1,"r_total_time_ms": 0.0242,"filtered": 100,"r_filtered": 100}}}}}}},"subqueries": [{"query_block": {"select_id": 4,"r_loops": 1,"r_total_time_ms": 0.0985,"outer_ref_condition": "Artist1.ArtistId is not null","table": {"table_name": "<derived5>","access_type": "ref","possible_keys": ["key0"],"key": "key0","key_length": "5","used_key_parts": ["ArtistId"],"ref": ["Artist1.ArtistId"],"r_loops": 1,"rows": 2,"r_rows": 2,"r_total_time_ms": 0.005,"filtered": 100,"r_filtered": 100,"materialized": {"query_block": {"select_id": 5,"r_loops": 1,"r_total_time_ms": 0.0477,"filesort": {"sort_key": "row_number() over ( partition by Album1.ArtistId order by Album1.AlbumId)","r_loops": 1,"r_total_time_ms": 0.0026,"r_used_priority_queue": false,"r_output_rows": 2,"r_buffer_size": "360","window_functions_computation": {"sorts": {"filesort": {"sort_key": "Album1.ArtistId, Album1.AlbumId","r_loops": 1,"r_total_time_ms": 0.0043,"r_used_priority_queue": false,"r_output_rows": 2,"r_buffer_size": "390"}},"temporary_table": {"table": {"table_name": "Album1","access_type": "ALL","possible_keys": ["ArtistId"],"r_loops": 1,"rows": 2,"r_rows": 2,"r_total_time_ms": 0.0094,"filtered": 100,"r_filtered": 100}}}}}}}}}]}}]}}1 row in set (0.002 sec)