Horton

Thread

Commits

  1. Handle WindowClause.runCondition in tree walker/mutator functions.

  2. Make dblink interruptible, via new libpqsrv APIs.

  3. Make Vars be outer-join-aware.

  1. BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func

    The Post Office <noreply@postgresql.org> — 2024-01-08T21:09:16Z

    The following bug has been logged on the website:

Bug reference:      18277
Logged by:          Zuming Jiang
Email address:      zuming.jiang@inf.ethz.ch
PostgreSQL version: 16.1
Operating system:   Ubuntu 20.04
Description:        

My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
error "ERROR: WindowFunc not found in subplan target lists".

--- Set up database ---
create table exeet_t184 (vkey int4);
create view exeet_t188 as
select
    ntile(ref_1.vkey) over w0 as c_1
  from
    (exeet_t184 as ref_0
      left outer join exeet_t184 as ref_1
      on (ref_0.vkey = ref_1.vkey))
  where ref_0.vkey <> ref_1.vkey
  window w0 as (partition by ref_0.vkey);

The fuzzer generates a test case:

--- Test case ---
select
  (case when ('false' <> (SELECT nummultirange() && 'empty'::numrange)) then
(case when (1 in (
                select
                  1 as c_0
                from
                  exeet_t188 as ref_12
                where 1 between ref_12.c_1 and 1)) then 1 else 1 end
        ) else 1 end) as c_5;

--- Expected behavior ---
The test case should not trigger any error.

--- Actual behavior ---
The test case trigger an error: 

ERROR:  WindowFunc not found in subplan target lists

--- Postgres version ---
Github commit: d3c5f37dd543498cc7c678815d3921823beec9e9
Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic

  2. Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-08T22:59:52Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
> error "ERROR: WindowFunc not found in subplan target lists".

Thanks for the report!  Bisecting says this broke at

2489d76c4906f4461a364ca8ad7e0751ead8aa0d is the first bad commit
commit 2489d76c4906f4461a364ca8ad7e0751ead8aa0d
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jan 30 13:16:20 2023 -0500

    Make Vars be outer-join-aware.

so it's my fault and I'll take a closer look in a bit.

			regards, tom lane

  3. Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func

    Richard Guo <guofenglinux@gmail.com> — 2024-01-09T07:35:08Z

    On Tue, Jan 9, 2024 at 7:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> PG Bug reporting form <noreply@postgresql.org> writes:
> > My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
> > error "ERROR: WindowFunc not found in subplan target lists".
>
> Thanks for the report!  Bisecting says this broke at
>
> 2489d76c4906f4461a364ca8ad7e0751ead8aa0d is the first bad commit
> commit 2489d76c4906f4461a364ca8ad7e0751ead8aa0d
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Mon Jan 30 13:16:20 2023 -0500
>
>     Make Vars be outer-join-aware.


I think this is caused by that query_tree_mutator fails to mutate
runCondition for WindowClause.  So when we've reduced the ref_0/ref_1
left join to inner join, we fail to remove references to this join as
nulling rels in the run condition '1 >= ntile(ref_1.vkey) OVER (?)'.

So maybe we can fix this issue by:

@@ -3641,6 +3646,7 @@ query_tree_mutator_impl(Query *query,
            FLATCOPY(newnode, wc, WindowClause);
            MUTATE(newnode->startOffset, wc->startOffset, Node *);
            MUTATE(newnode->endOffset, wc->endOffset, Node *);
+           MUTATE(newnode->runCondition, wc->runCondition, List *);

I think we should also consider WindowClause->runCondition in
query_tree_walker, expression_tree_mutator and expression_tree_walker.
Hence, proposed the attached patch.

Thanks
Richard

  4. Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-09T21:34:31Z

    Richard Guo <guofenglinux@gmail.com> writes:
> On Tue, Jan 9, 2024 at 7:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> PG Bug reporting form <noreply@postgresql.org> writes:
>>> My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
>>> error "ERROR: WindowFunc not found in subplan target lists".

> I think this is caused by that query_tree_mutator fails to mutate
> runCondition for WindowClause.

Ugh!  That seems quite horrid.  David, was there a reason to not do
that, or was it just an oversight?

> I think we should also consider WindowClause->runCondition in
> query_tree_walker, expression_tree_mutator and expression_tree_walker.

Offhand, +1.  If it has the form of an ordinary expression tree
then it really needs to be processed in all these places.

			regards, tom lane