Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.4.2
-
None
Description
Run this query:
select a, b, (
|
select json_arrayagg(i)
|
from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t
|
)
|
from (select 1 a, 2 b union all select 3 a, 4 b) as t
|
order by a;
|
MariaDB returns this:
|a |b |json_arrayagg|
|
|---|---|-------------|
|
|1 |2 |[1, 2] |
|
|3 |4 |[1, 2] |
|
But this is expected
|a |b |json_arrayagg|
|
|---|---|-------------|
|
|1 |2 |[1, 2] |
|
|3 |4 |[3, 4] |
|
It looks as though the correlated subquery is cached. A workaround is to disable the cache with a non-deterministic function, e.g.:
select a, b, (
|
select json_arrayagg(i)
|
from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t
|
where rand() is not null
|
)
|
from (select 1 a, 2 b union all select 3 a, 4 b) as t
|
order by a;
|
Now, the result is correct
Attachments
Issue Links
- duplicates
-
MDEV-30623 JSON_TABLE in subquery not correctly marked as correlated
-
- Closed
-