Horton

Thread

Commits

  1. Rethink treatment of "postponed" quals in deconstruct_jointree().

  1. BUG #17768: Assert triggered on initsplan.c

    The Post Office <noreply@postgresql.org> — 2023-02-02T02:52:49Z

    The following bug has been logged on the website:

Bug reference:      17768
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.com
PostgreSQL version: 15.1
Operating system:   Ubuntu 20.04
Description:        

This assert() is not easily reproducible, but thought may be of interest. 
Can provide full backtrace, if required.

Git: 253432f426@master
OS: Ubuntu 20.04

TRAP: failed Assert("j->jointype == JOIN_INNER"), File: "initsplan.c", Line:
1206, PID: 3285978

Backtrace / backtrace full excerpt / SQL below.

Backtrace
=========
Core was generated by `postgres: 253432f426@master@sqith: u73 postgres
127.0.0.1(35840) SELECT       '.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007f1d76f78859 in __GI_abort () at abort.c:79
#2  0x0000562684d12e9a in ExceptionalCondition (
    conditionName=0x562684ec7d20 "j->jointype == JOIN_INNER",
    fileName=0x562684ec7b3a "initsplan.c", lineNumber=1206) at assert.c:66
#3  0x00005626849ead15 in deconstruct_distribute (root=0x56268751f078,
    jtitem=0x5626870ff6a8, postponed_qual_list=0x7ffc7e6196c0)
    at initsplan.c:1206
#4  0x00005626849e9b3d in deconstruct_jointree (root=0x56268751f078)
    at initsplan.c:783
#5  0x00005626849eeae4 in query_planner (root=0x56268751f078,
    qp_callback=0x5626849f53c9 <standard_qp_callback>,
    qp_extra=0x7ffc7e6198e0) at planmain.c:186
#6  0x00005626849f1610 in grouping_planner (root=0x56268751f078,
    tuple_fraction=0) at planner.c:1496
#7  0x00005626849f0cbf in subquery_planner (glob=0x5626870b6cf8,
    parse=0x5626870b6e08, parent_root=0x0, hasRecursion=false,
    tuple_fraction=0) at planner.c:1065
#8  0x00005626849ef277 in standard_planner (parse=0x5626870b6e08,
    query_string=0x562686a6d188 "select  \n  public.constant_refcursor() as
c0\nfrom \n  public.rtest_t4 as ref_0,\n  lateral (select  \n       
pg_catalog.nummultirange() as c0\n      from>
#9  0x00007f1d73f6608b in pgss_planner (parse=0x5626870b6e08,


Backtrace full excerpt
=================
#2  0x0000562684d12e9a in ExceptionalCondition (
    conditionName=0x562684ec7d20 "j->jointype == JOIN_INNER",
    fileName=0x562684ec7b3a "initsplan.c", lineNumber=1206) at assert.c:66
No locals.
#3  0x00005626849ead15 in deconstruct_distribute (root=0x56268751f078,
    jtitem=0x5626870ff6a8, postponed_qual_list=0x7ffc7e6196c0)
    at initsplan.c:1206
        pq = 0x5626870ffaf8
        l__state = {l = 0x5626870ffb18, i = 0}
        ojscope = 0x0
        j = 0x5626870f5228
        new_postponed_quals = 0x0
        my_quals = 0x0
        sjinfo = 0x0
        postponed_oj_qual_list = 0x0
        l = 0x5626870ffb30
        jtnode = 0x5626870f5228
        __func__ = "deconstruct_distribute"
#4  0x00005626849e9b3d in deconstruct_jointree (root=0x56268751f078)
    at initsplan.c:783
        jtitem = 0x5626870ff6a8
        lc__state = {l = 0x5626870ff018, i = 6}
        result = 0x5626870ff2f8
        top_jdomain = 0x56268751f628
        item_list = 0x5626870ff018
        postponed_qual_list = 0x5626870ffb18
        lc = 0x5626870ffa48
#5  0x00005626849eeae4 in query_planner (root=0x56268751f078,
    qp_callback=0x5626849f53c9 <standard_qp_callback>,
    qp_extra=0x7ffc7e6198e0) at planmain.c:186
        parse = 0x5626870b6e08
        joinlist = 0x562684d985e0 <__libc_csu_init>
        final_rel = 0x7ffc7e619790
        __func__ = "query_planner"





SQL
===
select
  public.constant_refcursor() as c0
from
  public.rtest_t4 as ref_0,
  lateral (select
        pg_catalog.nummultirange() as c0
      from
        public.mic_inputs as sample_0 tablesample system (3.1)
            inner join pg_catalog.pg_subscription_rel as sample_1
tablesample bernoulli (8.3)
            on (ref_0.b is NULL)
          inner join sqithsch16.chr as sample_2 tablesample bernoulli
(5.2)
            right join public.num_result as ref_1
            on ((select s from public.lseg_tbl limit 1 offset 2)
                   <> (select lseg from public.tab_core_types limit 1 offset
1)
                  )
          on (true)
      where false) as subq_0
where false;

Thanks to SQLSmith for the find.

-
Robins Tharakan
Amazon Web Services

  2. Re: BUG #17768: Assert triggered on initsplan.c

    Richard Guo <guofenglinux@gmail.com> — 2023-02-02T09:13:18Z

    On Thu, Feb 2, 2023 at 3:34 PM PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      17768
> Logged by:          Robins Tharakan
> Email address:      tharakan@gmail.com
> PostgreSQL version: 15.1
> Operating system:   Ubuntu 20.04
> Description:
>
> This assert() is not easily reproducible, but thought may be of interest.
> Can provide full backtrace, if required.
>
> Git: 253432f426@master
> OS: Ubuntu 20.04
>
> TRAP: failed Assert("j->jointype == JOIN_INNER"), File: "initsplan.c",
> Line:
> 1206, PID: 3285978


Thanks for the report!  I can reproduce this issue with the following
query.

create table t1(a int);
create table t2(a int);
create table t3(a int);
create table t4(a int);
create table t5(a int);

select * from t1, lateral (select * from t2 inner join t3 on t1.a = 1 inner
join (t4 right join t5 on true) on true) as sub;

There is something wrong about postponing quals.  We are supposed to
postpone quals only from children to their parent nodes.  But here in
deconstruct_distribute we scan all the jointree nodes in depth-first
traversal order and any quals postponed by left children may be checked
against right children.  If the right subtree is an outer join as in
this example, the assertion would be triggered.

Thanks
Richard

  3. Re: BUG #17768: Assert triggered on initsplan.c

    Richard Guo <guofenglinux@gmail.com> — 2023-02-02T12:02:44Z

    On Thu, Feb 2, 2023 at 5:13 PM Richard Guo <guofenglinux@gmail.com> wrote:

>
> On Thu, Feb 2, 2023 at 3:34 PM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      17768
>> Logged by:          Robins Tharakan
>> Email address:      tharakan@gmail.com
>> PostgreSQL version: 15.1
>> Operating system:   Ubuntu 20.04
>> Description:
>>
>> This assert() is not easily reproducible, but thought may be of interest.
>> Can provide full backtrace, if required.
>>
>> Git: 253432f426@master
>> OS: Ubuntu 20.04
>>
>> TRAP: failed Assert("j->jointype == JOIN_INNER"), File: "initsplan.c",
>> Line:
>> 1206, PID: 3285978
>
>
> Thanks for the report!  I can reproduce this issue with the following
> query.
>
> create table t1(a int);
> create table t2(a int);
> create table t3(a int);
> create table t4(a int);
> create table t5(a int);
>
> select * from t1, lateral (select * from t2 inner join t3 on t1.a = 1
> inner join (t4 right join t5 on true) on true) as sub;
>
> There is something wrong about postponing quals.  We are supposed to
> postpone quals only from children to their parent nodes.  But here in
> deconstruct_distribute we scan all the jointree nodes in depth-first
> traversal order and any quals postponed by left children may be checked
> against right children.  If the right subtree is an outer join as in
> this example, the assertion would be triggered.
>

To fix this issue, I'm considering that we can add two types of info in
JoinTreeItem for each jointree node, one is the jointree's children, and
one is the list of quals that need to be postponed at this join level.
Thus in deconstruct_distribute we can process the quals postponed by
children.

Attach a draft patch (with no tests, no comments, no formats yet) to
show what I'm thinking.

Thanks
Richard

  4. Re: BUG #17768: Assert triggered on initsplan.c

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-02-03T00:47:25Z

    Richard Guo <guofenglinux@gmail.com> writes:
>> There is something wrong about postponing quals.  We are supposed to
>> postpone quals only from children to their parent nodes.  But here in
>> deconstruct_distribute we scan all the jointree nodes in depth-first
>> traversal order and any quals postponed by left children may be checked
>> against right children.  If the right subtree is an outer join as in
>> this example, the assertion would be triggered.

> To fix this issue, I'm considering that we can add two types of info in
> JoinTreeItem for each jointree node, one is the jointree's children, and
> one is the list of quals that need to be postponed at this join level.
> Thus in deconstruct_distribute we can process the quals postponed by
> children.

I've not looked closely at this, but ... I remember thinking while
revising deconstruct_jointree that the whole PostponedQual mechanism
was a wart we should try to get rid of.  I didn't touch it since
I saw no obvious bugs and the patch was too large already, but maybe
now is the time to try harder.  In any case I suspect you are right
that the core of this issue is that we're dealing with PostponedQuals
in the wrong order now.

			regards, tom lane

  5. Re: BUG #17768: Assert triggered on initsplan.c

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-02-03T22:31:21Z

    I wrote:
> I've not looked closely at this, but ... I remember thinking while
> revising deconstruct_jointree that the whole PostponedQual mechanism
> was a wart we should try to get rid of.  I didn't touch it since
> I saw no obvious bugs and the patch was too large already, but maybe
> now is the time to try harder.

Here's a draft patch (no test cases as yet) that does it like that.
I think this is morally equivalent to what you did, but perhaps a
bit neater and faster.

			regards, tom lane