Bug #85622 5.6: wrong results with materialization, views
Submitted: 25 Mar 2017 7:39 Modified: 4 Sep 2017 2:52
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.6.35 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 2017 7:39] Shane Bester
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' );
[4 Sep 2017 2:52] Paul Dubois
Posted by developer:
 
Fixed in 5.6.30, 5.7.20, 8.0.3.

Selecting from a view could yield different results with
materialization enabled versus materialization disabled.
[19 Oct 2017 8:04] Shane Bester
"Fixed in 5.6.30".  Hmm, I think you mean "5.6.38" there