Skip site navigation (1) Skip section navigation (2)

Re: MySQL/PostgreSQL discrepancy

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 (view raw or flat)
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

In response to

pgsql-bugs by date

Next:From: Philip WarnerDate: 2000-10-28 05:48:59
Subject: Re: MySQL/PostgreSQL discrepancy
Previous:From: Stephan SzaboDate: 2000-10-28 04:21:07
Subject: Re: MySQL/PostgreSQL discrepancy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group