Michael:
Thank you very much!
Michael Widenius wrote:
> <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).
>
