Ron -- Thanks for the question regarding "Visibility of column changes in a an update", version 10.2.0

Submitted on 27-Oct-2009 11:14 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 30-Oct-2009 9:59

You Asked

Hi Tom,

consider

create table t( x int, y int ) ;
insert into t values ( 1, 1 ) ;
commit ;

update t set x = 2, y = x ;
commit ;
select * from t ;

results in :

x y
2 1

Looking at the update one would ( incorrectly ) expect that the lexical order results in y seeing x as 2. Why is it not so ? Because set theory does not have notion of ordering and lexical ordering is meaningless there ?

and we said...

It is because by definition the rvalues (right hand size values) are computed first and then the lvalues (left) are assigned to.

The standard says "this is so".

Reviews

Swap values October 28, 2009 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

And that's a good thing because it makes it very easy to swap values in two columns "update t set 
x=y, y=x"

Is there a compelling reason why the standard requires rvalue evaluation first ? October 29, 2009 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ron

Hi Tom,

Thanks for your earlier response.

Is there a compelling reason why the standard requires rvalue evaluation first ?

Followup October 30, 2009 - 9am Central time zone:

it only makes sense, there is no "order of operation of a where clause and such"

select * from t where x > 5 and y < 10 and z > 20;
select * from t where x > 5 and (y < 10 and z > 20);
select * from t where (x > 5 and y < 10) and z > 20;
select * from (select * from (select * from t where x>5) where y<10) where z>20;

those are all identical, you have no control over the order of evaluation.

it would not make any sense to have an order, it is not like a procedural language - things are not processed "right to left" or "left to right" or "bottom up" or "top down", they are done in any order we want.

November 6, 2009 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: Helena Marková from Bratislava, Slovakia

Write a Review