Description:
A recursive query in a correlated derived table does not seem to work correctly in MySQL
How to repeat:
Run this query:
-----------------------------------------
with
books (id) as (
select 1 union
select 2 union
select 3 union
select 4
)
select
books.id,
(
select json_arrayagg(i)
from (
with recursive
t(i) as (
select 1
union all
select (i + 1)
from t
where i < books.id
)
select i
from t
) as t
) json_agg
from books
order by books.id;
-----------------------------------------
It should produce:
|json_agg |
|------------|
|[1] |
|[1, 2] |
|[1, 2, 3] |
|[1, 2, 3, 4]|
But it produces:
|id |json_agg|
|---|--------|
|1 |[1] |
|2 |[1] |
|3 |[1] |
|4 |[1] |
Suggested fix:
Moving the recursion up one level seems to work around the issue. The following query works correctly in MySQL 8.0.29:
-----------------------------------------
with
books (id) as (
select 1 union
select 2 union
select 3 union
select 4
)
select
books.id,
(
with recursive
t(i) as (
select 1
union all
select (i + 1)
from t
where i < books.id
)
select json_arrayagg(i)
from t
) json_agg
from books
order by books.id;
-----------------------------------------
Producing:
|json_agg |
|------------|
|[1] |
|[1, 2] |
|[1, 2, 3] |
|[1, 2, 3, 4]|