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