BUG #15741: ERROR: failed to build any 3-way joins
От | PG Bug reporting form |
---|---|
Тема | BUG #15741: ERROR: failed to build any 3-way joins |
Дата | |
Msg-id | 15741-276f1f464b3f40eb@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15741: ERROR: failed to build any 3-way joins
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15741 Logged by: Anthony SKORSKI Email address: skorski.anthony@gmail.com PostgreSQL version: 10.6 Operating system: Ubuntu 18.04 Description: Hi Trying to build a tool which automatically builds queries to "flatten" JSONB fields, I came to a `ERROR: failed to build any 3-way joins` message while trying to execute some of those queries. It is reproducible from a simple table... `CREATE TABLE random_table (random_field JSONB);` ... some JSON data in nested arrays ... `INSERT INTO public.random_table (random_field) VALUES ('{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}');` ... and a WIP query to flatten this JSON data. ``` SELECT * FROM random_table LEFT OUTER JOIN LATERAL ( SELECT * FROM jsonb_array_elements(random_table.random_field -> 'foo') WITH ORDINALITY AS foo_level_1(foo_1, foo_1_index) LEFT OUTER JOIN LATERAL ( SELECT * FROM jsonb_array_elements(foo_1) WITH ORDINALITY AS foo_level_2(foo_2, foo_2_index) --LEFT OUTER JOIN LATERAL ( -- SELECT * -- FROM jsonb_array_elements(foo_2) -- WITH ORDINALITY AS foo_level_3(foo_3, foo_3_index) --) AS foo_lateral_2 ON TRUE ) AS foo_lateral_1 ON TRUE FULL OUTER JOIN jsonb_array_elements(random_table.random_field -> 'bar') WITH ORDINALITY AS bar_level_1(bar_1, bar_1_index) ON FALSE LEFT OUTER JOIN LATERAL ( SELECT * FROM jsonb_array_elements(bar_1) WITH ORDINALITY AS bar_level_2(bar_2, bar_2_index) ) AS bar_lateral_1 ON TRUE ) AS pouet ON TRUE; ``` Note the commented part. As is the query works as expected (for its WIP sstatus). Once uncommented, the `ERROR: failed to build any 3-way joins` occurs and I did not find anything about such a potential Postgresql limitation or any workaround. This behavior occurs on every Postgres version I tested. A fiddle is available here to highlight the problem: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0c40ba750b3e477e002f879edd0d6841 Please let me know if it actually is a bug and if it has a chance to be fixed. Regards.
В списке pgsql-bugs по дате отправления: