From: Tom Lane <tgl@sss.pgh.pa.us>
To: feasiblechart@gmail.com
cc: David Rowley <dgrowleyml@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm
 fails with "could not find pathkey item t"
In-reply-to: <261146.1791064428@sss.pgh.pa.us>
References: <19742-dc403ca277cad1d3@postgresql.org>
 <261146.1791064428@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Sat, 03 Oct 2026 17:53:48 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <273646.1791075609.1@sss.pgh.pa.us>
Date: Sat, 03 Oct 2026 21:00:09 -0400
Message-ID: <273647.1791075609@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/273647.1791075609%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> Bisecting shows this started with
> fdda78e361f136ec2b8de579b366c1e66bba1199 is the first bad commit
> I suspect that that commit just allowed reaching some pre-existing
> mistake, but I've not dug into it.

After looking a bit closer, v18 produces this plan:

 Append  (cost=84.23..84.46 rows=14 width=4)
   ->  SetOp Intersect All  (cost=84.23..84.39 rows=13 width=4)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d.a
               ->  Seq Scan on d  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d_1.a
               ->  Seq Scan on d d_1  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)
   ->  Result  (cost=0.00..0.00 rows=0 width=0)
         One-Time Filter: false

v19/HEAD produce a Path that is equivalent to v18's except for two
things:

* The empty-query Result isn't there; evidently we figured out that
it's useless and tossed it.  So now the AppendPath has only one child.

* The AppendPath is marked as having pathkeys:

   :path.pathkeys (
      {PATHKEY 
      :pk_eclass 
         {EQUIVALENCECLASS 
         :ec_opfamilies (o 1976)
         :ec_collation 0 
         :ec_childmembers_size 0 
         :ec_members (
            {EQUIVALENCEMEMBER 
            :em_expr 
               {VAR 
               :varno 1 
               :varattno 1 
               :vartype 23 
               :vartypmod -1 
               :varcollid 0 
               :varnullingrels (b)
               :varlevelsup 0 
               :varreturningtype 0 
               :varnosyn 1 
               :varattnosyn 1 
               :location -1
               }
            :em_relids (b 1)
            ...

whereas in v18 it has nil pathkeys.  The immediate problem is that
create_append_plan calls prepare_sort_from_pathkeys to try to
create a representation of the pathkey in terms of the Append's
tlist, and what's in the Append's tlist is

         {VAR 
         :varno 0 
         :varattno 1 
         :vartype 23 
         :vartypmod -1 
         :varcollid 0 
         :varnullingrels (b)
         :varlevelsup 0 
         :varreturningtype 0 
         :varnosyn 0 
         :varattnosyn 1 
         :location -1
         }

that is the tlist has been translated to the "varno zero"
representation that prepunion.c generates.  So we fail to
match the 1/1 Var to this 0/1 Var, and kaboom.

So the seeds of this problem go far back, but the immediate
cause is that we're labeling the AppendPath with pathkeys
in cases where we did not before, and our implementation can't
actually support that.  Interestingly, this doesn't fail:

explain SELECT * FROM ((SELECT a FROM d INTERSECT ALL SELECT a FROM d)
union all (SELECT a FROM d INTERSECT ALL SELECT a FROM d)) s
WHERE a = 1;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Append  (cost=84.23..168.92 rows=26 width=4)
   ->  SetOp Intersect All  (cost=84.23..84.39 rows=13 width=4)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d.a
               ->  Seq Scan on d  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d_1.a
               ->  Seq Scan on d d_1  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)
   ->  SetOp Intersect All  (cost=84.23..84.39 rows=13 width=4)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d_2.a
               ->  Seq Scan on d d_2  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)
         ->  Sort  (cost=42.12..42.15 rows=13 width=4)
               Sort Key: d_3.a
               ->  Seq Scan on d d_3  (cost=0.00..41.88 rows=13 width=4)
                     Filter: (a = 1)

and the reason it doesn't fail is that the AppendPath has nil pathkeys
in this case.  So (I speculate that) we never attached pathkeys to a
UNION ALL AppendPath before, and the reason we're trying to now has
something to do with having reduced the child list to a singleton.

I'm too tired to dig any further tonight.

			regards, tom lane