Bug #35767 Processing of uncorrelated subquery with semi-join cause wrong result and crash

• Submitted: 2 Apr 2008 10:55
• Modified: 20 Nov 2010 23:06
• Reporter: Alexey Stroganov
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 6.0.5pre
• OS: Any
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[2 Apr 2008 10:55] Alexey Stroganov

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 );

[5 Apr 2008 23:19] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44956

ChangeSet@1.2624, 2008-04-06 03:19:24+04:00, [e-mail] +3 -0
  BUG#35767 "Processing of uncorrelated subquery with semi-join cause wrong result and crash"
  - When calling left_expr->fix_fields(), set the context to point to the 
    parent select (as left expression of IN subquery is located in the context
    of the parent select), in the same manner as  Item_in_subselect::
    single_value_transformer does. Also set thd->where appropriately.

[28 May 2008 10:02] Bugs System

Pushed into 6.0.6-alpha

[30 May 2008 18:24] Paul DuBois

Noted in 6.0.6 changelog.

Processing of an uncorrelated subquery using semi-join could cause
incorrect results or a server crash.

[16 Aug 2010 6:42] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:27] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

[20 Nov 2010 23:06] Paul DuBois

Noted in 5.6.1 changelog.

[23 Nov 2010 2:08] Paul DuBois

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.