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