Thread

1. MySQL/PostgreSQL discrepancy

   Ken Smith <ken@turbolinux.co.jp> — 2000-10-27T06:28:33Z

   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?
   
   

2. Re: MySQL/PostgreSQL discrepancy

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-10-28T04:21:07Z

   > 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."
   
   
   
   

3. Re: MySQL/PostgreSQL discrepancy

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-28T04:39:53Z

   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
   
   

4. Re: MySQL/PostgreSQL discrepancy

   Philip Warner <pjw@rhyme.com.au> — 2000-10-28T05:48:59Z

   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   |/