MySQL Bugs: #13983: Bug in a wrong update command

Bug #13983	Bug in a wrong update command
Submitted:	12 Oct 2005 21:36	Modified:	13 Oct 2005 12:46
Reporter:	Fabricio Mota	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.x.x	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Hi, the problem ocurred when I was trying to update an massive deal of data, using the latest version of MySQL (5.1.11). When I was typing the command, erroneously I typed the command wrong, so:

update localidade l
set cep_geral =
  (select cep
   from cepgroup1001 c
   where c.localidade = l.id
     and total in (select max(total) from cepgroup1001 cc
                   where cc.localidade = c.localidade
                     and cep <> 0)
   )
and (l.cep_geral = 'NA' or l.cep_geral = 'NE')

but note that the update where clause is wrong. I mean that, how the command really should be typed:

update localidade l
set cep_geral =
  (select cep
   from cepgroup1001 c
   where c.localidade = l.id
     and total in (select max(total) from cepgroup1001 cc
                   where cc.localidade = c.localidade
                     and cep <> 0)
   )
where (l.cep_geral = 'NA' or l.cep_geral = 'NE')

Thinking correctly, I would expect it returned an syntax error message, but the occured was: the server updated all record in the table (with no restrictions), with randomic values "0" and "1" in the referred column. Consider that at the original query, no zeros or ones are possible to be returned.

Thank you a lot.

How to repeat:
Try to update a table with an syntax error in the where clause, like that:

update TABLE
set COLUMN =
  (one row subquery......)
and (CONDITION1 or CONDITION2)

instead of:

update TABLE
set COLUMN =
  (one row subquery......)
where (CONDITION1 or CONDITION2)

Suggested fix:
I have no Idea of how to fix it, but I imagine this is a problem occurred in the SQL Interpreter.

Thank you very much.

Thank you for a problem report. I have some questions about the version, though.

Is it really 5.1.11? Or 5.0.11? Have you tried to repeat this behaviour on officially released 5.0.13-rc binaries?

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug.
The syntax is UPDATE ... SET column=expression
and what you typed is a valid expression. E.g.

  UPDATE TABLE t1 SET a=(select 1) AND (3>2);

'a' will be set to the value of (select 1) AND (3>2), that is to 1 AND 1, that is to 1.

Hi,

1. I'm sorry about the version. Once again I realized a typing error, but at this time, not in the SQL command (lot of laughs). The correct version which the bug ocurred was 5.0.11. You've got the reason.

2. About to test in a 5.0.13 server, I didn't try it because I have no one instaled, but I asked a friend who has one instaled at home, and he told me the problem continues.

3. I would like to have your help with the following: I have an Idea about some (low cost) functionalities I suggest to be included in MySQL Database Server, which will turn it into a powered competitive commercial tool, and help many enterprises in the world to solve some common and important problems envolving most of their applications. This challenges may revolution, with low cost of development, some concepts about 2 and 3-layers applications. I'm concluding a project, in a paper.
Could you help me which an e-mail contact of a people who could be interested on it?
Thank you so much.

Hi, about the (non) bug, I received an new email saying this is not a bug. Thank you very much. 

Would you tell me an conctact email to I send the project about new functionalities?

Thank you once again.

FM

You can submit them as a new ticket in this bugdb (just be sure to set the severity to "feature request").