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/22 7:18, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> On 2019/02/01 23:32, Petr Fedorov wrote:
>>> ERROR: structure of query does not match function result type
>
>> Thanks for the report. There indeed appears to be a bug here.
>
> Yup, for sure. You don't actually need a function at all to see
> that there's a problem: if you just execute
> UPDATE ... WHERE false RETURNING some-columns;
> you will notice that the emitted resultset has zero columns.

Ah, indeed.

>> Attached patch seems to fix it. It also adds a test in inherit.sql.
>
> This isn't quite right, because what we actually need to return is the
> RETURNING column set. If you only check "RETURNING *" then you might not
> notice the difference, but with anything else it's obviously wrong.
>
> I propose the attached modification instead.

Looks good.

I know this code may not be with us forever, but I wonder why the plan
shape looks different for an empty update on a regular table vs
inheritance tree. For regular table, it's ModifyTable on top of a dummy
Result node, whereas it's just dummy Result node for the latter. If the
plan shape for the two cases had matched, we wouldn't have this bug at all
or we'd have it for both cases (in slightly different form for the regular
table case). To check I patched grouping_planner() to not add a
ModifyTable on top of a dummy result path for a regular table and the
resulting target list is not quite right (what you get with my patch
upthread):

create table foo (a int);
update foo set a = a where false returning a+1 as b;
a
───
(0 rows)

explain verbose update foo set a = a where false returning a+1 as b;
QUERY PLAN
───────────────────────────────────────────
Result (cost=0.00..0.00 rows=0 width=10)
Output: a, ctid
One-Time Filter: false
(3 rows)

Also, I noticed regression test failure having to do with statement
triggers not firing, which makes sense, as there's no ModifyTable to
invoke them.

That means we have a bug (?) today that statement triggers of inheritance
parent tables don't fire when it's an empty update/delete.

create table parent (a int, b int);
create table child () inherits (parent);
create or replace function before_stmt_notice() returns trigger as $$
begin raise notice 'updating %', TG_TABLE_NAME; return null; end; $$
language plpgsql;
create trigger before_stmt_trigger before update on parent execute
function before_stmt_notice();

-- trigger doesn't fire
update parent set a = a where false returning a+1 as b;
──
(0 rows)

It does fire for an empty update on a regular table (with HEAD I mean)

create trigger before_stmt_trigger before update on foo execute function
before_stmt_notice();
update foo set a = a where false returning a+1 as b;
NOTICE: updating foo
b
───
(0 rows)

Thanks,
Amit