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

On 2019/02/06 16:35, Amit Langote wrote:
> 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?

Will add this to next CF.

Thanks,
Amit