Bug #15928 update syntax error not detected

• Submitted: 22 Dec 2005 8:26
• Modified: 22 Dec 2005 9:58
• Reporter: jo soares
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.24_Debian-10sarge1-log
• OS: Linux (Linux Debian 2.6.9 i686)
• Assigned to: Aleksey Kishkin
• CPU Architecture: Any

[22 Dec 2005 8:26] jo soares

Description:
The following query has changed data by chance in an entire table.

It seems to me this query has an error not detected by mysql and it updates the orecchio_ristampa with the value '0' in all rows of table contabilita.

UPDATE contabilita SET orecchio_ristampa='pippo'
 AND data_lotto='2005-12-15'
 AND data_consegna_maior=''
 AND data_fattura='2005-12-16'
 AND from_file=''
 AND data_invio_teramo='0000-00-00'
 AND numero_fattura='22185'
 AND importo_fattura='110.380'
 AND priorita='1'

How to repeat:

UPDATE contabilita SET orecchio_ristampa='pippo'
 AND data_lotto='2005-12-15'
 AND data_consegna_maior=''
 AND data_fattura='2005-12-16'
 AND from_file=''
 AND data_invio_teramo='0000-00-00'
 AND numero_fattura='22185'
 AND importo_fattura='110.380'
 AND priorita='1'

[22 Dec 2005 8:42] Aleksey Kishkin

Jo@ , could you put here definition of the table 'contabilita' that we could try to reproduce this bug?

What engine do you use for this table? Did you check if your indexes are ok?

[22 Dec 2005 9:00] jo soares

My table definition.... the indexex are ok.

create table contabilita (
 orecchio_ristampa   char(1),
 data_lotto          date,
 data_consegna_maior date,
 data_fattura        date,
 from_file           integer,
 data_invio_teramo   date,
 numero_fattura      integer,
 importo_fattura     decimal(10,3),
 priorita            integer
);

[22 Dec 2005 9:02] jo soares

I'm using mysql client in my linux box.

[22 Dec 2005 9:06] jo soares

I suppose mysql executes an AND  binary instruction here,
every AND in this query returns FALSE then orecchio_ristampa should become FALSE but mysql change the FALSE value in '0'.
I wonder if is there a way to disable this so dangerous autocasting in mysql.

[22 Dec 2005 9:23] Aleksey Kishkin

You should use comma instead of AND here.

[22 Dec 2005 9:58] jo soares

I know, but it was an involuntary mistake that changed all data in my table.