[#MDEV-31726] ORDER BY in nested json_object() using row

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`;

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)

[MDEV-31726] ORDER BY in nested json_object() using row_number() sorts in reverse order Created: 2023-07-17 Updated: 2023-07-18
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Optimizer - Window functions
Affects Version/s:	11.1.1, 10.4, 10.11.4
Fix Version/s:	10.4

[MDEV-31726] ORDER BY in nested json_object() using row_number() sorts in reverse order Created: 2023-07-17 Updated: 2023-07-18