MySQL Bugs: #14076: wrong delete when column names are ambiguous

Bug #14076	wrong delete when column names are ambiguous
Submitted:	17 Oct 2005 16:40	Modified:	18 Oct 2005 9:19
Reporter:	Shantanu Oak	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0+	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The query is deleting all the records from table.
Even if the brackets () are there, it takes all the invoiceno values from bill table.
This behaviour is not expected since it does not follow the brackets.
It should show an error since there is no invoiceno column in the receipts table.

How to repeat:
drop table bill;
drop table receipts;

CREATE TABLE `bill` (
`invoiceno` int(11), `amount` decimal(10,2) 
);

CREATE TABLE `receipts` (
`cheque` int(11), `amount` decimal(10,2) 
);

insert into bill values (1,23), (2,43), (5,39);
insert into receipts values (10,23), (20,43), (5,9);

select * from bill;
select * from receipts;

DELETE FROM 
bill
WHERE invoiceno IN ( SELECT 
invoiceno
FROM receipts);

select * from bill;

It is deleting all the records from bill table.
Even if the brackets () are there, it takes all the invoiceno values from bill table.
This behaviour is not expected since it does not follow the brackets.
It should show an error since there is no invoiceno column in the receipts table.

The correct query here was...

DELETE FROM 
bill
WHERE invoiceno IN ( SELECT 
cheque
FROM receipts);

If I add the table names then the query will show an error message as expected.

DELETE FROM 
bill
WHERE bill.invoiceno IN ( SELECT 
receipts.invoiceno
FROM receipts);

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:

Sorry, but there is NO ambiguity among column names. Your 

Look:

mysql> CREATE TABLE `bill` (
    -> ``invoiceno` int(11), `amount` decimal(10,2)
    -> );

Query OK, 0 rows affected (0,03 sec)

mysql> CREATE TABLE `receipts` (
    -> `cheque` int(11), `amount` decimal(10,2)
    -> );

iQuery OK, 0 rows affected (0,01 sec)

mysql> insert into bill values (1,23), (2,43), (5,39);
Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into receipts values (10,23), (20,43), (5,9);
Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from bill
    -> where invoiceno in (select invoiceno from receipts);
+-----------+--------+
| invoiceno | amount |
+-----------+--------+
|         1 |  23.00 |
|         2 |  43.00 |
|         5 |  39.00 |
+-----------+--------+
3 rows in set (0,01 sec)

This is a kind of a query to try before real DELETE, by the way. Why all rows are selected. Let's explain:

mysql> explain select * from bill where invoiceno in (select invoiceno from rece
ipts);
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | bill     | ALL  | NULL          | NULL |    NULL | NULL |    3 | Using where |
|  2 | DEPENDENT SUBQUERY | receipts | ALL  | NULL          | NULL |    NULL | NULL |    3 | Using where |
+----+--------------------+----------+------+---------------+------+---------+--
----+------+-------------+
2 rows in set (0,01 sec)

So, your subquery is dependent, that is, it is executed once for each row from the bill table, like this:

mysql> select * from bill where invoiceno in (select bill.invoiceno from receipts);
+-----------+--------+
| invoiceno | amount |
+-----------+--------+
|         1 |  23.00 |
|         2 |  43.00 |
|         5 |  39.00 |
+-----------+--------+
3 rows in set (0,00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.14-standard |
+-----------------+
1 row in set (0,00 sec)

Because there is no column invoice no in your receipts table...

It is your fault, not a MySQL bug in any case.

Yes, It's technical not a bug. 

CREATE TABLE `bill` (
`invoiceno` int(11), `amount` decimal(10,2));

CREATE TABLE `receipts` (
`invoiceno` int(11), `amount` decimal(10,2) );

insert into bill values (1,23), (2,43), (5,39);

insert into receipts values (10,23), (20,43), (5,9);

select invoiceno from bill 
inner join receipts 
where bill.invoiceno = receipts.invoiceno;

select invoiceno from bill 
where invoiceno in (select invoiceno from receipts);

In the above example the inner join doesn't work where as "DEPENDENT SUBQUERY" does work. I wanted to note and mention the unexpected behavior. (unexpected, from general user’s point of view and not from an expert’s point of view)