[Bug] Data-modifying statements Inside CTE is lost

[tuhaihe]

Cloudberry Database version

No response

What happened

see GPDB: https://github.com/greenplum-db/gpdb/issues/15899

What you think should happen instead

use PG to show example, there is no CTE scan node in GPDB, but should be a UPDATE node in plan.

explain(costs off) with cte1 as (update t3 a set c1=b.c2 from t1 b returning a.c1) select * from t2 join t1 using(c1);
                 QUERY PLAN
--------------------------------------------
 Merge Join
   Merge Cond: (t2.c1 = t1.c1)
   CTE cte1
     ->  Update on t3 a
           ->  Nested Loop
                 ->  Seq Scan on t3 a
                 ->  Materialize
                       ->  Seq Scan on t1 b
   ->  Sort
         Sort Key: t2.c1
         ->  Seq Scan on t2
   ->  Sort
         Sort Key: t1.c1
         ->  Seq Scan on t1
(14 rows)

Actual behavior

Lost UPDATE table.

How to reproduce

create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
create table t3(c1 int, c2 int);
explain(costs off) with cte1 as (update t3 a set c1=b.c2 from t1 b returning a.c1) select * from t2 join t1 using(c1);
                QUERY PLAN
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Hash Join
         Hash Cond: (t2.c1 = t1.c1)
         ->  Seq Scan on t2
         ->  Hash
               ->  Seq Scan on t1
 Optimizer: Postgres query optimizer
(7 rows)

The UPDATE is lost when we do not reference it in primary query. That's incorrect.

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output.

https://www.postgresql.org/docs/12/queries-with.html

Operating System

Linux

Anything else

No response

Are you willing to submit PR?

• [ ] Yes, I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.