Description:
Affects 5.6 5.5 and 5.7 was correct.
5.6 gives different results with materialization=on or off.
testcase outputs:
mysql> set session optimizer_switch='materialization=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' );
+---+
| f |
+---+
| 0 |
| 4 |
+---+
2 rows in set (0.03 sec)
mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' );
+---+
| f |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql>
mysql> set session optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)
mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' );
+---+
| f |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' );
+---+
| f |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
How to repeat:
drop table if exists t2;
create table t2 (
a varchar(9), b varchar(9), c varchar(9), d varchar(9),
primary key (a,d),key newindex1 (d)
) engine=myisam default charset=latin1;
drop table if exists t1;
create table t1 (
a varchar(9), e varchar(9), f varchar(9), d varchar(9),
primary key (a,e,f,d), key a (a), key d (d)
) engine=myisam default charset=latin1;
drop view if exists v1;
create view v1 as select a,b,c from t2 where t2.d = '1';
drop view if exists v2;
create view v2 as
select bi.a as a,bi.e as e,if(bi.a = '1','1',bi.f) as f
from t1 bi join v1 bm on bi.a = bm.a and bi.d = bm.c;
insert into t1 values ('0','0','3','2'),('0','0','4','3'),('0','2','0','3');
insert into t1 values ('0','2','2','0'),('0','3','0','3'),('0','3','2','0');
insert into t1 values ('0','4','0','2'),('1','0','0','1'),('1','0','2','4');
insert into t1 values ('1','2','1','2'),('1','3','0','3'),('1','3','1','4');
insert into t1 values ('1','4','0','3'),('1','4','3','0'),('2','0','2','1');
insert into t1 values ('2','3','1','3'),('2','4','1','0'),('3','0','1','2');
insert into t1 values ('3','1','0','4'),('3','2','4','1'),('3','3','3','2');
insert into t1 values ('3','3','4','4'),('3','4','0','3'),('4','1','3','4');
insert into t1 values ('4','2','2','0'),('4','2','4','0'),('4','3','0','2');
insert into t1 values ('4','3','0','3'),('4','4','1','1'),('4','4','2','3');
insert into t2 values ('3','0','4','2'),('0','2','3','1'),('3','4','1','1');
insert into t2 values ('4','0','2','2'),('1','4','3','4'),('1','3','1','0');
insert into t2 values ('2','3','3','1'),('4','3','0','4'),('4','4','2','1');
insert into t2 values ('0','2','1','0'),('1','3','2','2'),('3','4','3','3');
insert into t2 values ('4','2','3','3'),('2','2','0','0'),('2','0','1','2');
insert into t2 values ('0','1','0','3'),('2','2','2','3'),('4','3','2','0');
insert into t2 values ('3','1','0','0');
flush tables;
set session optimizer_switch='materialization=on';
select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' );
select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' );
set session optimizer_switch='materialization=off';
select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' );
select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' );
Description: Affects 5.6 5.5 and 5.7 was correct. 5.6 gives different results with materialization=on or off. testcase outputs: mysql> set session optimizer_switch='materialization=on'; Query OK, 0 rows affected (0.00 sec) mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' ); +---+ | f | +---+ | 0 | | 4 | +---+ 2 rows in set (0.03 sec) mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' ); +---+ | f | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql> mysql> set session optimizer_switch='materialization=off'; Query OK, 0 rows affected (0.00 sec) mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' ); +---+ | f | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql> select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' ); +---+ | f | +---+ | 0 | +---+ 1 row in set (0.00 sec) How to repeat: drop table if exists t2; create table t2 ( a varchar(9), b varchar(9), c varchar(9), d varchar(9), primary key (a,d),key newindex1 (d) ) engine=myisam default charset=latin1; drop table if exists t1; create table t1 ( a varchar(9), e varchar(9), f varchar(9), d varchar(9), primary key (a,e,f,d), key a (a), key d (d) ) engine=myisam default charset=latin1; drop view if exists v1; create view v1 as select a,b,c from t2 where t2.d = '1'; drop view if exists v2; create view v2 as select bi.a as a,bi.e as e,if(bi.a = '1','1',bi.f) as f from t1 bi join v1 bm on bi.a = bm.a and bi.d = bm.c; insert into t1 values ('0','0','3','2'),('0','0','4','3'),('0','2','0','3'); insert into t1 values ('0','2','2','0'),('0','3','0','3'),('0','3','2','0'); insert into t1 values ('0','4','0','2'),('1','0','0','1'),('1','0','2','4'); insert into t1 values ('1','2','1','2'),('1','3','0','3'),('1','3','1','4'); insert into t1 values ('1','4','0','3'),('1','4','3','0'),('2','0','2','1'); insert into t1 values ('2','3','1','3'),('2','4','1','0'),('3','0','1','2'); insert into t1 values ('3','1','0','4'),('3','2','4','1'),('3','3','3','2'); insert into t1 values ('3','3','4','4'),('3','4','0','3'),('4','1','3','4'); insert into t1 values ('4','2','2','0'),('4','2','4','0'),('4','3','0','2'); insert into t1 values ('4','3','0','3'),('4','4','1','1'),('4','4','2','3'); insert into t2 values ('3','0','4','2'),('0','2','3','1'),('3','4','1','1'); insert into t2 values ('4','0','2','2'),('1','4','3','4'),('1','3','1','0'); insert into t2 values ('2','3','3','1'),('4','3','0','4'),('4','4','2','1'); insert into t2 values ('0','2','1','0'),('1','3','2','2'),('3','4','3','3'); insert into t2 values ('4','2','3','3'),('2','2','0','0'),('2','0','1','2'); insert into t2 values ('0','1','0','3'),('2','2','2','3'),('4','3','2','0'); insert into t2 values ('3','1','0','0'); flush tables; set session optimizer_switch='materialization=on'; select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' ); select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' ); set session optimizer_switch='materialization=off'; select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e like '3' and a like '4' ); select sql_no_cache f from v2 where e like '2' and a in (select f from v2 where e='3' and a='4' );