I recently came across this strange behaviour in Postgres. I had a table like the following one:
sasdb=# \d emp_manager_rel Table "db3004db.emp_manager_rel" Column | Type | Collation | Nullable | Default ------------+--------+-----------+----------+--------- emp_id | bigint | | | manager_id | bigint | | | select * from emp_manager_rel ; emp_id | manager_id --------+------------ 1 | 123
I executed the following update statement :
UPDATE 1:
update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;
which update the table like follow:
emp_id | manager_id --------+------------ 4 | 124
UPDATE 2: I executed the following query (on the original table, not on the updated)
update emp_manager_rel set emp_id=emp_id*4 , manager_id = manager_id+emp_id ;
it updates the table like follows:
emp_id | manager_id --------+------------ 4 | 124
I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.