Bug #107286 Recursive correlated derived table does not work correctly
Submitted: 13 May 2022 9:06 Modified: 13 May 2022 9:28
Reporter: Lukas Eder Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[13 May 2022 9:06] Lukas Eder
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]|
[13 May 2022 9:28] MySQL Verification Team
Hello Lukas Eder,

Thank you for the report and test case.

regards,
Umesh