Description:
I created following two tables:
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`fid` int(11) NOT NULL,
PRIMARY KEY (`id`));
Inserted following data:
insert into t1 values(1);
insert into t2 values(1,7503),(2,1);
Run query below for three cases: subquery optimizations disabled, only materialization
enabled and only semi-join enabled.
For first two cases we got a error(that is ok, there is no fid column in the outer table )
but query succeed for semijoin(that is not ok).
query: explain select count(*) from t1 where fid IN (select fid from t2 where (id between
7502 and 8420) order by fid ) ;
subquery optimizations disabled
-------------------------------
set @@optimizer_switch='no_semijoin,no_materialization';
<query>
ERROR 1054 (42S22): Unknown column 'fid' in 'IN/ALL/ANY subquery'
only materialization enabled
----------------------------
set @@optimizer_switch='no_semijoin';
<query>
ERROR 1054 (42S22): Unknown column 'fid' in 'IN/ALL/ANY subquery'
semi-join enabled
-----------------
set @@optimizer_switch='no_materialization';
<query>
+----+-------------+-------+--------+---------------+------+---------+------+------+------
------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+--------+---------------+------+---------+------+------+------
------------------------+
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 |
|
| 1 | PRIMARY | t2 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using
where; FirstMatch(t1) |
+----+-------------+-------+--------+---------------+------+---------+------+------+------
------------------------+
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------
------------------------------------------------+
| Level | Code | Message
|
+-------+------+--------------------------------------------------------------------------
------------------------------------------------+
| Note | 1003 | select count(0) AS `count(*)` from `test`.`t1` semi join (`test`.`t2`)
where (`test`.`t2`.`id` between 7502 and 8420) |
+-------+------+--------------------------------------------------------------------------
------------------------------------------------+
How to repeat:
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`fid` int(11) NOT NULL,
PRIMARY KEY (`id`));
insert into t1 values(1);
insert into t2 values(1,7503),(2,1);
set @@optimizer_switch='no_materialization';
explain select count(*) from t1 where fid IN (select fid from t2 where (id between 7502
and 8420) order by fid );
Description: I created following two tables: CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `t2` ( `id` int(11) NOT NULL, `fid` int(11) NOT NULL, PRIMARY KEY (`id`)); Inserted following data: insert into t1 values(1); insert into t2 values(1,7503),(2,1); Run query below for three cases: subquery optimizations disabled, only materialization enabled and only semi-join enabled. For first two cases we got a error(that is ok, there is no fid column in the outer table ) but query succeed for semijoin(that is not ok). query: explain select count(*) from t1 where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid ) ; subquery optimizations disabled ------------------------------- set @@optimizer_switch='no_semijoin,no_materialization'; <query> ERROR 1054 (42S22): Unknown column 'fid' in 'IN/ALL/ANY subquery' only materialization enabled ---------------------------- set @@optimizer_switch='no_semijoin'; <query> ERROR 1054 (42S22): Unknown column 'fid' in 'IN/ALL/ANY subquery' semi-join enabled ----------------- set @@optimizer_switch='no_materialization'; <query> +----+-------------+-------+--------+---------------+------+---------+------+------+------ ------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+------+---------+------+------+------ ------------------------+ | 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | t2 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; FirstMatch(t1) | +----+-------------+-------+--------+---------------+------+---------+------+------+------ ------------------------+ mysql> show warnings; +-------+------+-------------------------------------------------------------------------- ------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------- ------------------------------------------------+ | Note | 1003 | select count(0) AS `count(*)` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`id` between 7502 and 8420) | +-------+------+-------------------------------------------------------------------------- ------------------------------------------------+ How to repeat: CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `t2` ( `id` int(11) NOT NULL, `fid` int(11) NOT NULL, PRIMARY KEY (`id`)); insert into t1 values(1); insert into t2 values(1,7503),(2,1); set @@optimizer_switch='no_materialization'; explain select count(*) from t1 where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid );