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
-
-
- Closed
-
Activity
Thank you for the report!
This is the same as MDEV-30623, I checked the test case on the current 10.6,10.11 - now it returns correct results:
MariaDB [test]> 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}] | 4 |
|
+----+-------------------------------+---------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select version();
|
+-----------------------+
|
| version() |
|
+-----------------------+
|
| 10.11.9-MariaDB-debug |
|
+-----------------------+
|
1 row in set (0,001 sec)
|
Looks similar to MDEV-28608