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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.29
• OS: Any
• 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