Comment 1 for bug 823930

create table t1_1024 (a1 blob(1024), a2 blob(1024));
create table t2_1024 (b1 blob(1024), b2 blob(1024));
create table t3_1024 (c1 blob(1024), c2 blob(1024));

insert into t1_1024 values
 (concat('1 - 00', repeat('x', 1018)), concat('2 - 00', repeat('x', 1018)));
insert into t1_1024 values
 (concat('1 - 01', repeat('x', 1018)), concat('2 - 01', repeat('x', 1018)));
insert into t1_1024 values
 (concat('1 - 02', repeat('x', 1018)), concat('2 - 02', repeat('x', 1018)));

insert into t2_1024 values
 (concat('1 - 01', repeat('x', 1018)), concat('2 - 01', repeat('x', 1018)));
insert into t2_1024 values
 (concat('1 - 02', repeat('x', 1018)), concat('2 - 02', repeat('x', 1018)));
insert into t2_1024 values
 (concat('1 - 03', repeat('x', 1018)), concat('2 - 03', repeat('x', 1018)));

insert into t3_1024 values
 (concat('1 - 01', repeat('x', 1018)), concat('2 - 01', repeat('x', 1018)));
insert into t3_1024 values
 (concat('1 - 02', repeat('x', 1018)), concat('2 - 02', repeat('x', 1018)));
insert into t3_1024 values
 (concat('1 - 03', repeat('x', 1018)), concat('2 - 03', repeat('x', 1018)));
insert into t3_1024 values
 (concat('1 - 04', repeat('x', 1018)), concat('2 - 04', repeat('x', 1018)));

-- Correct result
set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off';

select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');

-- Wrong result
set @@optimizer_switch='semijoin=on,materialization=on,in_to_exists=off';

select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');