Re: BUG #15669: Error with unnest in PG 11 (ERROR: 0A000) - Mailing list pgsql-bugs

I wrote:
> 2. If we have a dummy relation, and we stick a ProjectionPath atop the
> existing dummy path, it stops looking like a dummy relation, as indeed
> noted in the existing comment.  It's possible that nothing after this
> point cares, but I would not exactly bet on that --- I think it's more
> likely that we just don't have test cases exercising combinations where
> there are nontrivial processing steps remaining.

Indeed, here's a test case, using some trivial regression-test tables:

explain verbose
select * from int4_tbl,
  (select unnest(array[1,2]) from int8_tbl where false offset 0) ss;

In 9.6 we figure out that the entire query must be dummy:

                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=8)
   Output: int4_tbl.f1, ss.unnest
   One-Time Filter: false
(3 rows)

In v10 and later, not so much:

                             QUERY PLAN                              
---------------------------------------------------------------------
 Nested Loop  (cost=0.00..1.10 rows=5 width=8)
   Output: int4_tbl.f1, (unnest('{1,2}'::integer[]))
   ->  Seq Scan on public.int4_tbl  (cost=0.00..1.05 rows=5 width=4)
         Output: int4_tbl.f1
   ->  ProjectSet  (cost=0.00..0.00 rows=0 width=4)
         Output: unnest('{1,2}'::integer[])
         ->  Result  (cost=0.00..0.00 rows=0 width=0)
               One-Time Filter: false
(8 rows)

but if you delete the "unnest()" it's okay again:

regression=# explain verbose
select * from int4_tbl,
  (select (array[1,2]) from int8_tbl where false offset 0) ss;
                QUERY PLAN                 
-------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=36)
   Output: int4_tbl.f1, ss."array"
   One-Time Filter: false
(3 rows)

The reason for that is that the outer query level understands that
the "ss" subselect is dummy only as long as there's not a
ProjectSetPath in the way.  So really this is a bug induced by the
ProjectSet patches, and we need a fix back to v10.

I'm now thinking that my hesitance to back-patch a data structure
change was misguided.  We should add the bool flag to RelOptInfo
(in the back branches, being careful that it goes into alignment
padding or at the end) and redefine

#define IS_DUMMY_REL(r) ((r)->is_dummy_rel)

thus preserving source-level API compatibility.  We'll still maintain
the convention that there's one dummy path, but possibly with a
ProjectSetPath on top.  This means that extensions that are calling
IS_DUMMY_REL will get the wrong answer in these cases until they're
recompiled.  But that really can't be helped --- my other idea
certainly wasn't better from that standpoint.

Alternatively, we could teach IS_DUMMY_PATH to recurse through
ProjectSetPath and then the IS_DUMMY_REL macro could be left alone
(though it's still broken ABI-wise, if IS_DUMMY_PATH changes).
I'd rather not do it that way, on cost grounds.  It'd only matter
for direct uses of IS_DUMMY_PATH, of which there are only two in
the core code.  The one in inheritance_planner should become an
IS_DUMMY_REL test anyway (though it might well be impossible to see a
ProjectSetPath there).  The one in is_projection_capable_path is OK
as-is.  Question is whether anyone has duplicated the
inheritance_planner coding in extensions ...

            regards, tom lane