Re: 'update returning *' returns 0 columns instead of empty row with2 columns when (i) no rows updated and (ii) when applied to a partitionedtable with sub-partition

Hi,

On 2019/02/01 23:32, Petr Fedorov wrote:
> Hello,
> 
> The following code snippet demonstrates the problem: the first select
> passes and the second [select * from testf(FALSE)] fails. I would expect
> that select * from testf(...); works without errors in both cases.
> 
> begin;
> 
> create table test (id integer, data char(1)) partition by list (id)
> tablespace pg_default;
> create table test_1 partition of test for values in (1) partition by
> list (data);
> create table test_1_a partition of test_1 for values in ('a');
> create function testf(p boolean) returns setof test language 'plpgsql'
> as $body$ begin return query update test set id=id where p returning *;
> end; $body$;
> insert into test (id, data) values (1, 'a');
> select * from testf(TRUE);
> select * from testf(FALSE);
> 
> rollback;   
> 
> The result:
> 
> ERROR: structure of query does not match function result type
> 
> SQL state: 42804
> 
> Detail: Number of returned columns (0) does not match expected column
> count (2).
> 
> Context: PL/pgSQL function testf(boolean) line 1 at RETURN QUERY

Thanks for the report.  There indeed appears to be a bug here.

The problem seems to be with how planner handles an empty plan (due to
constant-FALSE qual) when the target table is an inheritance tree.  OP's
example contains a partitioned table, but I could reproduce it with
regular inheritance:

create table parent (id int);
create table child () inherits (parent);
create or replace function testf(p boolean) returns setof parent
  language 'plpgsql' as $body$
    begin
      return query update parent set id = id where p returning *;
    end;
$body$;

select * from testf(true);
 id
────
(0 rows)

select * from testf(false);
ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (0) does not match expected column
count (1).
CONTEXT:  PL/pgSQL function testf(boolean) line 1 at RETURN QUERY

No problem when there is no inheritance:

drop function testf;
create table foo (like parent);
create or replace function testf(p boolean) returns setof foo
  language 'plpgsql' as $body$
    begin
      return query update foo set id = id where p returning *;
    end;
$body$;
select * from testf(false);
 id
────
(0 rows)


Mismatch between the query result type and the function result type occurs
in the inheritance case, because the targetlist of the plan for the UPDATE
query in testf's body is empty, whereas the function execution code
(pl_exec.c) expects it match the function's result type (set of parent).
It's empty because inheritance_planner sets an empty Result path when it
finds that all the children are excluded, but hasn't generated enough
state in the path's RelOptInfo and PlannerInfo such that the correct
targetlist could be set in the empty Result plan that's eventually created.

Attached patch seems to fix it.  It also adds a test in inherit.sql.

Thoughts?

Thanks,
Amit