wCTE behaviour

Hi all,

The discussion around wCTE during the last week or so has brought to my 
attention that we don't actually have a consensus on how exactly wCTEs 
should behave.  The question seems to be whether or not a statement 
should see the modifications of statements ran before it.  While I think 
making the modifications visible would be a lot more intuitive, it's not 
clear how we'd optimize the execution in the future without changing the 
behaviour (triggers are a big concern).

I've done some digging today and it seems that IBM's DB2 took the more 
intuitive approach: all statements are ran, in the order they're written 
in, to completion before the main statement, materializing the "deltas" 
into a temporary table and the modifications are made visible to the 
next statements.

I have no idea how many complaints they have received about this 
behaviour, but I'd be in favor of matching it.

Thoughts?


Regards,
Marko Tiikkaja