Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11.7, 11.4.2
-
None
Description
When I try to use JSON_TABLE in SELECT part, that subquery returns same value for all rows:
Test case:
MariaDB [(none)]> select version(); |
+------------------------+ |
| version() |
|
+------------------------+ |
| 11.4.2-MariaDB-ubu2404 |
|
+------------------------+ |
1 row in set (0.001 sec) |
 |
MariaDB [(none)]> select d.id,d.doc |
-> ,(select a from json_table(d.doc,'$[*]' columns(a int path '$.a',b int path '$.b')) t where t.b=2 limit 1) found_a |
-> from ( |
-> (select 1 id,'[{"a":3,"b":1},{"a":2,"b":2}]' doc) |
-> union all (select 2,'[{"a":6,"b":1},{"a":4,"b":2}]') |
-> ) d
|
-> ;
|
+----+-------------------------------+---------+ |
| id | doc | found_a |
|
+----+-------------------------------+---------+ |
| 1 | [{"a":3,"b":1},{"a":2,"b":2}] | 2 | |
| 2 | [{"a":6,"b":1},{"a":4,"b":2}] | 2 | |
+----+-------------------------------+---------+ |
2 rows in set (0.000 sec) |
Workaround (sort of):
MariaDB [(none)]> select d.id,d.doc |
-> ,t.a found_a
|
-> from ( |
-> (select 1 id,'[{"a":3,"b":1},{"a":2,"b":2}]' doc) |
-> union all (select 2,'[{"a":6,"b":1},{"a":4,"b":2}]') |
-> ) d
|
-> join json_table(d.doc,'$[*]' columns(a int path '$.a',b int path '$.b')) t on t.b=2 |
-> group by d.id |
-> ;
|
+----+-------------------------------+---------+ |
| id | doc | found_a |
|
+----+-------------------------------+---------+ |
| 1 | [{"a":3,"b":1},{"a":2,"b":2}] | 2 | |
| 2 | [{"a":6,"b":1},{"a":4,"b":2}] | 4 | |
+----+-------------------------------+---------+ |
2 rows in set (0.000 sec) |
Tested versions:
- 11.4.2-MariaDB-ubu2404
- 10.11.7-MariaDB-1:10.11.7+maria~ubu2204
Attachments
Issue Links
- duplicates
-
MDEV-30623 JSON_TABLE in subquery not correctly marked as correlated
-
- Closed
-