MySQL Bugs: #67866: typical subquery does not work properly !

Bug #67866	typical subquery does not work properly !
Submitted:	10 Dec 2012 20:31	Modified:	18 Jan 2013 11:10
Reporter:	Bez Pek	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.28, 5.5.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
subquery does not work properly
error can damage many projects

try this:

prepare:
--------

create table __test1
 ( id int not null auto_increment,
   a int,
   b int,
   c int,
   primary key (id),
   key idx_a (a),
   key idx_b (b),
   key idx_c (c)
 )
engine = innodb
default character set = latin2;

insert into __test1
(id,a,b,c)
values (1,1,1,1),(2,1,1,2),(3,1,1,3);

faulting query:
---------------

select 
(select id from __test1 t1
 where t1.a=1 and t1.b=1
 order by t1.c desc
 limit 1)
from (select 1 as a) as t

query result:
-------------

mysql v 5.5.28: 1 (FAIL)
mysql v 5.5.25: 3 (OK)

How to repeat:
see description
each test fails (on 5.5.28)

faulting query: (fix)
----------------------

select 
(select id from __test1 t1
 where t1.a=t.a and t1.b=1
 order by t1.c desc
 limit 1)
from (select 1 as a) as t

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.28    |
+-----------+
1 row in set (0.00 sec)

mysql> create table __test1
    ->  ( id int not null auto_increment,
    ->    a int,
    ->    b int,
    ->    c int,
    ->    primary key (id),
    ->    key idx_a (a),
    ->    key idx_b (b),
    ->    key idx_c (c)
    ->  );
Query OK, 0 rows affected (2.65 sec)

mysql> insert into __test1
    -> (id,a,b,c)
    -> values (1,1,1,1),(2,1,1,2),(3,1,1,3);
Query OK, 3 rows affected (0.35 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select
    -> (select id from __test1 t1
    ->  where t1.a=1 and t1.b=1
    ->  order by t1.c desc
    ->  limit 1)
    -> from (select 1 as a) as t;
+-------------------------------------------------------------------------------
----+
| (select id from __test1 t1
 where t1.a=1 and t1.b=1
 order by t1.c desc
 limit 1) |
+-------------------------------------------------------------------------------
----+
|
  3 |
+-------------------------------------------------------------------------------
----+
1 row in set (0.27 sec)

The above is correct, now let's join instead to a single row outer table created on the fly:

mysql> select
    -> (select id from __test1 t1
    ->  where t1.a=t.a and t1.b=1
    ->  order by t1.c desc
    ->  limit 1)
    -> from (select 1 as a) as t;
+-------------------------------------------------------------------------------
------+
| (select id from __test1 t1
 where t1.a=t.a and t1.b=1
 order by t1.c desc
 limit 1) |
+-------------------------------------------------------------------------------
------+
|
    1 |
+-------------------------------------------------------------------------------
------+
1 row in set (0.04 sec)

mysql> explain extended select
    -> (select id from __test1 t1
    ->  where t1.a=t.a and t1.b=1
    ->  order by t1.c desc
    ->  limit 1)
    -> from (select 1 as a) as t;
+----+--------------------+------------+--------+---------------+-------+-------
--+------+------+----------+-----------------------------+
| id | select_type        | table      | type   | possible_keys | key   | key_le
n | ref  | rows | filtered | Extra                       |
+----+--------------------+------------+--------+---------------+-------+-------
--+------+------+----------+-----------------------------+
|  1 | PRIMARY            | <derived3> | system | NULL          | NULL  | NULL
  | NULL |    1 |   100.00 |                             |
|  3 | DERIVED            | NULL       | NULL   | NULL          | NULL  | NULL
  | NULL | NULL |     NULL | No tables used              |
|  2 | DEPENDENT SUBQUERY | t1         | range  | idx_a,idx_b   | idx_a | 5
  | NULL |    3 |   100.00 | Using where; Using filesort |
+----+--------------------+------------+--------+---------------+-------+-------
--+------+------+----------+-----------------------------+
3 rows in set, 2 warnings (0.16 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 't.a' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select (select `test`.`t1`.`id` from `test`.`__test1` `t1` where ((`tes
t`.`t1`.`b` = 1) and (`test`.`t1`.`a` = '1')) order by `test`.`t1`.`c` desc limi
t 1) AS `(select id from __test1 t1
 where t1.a=t.a and t1.b=1
 order by t1.c desc
 limit 1)` from dual
2 rows in set (0.00 sec)

Surely, rewritten SELECT from show warnings above works correctly:

mysql> select (select `test`.`t1`.`id` from `test`.`__test1` `t1` where ((`test`
.`t1`.`b` = 1) and (`test`.`t1`.`a` = '1')) order by `test`.`t1`.`c` desc limit
1) AS `something` from dual
    -> ;
+-----------+
| something |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

try only please:
----------------

select 
(select id from __test1 t1
 where t1.a=t.a and t1.b=1
 order by t1.c desc
 limit 1)
from (select 1 as a) as t

this is query demonstrating bug
i am sorry for initial mismatch
query with 't1.a=1' is only for test with ok result

Thank you for the report.

Verified as described with query:

select 
(select id from __test1 t1
 where t1.a=t.a and t1.b=1
 order by t1.c desc
 limit 1)
from (select 1 as a) as t;

Only 5.5 series is affected: earlier versions and trunk work fine.

test case for MTR

Attachment: bug67866.test (application/octet-stream, text), 436 bytes.

related or even a duplicate of this is:
http://bugs.mysql.com/bug.php?id=67958

This is caused by the same issue as reported in Bug#66845.

Closing this as duplicate of Bug#66845.