Description:
mysql> select ref_1.C_BLOB as c2 from sqltester_1.t2 as ref_0 inner join sqltester.key_pt_t0 as ref_1 on (EXISTS ( select 39 as c7, 65 as c8 from sqltester_1.t0_range_key_subpartition_sub_view as ref_7 where ref_7.c_credit is not NULL )) right join sqltester_1.tx_cmplx_002 as ref_8 on (true) where EXISTS ( select database() as c5 from sqltester.t10 as ref_11 );
+------------+
| c2 |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> select /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ ref_1.C_BLOB as c2 from sqltester_1.t2 as ref_0 inner join sqltester.key_pt_t0 as ref_1 on (EXISTS ( select /*+ QB_NAME(subq1) */ 39 as c7, 65 as c8 from sqltester_1.ot0_range_key_subpartition_sub_view as ref_7 where ref_7.c_credit
Empty set (0.00 sec)
How to repeat:
1. create table and insert data
create database sqltester;
use sqltester
source sqltester_for_parallel_with_index.2k_dp_210col.index_desc.sql
source sqltester_for_parallel_with_index_view_part_tab.sql
2.execute query
select ref_1.C_BLOB as c2 from sqltester_1.t2 as ref_0 inner join sqltester.key_pt_t0 as ref_1 on (EXISTS ( select 39 as c7, 65 as c8 from sqltester_1.t0_range_key_subpartition_sub_view as ref_7 where ref_7.c_credit is not NULL )) right join sqltester_1.tx_cmplx_002 as ref_8 on (true) where EXISTS ( select database() as c5 from sqltester.t10 as ref_11 ); ------right result
select /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ ref_1.C_BLOB as c2 from sqltester_1.t2 as ref_0 inner join sqltester.key_pt_t0 as ref_1 on (EXISTS ( select /*+ QB_NAME(subq1) */ 39 as c7, 65 as c8 from sqltester_1.t0_range_key_subpartition_sub_view as ref_7 where ref_7.c_credit is not NULL )) right join sqltester_1.tx_cmplx_002 as ref_8 on (true) where EXISTS ( select database() as c5 from sqltester.t10 as ref_11 );---------wrong result
