Re: BUG #13465: multi update query use CTE, result & plan not equal, BUG?

On Wed, Jun 24, 2015 at 8:27 PM, 德哥 <digoal(at)126(dot)com> wrote:

> But Why, The same SQL has two diff result?
> It's not a BUG?
>

​TBH, it might be but the pain I experience in trying to decipher your
example is too great for me to know for sure.

If you cannot simplify it even further I would suggest some improvements.

1. choose better data values and less complexity on the where/joins
2. avoid having CTE names that match up with your table names
3. minimize the table count altogether (i.e, 1) and simply use constants in
your where/joins

Note that #2 above may be why you are confused....

with t(c1,c2) as (update t1 set id=t2.id from t2 where t1.info=t2.info
returning t1.id,t1.info), t2(c3,c4) as (update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info) select * from t,t2;

You may also want to write more commentary, especially regarding what you
are expecting versus what you are seeing.

David J.