pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15741: ERROR: failed to build any 3-way joins
5+ messages / 4 participants
[nested] [flat]
* BUG #15741: ERROR: failed to build any 3-way joins
@ 2019-04-08 13:32 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2019-04-08 13:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: skorski.anthony@gmail.com
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15741: ERROR: failed to build any 3-way joins
@ 2019-04-08 14:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2019-04-08 14:13 UTC (permalink / raw)
To: skorski.anthony@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
Yeah, that's certainly a bug. I'll take a look later ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15741: ERROR: failed to build any 3-way joins
@ 2019-04-08 20:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2019-04-08 20:13 UTC (permalink / raw)
To: skorski.anthony@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
I wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> 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.
> Yeah, that's certainly a bug. I'll take a look later ...
Fixed at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=051c71c6746309782d9bf1ebb257d3b5f...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15741: ERROR: failed to build any 3-way joins
@ 2019-04-09 08:00 SKORSKI Anthony <skorski.anthony@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: SKORSKI Anthony @ 2019-04-09 08:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks for the impressive reactivity.
I will test it as soon as possible.
Regards
Le lun. 8 avr. 2019 à 22:13, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> I wrote:
> > PG Bug reporting form <noreply@postgresql.org> writes:
> >> 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.
>
> > Yeah, that's certainly a bug. I'll take a look later ...
>
> Fixed at
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=051c71c6746309782d9bf1ebb257d3b5f...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15741: ERROR: failed to build any 3-way joins
@ 2019-05-01 17:30 Anthony SKORSKI <skorski.anthony@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Anthony SKORSKI @ 2019-05-01 17:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
> Fixed at
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=051c71c6746309782d9bf1ebb257d3b5f...
>
> regards, tom lane
I finally just managed to test this commit and I confirm that it fixed
the error I was encountering.
Thanks again for the impressive efficiency.
Regards, Anthony SKORSKI
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-05-01 17:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-08 13:32 BUG #15741: ERROR: failed to build any 3-way joins PG Bug reporting form <noreply@postgresql.org>
2019-04-08 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-08 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-09 08:00 ` SKORSKI Anthony <skorski.anthony@gmail.com>
2019-05-01 17:30 ` Anthony SKORSKI <skorski.anthony@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox