Description:
drop table if exists test1;
drop table if exists test2;
create table test1(id int auto_increment primary key, fk_id int);
insert into test1 (fk_id) values (1),(2),(3);
create table test2(test2_id int auto_increment primary key, fk_val int);
insert into test2(fk_val) values (1),(2),(3);
-- This parses as a DEPENDENT SUBQUERY
-- It shouldn't be dependent. It should be a regular SUBQUERY, resolved first -- (and the parse should fail) but given the name resolution rules, it appears
-- that outer resolves after inner and this query works because of that.
-- this may or may not be a bug.
explain
select * from test1
where id = (select fk_id from test2 where fk_val = 2);
-- +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
-- | 1 | PRIMARY | test1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
-- | 2 | DEPENDENT SUBQUERY | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
-- +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
-- 2 rows in set (0.00 sec)
-- returns three rows. I only expected 1 (or an error)
select * from test1;
-- +----+-------+
-- | id | fk_id |
-- +----+-------+
-- | 1 | 1 |
-- | 2 | 2 |
-- | 3 | 3 |
-- +----+-------+
-- 3 rows in set (0.00 sec)
-- Add a column with the same name as the outer table
-- I hoped this would result in an ambiguous column error given the
-- resolution rules of inner/outer and the column being defined in both
-- tables
alter table test2 add fk_id int default 1;
-- This now parses as a SUBQUERY because fk_id is located in the
-- both tables, but it uses the column from the second table and never
-- looks to see that the column is ambiguous with the outer
-- I would expect a warning or an error because the column is ambiguous
explain
select * from test1
where id = (select fk_id from test2 where fk_val = 2);
-- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
-- | 1 | PRIMARY | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
-- | 2 | SUBQUERY | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
-- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
-- 2 rows in set (0.00 sec)
-- returns only 1 row
select * from test1
where id = (select fk_id from test2 where fk_val = 2);
-- +----+-------+
-- | id | fk_id |
-- +----+-------+
-- | 1 | 1 |
-- +----+-------+
-- 1 row in set (0.00 sec)
How to repeat:
run above script
