MySQL Bugs: #39316: executing a delete command with incomplete WHERE clause deletes whole table

Bug #39316	executing a delete command with incomplete WHERE clause deletes whole table
Submitted:	8 Sep 2008 15:17	Modified:	8 Sep 2008 16:19
Reporter:	Sandro Frattura	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	All (5.1.26 most recent)	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	delete, incomplete, logical operator, where, where clause

Description:
if you type any delete command and reference a column with no logical stmt following, the entire table gets deleted:

delete from <table> where <col>

--the whole table is deleted

How to repeat:
create table TBL1 (myCol1 int(10) NULL);
insert TBL1 values (1);
insert TBL1 values (2);
insert TBL1 values (3);
delete from sfsf where myCol1;
-----Query OK, 3 rows affected (0.00 sec)

Suggested fix:
in other DBs, you get a syntax error if you try a where clause without any logical operators

(in ORACLE, you get "ORA-00920: invalid relational operator")
(in MS SQL, I am sure you get something similar, but I can not test that here)

The suggested fix is to have MySql look for logical operators after the where clause. if there are none, it should return an error similar to the Oracle error above.

Thank you for the bug report. This is expected behavior, if you want to change that behavior then use the option:

-U, --safe-updates  Only allow UPDATE and DELETE that uses keys

c:\dbs>5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >" -U test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >create table TBL1 (myCol1 int(10) NULL);
Query OK, 0 rows affected (0.05 sec)

mysql 5.1 >insert TBL1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >insert TBL1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >insert TBL1 values (3);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >delete from TBL1 where mycoll;
ERROR 1054 (42S22): Unknown column 'mycoll' in 'where clause'
mysql 5.1 >

Sorry the correct message according test case is:

mysql 5.1 >delete from TBL1 where mycol1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql 5.1 >

In MySQL, when you write "where myCol1", column is casted to a boolean and is used  as where condition. Practically your where clause is equivalent to "where myCol1 <> 0". Try, for example, SELECT * FROM TBL1 WHERE myCol1.

And check the manual.