Обсуждение: MySQL/PostgreSQL discrepancy

I believe I have found a bug.  I don't know whether the bug is with
PostgreSQL, MySQL, or the SQL specification.  Please help me to
understand what I have.  Thank you.

THE TEST SYSTEM
PostgreSQL version: 7.0.2
Platform: PentiumIII550x2, 512MB SDRAM, Linux2.2.13, glibc-2.1.2
[ken@ken ken]$ ldd /usr/bin/psql
        libpq.so.2.1 => /usr/lib/libpq.so.2.1 (0x4001b000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x4002d000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x4005a000)
        libdl.so.2 => /lib/libdl.so.2 (0x40071000)
        libm.so.6 => /lib/libm.so.6 (0x40074000)
        libutil.so.1 => /lib/libutil.so.1 (0x40091000)
        libreadline.so.4.0 => /usr/lib/libreadline.so.4.0 (0x40095000)
        libncurses.so.5 => /usr/lib/libncurses.so.5 (0x400ba000)
        libc.so.6 => /lib/libc.so.6 (0x400fb000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

THE TEST
Run 'psql', then enter the following except for the select statement
output.

---BEGIN---
create table test (name char, a int, b int);
insert into test values ('x', 1, 2);
select * from test;
update test set a=3,b=a where name='x';
insert into test values ('y', 1, 2);
update test set b=a,a=3 where name='y';
select * from test;
 name | a | b
------+---+---
 x    | 3 | 1
 y    | 3 | 1
drop table test;
---END---

I am used to MySQL so I expected the following from the select
statement.
 name | a | b
------+---+---
 x    | 3 | 3
 y    | 3 | 1

Which behavior is the correct?

Ken Smith <ken@turbolinux.co.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

At 15:28 27/10/00 +0900, Ken Smith wrote:
>
>Which behavior is the correct?
>

To quote from the stanard:

    "Each <update source> is effectively evaluated for the current
    row before any of the current row's object rows is updated"

So I'd say MySQL is wrong.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

> THE TEST
> Run 'psql', then enter the following except for the select statement
> output.
>
> ---BEGIN---
> create table test (name char, a int, b int);
> insert into test values ('x', 1, 2);
> select * from test;
> update test set a=3,b=a where name='x';
> insert into test values ('y', 1, 2);
> update test set b=a,a=3 where name='y';
> select * from test;
>  name | a | b
> ------+---+---
>  x    | 3 | 1
>  y    | 3 | 1
> drop table test;
> ---END---
>
> I am used to MySQL so I expected the following from the select
> statement.
>  name | a | b
> ------+---+---
>  x    | 3 | 3
>  y    | 3 | 1
>
> Which behavior is the correct?

I believe ours...
From update positioned:
"The <value expression>s are effecitvely evaluated before
updating the object row.  If a <value expression> contains a
reference to a column of T, then the reference is to the value
of that column in the object row before any value of the object
row is updated."

From update searched:
"The <value expressions> are effectively evaluated for each row
 of T before updating any row of T."