MySQL Bugs: #64726: sub-query not failing when it should be

Bug #64726	sub-query not failing when it should be
Submitted:	21 Mar 2012 19:43	Modified:	26 Mar 2012 22:32
Reporter:	James Goatcher	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.61 and 5.5.21	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
 A query that should fail, and does, on its own is *not* failing when incorporated as a sub-query into another query.

How to repeat:
-- mysql version 5.1.61, default install
-- mysql version 5.5.21, default install

use test;

drop table if exists stream_item;
drop table if exists stream;

CREATE TABLE stream_item (
  stream_item_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  stream_item_subject_type_id int(10) unsigned NOT NULL,
  PRIMARY KEY (stream_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE stream (
  stream_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  stream_item_id int(10) unsigned NOT NULL,
  PRIMARY KEY (stream_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into stream_item (stream_item_subject_type_id) values (  1 );
insert into stream_item (stream_item_subject_type_id) values (  4 );
insert into stream_item (stream_item_subject_type_id) values ( 12 );

insert into stream (stream_item_id) values (  1 );
insert into stream (stream_item_id) values (  4 );
insert into stream (stream_item_id) values (  7 );
insert into stream (stream_item_id) values ( 19 );
insert into stream (stream_item_id) values ( 27 );

select * from stream_item;
select * from stream;

-- Everything to this point is good, normal and as expected.

-- The following statement will generate an error along the lines of:
--    ERROR 1054 (42S22) ... Unknown column 'stream_id' in ...
-- because there is no column named stream_id in table stream_item.
-- This is good.

select stream_id from stream_item
WHERE  stream_item_subject_type_id = 12;

-- The following statement *should* generate a similar error because 
-- there is no column named stream_id in table stream_item, but it 
-- doesn't.  Instead it returns all rows from table stream.
-- This is NOT good.

select * from stream
    where stream_id in (
       select stream_id from stream_item
       WHERE  stream_item_subject_type_id = 12
    )
;

-- The following statement *should* generate a similar error because 
-- there is no column named stream_id in table stream_item, but it 
-- doesn't.  Instead it deletes all rows from table stream.
-- This is NOT good.

delete from stream
    where stream_id in (
       select stream_id from stream_item
       WHERE  stream_item_subject_type_id = 12
    )
;

-- The following statement *should* return the same rows as it
-- does up above, but it doesn't because the rows are gone.
select * from stream;

Suggested fix:
Make an errant sub-query like this cause an error for the whole query.

Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html. Subquery has access to columns from outer query.

Yes, this is a bug.  Try my code.

1) Neither of my sub-querys is a correlated subquery.
2) The web page you refer says that "MySQL evaluates from inside to outside".  If this is the case, then the inside query should cause the entire query to fail.

This is a bug.  See my comment above.

Any subquery is correlated unless it does not refer to columns from outer tables. Your subquery refers to column name that is NOT present in table mentioned in its FROM clause, so search goes to outer table to find out that name can be resolved.

Do you know any other database that treats your query differently and returns error message?

Postgres produced the same results as MySQL, so I can only conclude that this is expected behavior.  I was not able to find an Oracle resource to run it on.  Is there a link you can give me showing the ANSI standard on which this behavior is based?