List:General Discussion« Previous MessageNext Message »
From:Jorge Renato Machin Ibarra Date:March 19 1999 3:23pm
Subject:Re: Could youexplain it?
View as plain text  
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).
>

Thread
Could youexplain it?Jorge Renato Machin Ibarra19 Mar
  • Could youexplain it?Michael Widenius19 Mar
  • Re: Could youexplain it?Jorge Renato Machin Ibarra19 Mar