Bug #79502 query with materialized table returns incorrect results in 5.6
Submitted: 3 Dec 2015 9:26 Modified: 28 Apr 2016 14:22
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression, wrong results

[3 Dec 2015 9:26] Shane Bester
Description:
Only affects 5.6.   5.5 and 5.7 return same results.
Testcase should return same results for both queries, but we have:

5.6.27:

mysql> select * from t where a in
    -> (
    ->  select a from t where concat(a,'') not in
    ->  (select concat(a,'') from t where b=1)
    -> );
+------+------+
| a    | b    |
+------+------+
|    1 |    0 |
|    1 |    0 |
|    1 |    0 |
|    1 |    0 |
|    1 |    1 |
+------+------+
5 rows in set (0.03 sec)

mysql> select * from t where a in
    -> (
    ->  select p.cc from
    ->  (
    ->    select a cc from t where concat(a,'') not in
    ->    (select concat(a,'') from t where b=1)
    ->  )p
    -> );
Empty set (0.00 sec)

--------------------
5.7.9 is okay:
--------------
mysql> select * from t where a in
    -> (
    ->  select a from t where concat(a,'') not in
    ->  (select concat(a,'') from t where b=1)
    -> );
Empty set (0.10 sec)

mysql> select * from t where a in
    -> (
    ->  select p.cc from
    ->  (
    ->    select a cc from t where concat(a,'') not in
    ->    (select concat(a,'') from t where b=1)
    ->  )p
    -> );
Empty set (0.00 sec)

How to repeat:
drop table if exists t;
create table t(a int,b int) engine=innodb;
insert into t values (1,0),(1,0),(1,0),(1,0),(1,1);
select * from t where a in
(
 select a from t where concat(a,'') not in
 (select concat(a,'') from t where b=1)
);

select * from t where a in
(
 select p.cc from
 (
   select a cc from t where concat(a,'') not in
   (select concat(a,'') from t where b=1)
 )p
);
[28 Apr 2016 14:22] Paul DuBois
Posted by developer:
 
Noted in 5.6.31 changelog.

For debug builds, merging a derived table into an outer query block
could raise an assertion.