Horton

Thread

Commits

  1. Fix improper interaction of FULL JOINs with lateral references.

  1. BUG #15741: ERROR: failed to build any 3-way joins

    The Post Office <noreply@postgresql.org> — 2019-04-08T13:32:44Z

    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.

  2. Re: BUG #15741: ERROR: failed to build any 3-way joins

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-08T14:13:58Z

    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

  3. Re: BUG #15741: ERROR: failed to build any 3-way joins

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-08T20:13:42Z

    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=051c71c6746309782d9bf1ebb257d3b5f88e6fb7

			regards, tom lane

  4. Re: BUG #15741: ERROR: failed to build any 3-way joins

    Anthony SKORSKI <skorski.anthony@gmail.com> — 2019-04-09T08:00:58Z

    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=051c71c6746309782d9bf1ebb257d3b5f88e6fb7
>
>                         regards, tom lane
>

  5. Re: BUG #15741: ERROR: failed to build any 3-way joins

    Anthony SKORSKI <skorski.anthony@gmail.com> — 2019-05-01T17:30:00Z

    > Fixed at
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=051c71c6746309782d9bf1ebb257d3b5f88e6fb7
>
> 			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