From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ken Smith <ken(at)turbolinux(dot)co(dot)jp> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: MySQL/PostgreSQL discrepancy |
Date: | 2000-10-28 04:39:53 |
Message-ID: | 6747.972707993@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Ken Smith <ken(at)turbolinux(dot)co(dot)jp> writes:
> create table test (name char, a int, b int);
> insert into test values ('x', 1, 2);
> update test set a=3,b=a where name='x';
> select * from test;
> name | a | b
> ------+---+---
> x | 3 | 1
This is correct ...
> I am used to MySQL so I expected the following from the select
> statement.
> name | a | b
> ------+---+---
> x | 3 | 3
> Which behavior is the correct?
MySQL is evidently evaluating the assignments left-to-right, and using
the updated values of prior columns in subsequent expressions.
Unfortunately for MySQL, that is unquestionably a violation of the SQL
spec. I refer you to SQL92 section 3.10 <update statement: searched>,
General Rule 6:
6) The <value expression>s are effectively evaluated for each row
of T before updating any row of T.
There is no other way to read that except that the expressions are all
to be evaluated using the *old* values of the row.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Philip Warner | 2000-10-28 05:48:59 | Re: MySQL/PostgreSQL discrepancy |
Previous Message | Stephan Szabo | 2000-10-28 04:21:07 | Re: MySQL/PostgreSQL discrepancy |