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