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 - Mailing list pgsql-bugs

Amit Langote <Langote_Amit_f8@lab.ntt.co.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.

> 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.

            regards, tom lane

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5579dfa..93d9448 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1585,10 +1585,19 @@ inheritance_planner(PlannerInfo *root)

     /*
      * If we managed to exclude every child rel, return a dummy plan; it
-     * doesn't even need a ModifyTable node.
+     * doesn't even need a ModifyTable node.  But, if the query has RETURNING,
+     * we need to cons up a suitable pathtarget, else the finished plan will
+     * appear to return the wrong column set.
      */
     if (subpaths == NIL)
     {
+        if (parse->returningList)
+        {
+            final_rel->reltarget = create_pathtarget(root,
+                                                     parse->returningList);
+            /* hack to keep createplan.c from breaking things: */
+            root->processed_tlist = parse->returningList;
+        }
         set_dummy_rel_pathlist(final_rel);
         return;
     }
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f259d07..9d610b8 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -539,6 +539,41 @@ CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
 INSERT INTO z VALUES (NULL, 'text'); -- should fail
 ERROR:  null value in column "aa" violates not-null constraint
 DETAIL:  Failing row contains (null, text).
+-- Check inherited UPDATE with all children excluded
+create table some_tab (a int, b int);
+create table some_tab_child () inherits (some_tab);
+insert into some_tab_child values(1,2);
+explain (verbose, costs off)
+update some_tab set a = a + 1 where false;
+        QUERY PLAN
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+update some_tab set a = a + 1 where false;
+explain (verbose, costs off)
+update some_tab set a = a + 1 where false returning b, a;
+            QUERY PLAN
+----------------------------------
+ Result
+   Output: some_tab.b, some_tab.a
+   One-Time Filter: false
+(3 rows)
+
+update some_tab set a = a + 1 where false returning b, a;
+ b | a
+---+---
+(0 rows)
+
+table some_tab;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+drop table some_tab cascade;
+NOTICE:  drop cascades to table some_tab_child
 -- Check UPDATE with inherited target and an inherited source table
 create temp table foo(f1 int, f2 int);
 create temp table foo2(f3 int) inherits (foo);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 425052c..5480fe7 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -97,6 +97,21 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
 INSERT INTO z VALUES (NULL, 'text'); -- should fail

+-- Check inherited UPDATE with all children excluded
+create table some_tab (a int, b int);
+create table some_tab_child () inherits (some_tab);
+insert into some_tab_child values(1,2);
+
+explain (verbose, costs off)
+update some_tab set a = a + 1 where false;
+update some_tab set a = a + 1 where false;
+explain (verbose, costs off)
+update some_tab set a = a + 1 where false returning b, a;
+update some_tab set a = a + 1 where false returning b, a;
+table some_tab;
+
+drop table some_tab cascade;
+
 -- Check UPDATE with inherited target and an inherited source table
 create temp table foo(f1 int, f2 int);
 create temp table foo2(f3 int) inherits (foo);