From: Michael Widenius
Date: March 19 1999 11:25am
Subject: Could you
 	 explain it?
List-Archive: http://lists.mysql.com/mysql/581
Message-Id: <14066.12814.760710.739041@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


<cut>

mysql> select Nombre, Padre, idUsuario, url, min, hide, if
Jorge> (Nombre=Padre,0,1) as orden from umbLigasxUsr where
Jorge> idUsuario='dinosaurio' order by Padre, orden, Nombre;
Jorge> +--------------+----------+------------+---------------------+-----+------+-------+

Jorge> | Nombre       | Padre    | idUsuario  | url                 | min |
Jorge> hide | orden |
Jorge> +--------------+----------+------------+---------------------+-----+------+-------+

Jorge> | Buscador     | Buscador | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     0 |
Jorge> | Buscador_uno | Buscador | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> | Busqueda     | Buscador | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> | Libro        | Libro    | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     0 |
Jorge> | Tumbaburros  | Libro    | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> +--------------+----------+------------+---------------------+-----+------+-------+

Jorge> 5 rows in set (0.01 sec)

mysql> UPDATE umbLigasxUsr set
Jorge> Padre='Diccionario',Nombre=if(Padre=Nombre,'Diccionario',Nombre) where
Jorge> Padre='Libro';
Jorge> Query OK, 2 rows affected (0.00 sec)
Jorge> Rows matched: 2  Changed: 2  Warnings: 0

mysql> select Nombre, Padre, idUsuario, url, min, hide, if
Jorge> (Nombre=Padre,0,1) as orden from umbLigasxUsr where
Jorge> idUsuario='dinosaurio' order by Padre, orden, Nombre;
Jorge> +--------------+-------------+------------+---------------------+-----+------+-------+

Jorge> | Nombre       | Padre       | idUsuario  | url                 | min |
Jorge> hide | orden |
Jorge> +--------------+-------------+------------+---------------------+-----+------+-------+

Jorge> | Buscador     | Buscador    | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     0 |
Jorge> | Buscador_uno | Buscador    | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> | Busqueda     | Buscador    | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> | Libro        | Diccionario | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> | Tumbaburros  | Diccionario | dinosaurio | http:www.umbral.com | N   |
Jorge> N    |     1 |
Jorge> +--------------+-------------+------------+---------------------+-----+------+-------+

Jorge> 5 rows in set (0.01 sec)

Jorge> As you can see, the field "Nombre" on the last result table doesn't
Jorge> change the word "Libro" on row 4 for the word "Diccionario". Could you
Jorge> explain it? I think I'm doing everything right!!! By the way, I'm using
Jorge> MYSQL 3.22.14b.

<cut>

Hi!

This is 'probably' a bug in MySQL (but I am not sure).

The problem is that all updates are done from right to left.

In the case:

UPDATE umbLigasxUsr set Padre='Diccionario',
                        Nombre=if(Padre=Nombre,'Diccionario',Nombre)
	where Padre='Libro';

Padre is first updated to 'Diccionario';  After this the 'if' check
will fails becase Padre now contains the value 'Diccionario' and not
'Libro'.

Fix use:

UPDATE umbLigasxUsr set Nombre=if(Padre=Nombre,'Diccionario',Nombre),
       Padre='Diccionario' where Padre='Libro';

Regards,
Monty

PS: I just checked the SQL standard, and it says that MySQL does wrong 
    in the case :(

    We will have to change that in the future all columns
    refer to the original row value.  This will break all applications
    that uses this feature in the INSERT statement (but I assume there
    is very few applications that uses this).