Bug #74357 assertion failure in subselect_hash_sj_engine::exec with subquery in left argume
Submitted: 13 Oct 2014 13:30 Modified: 23 Feb 2015 15:11
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.5, 5.6.37 OS:Any
Assigned to: CPU Architecture:Any

[13 Oct 2014 13:30] Guilhem Bichot 
Description:
didn't try earlier versions. Using:
revision-id:norvald.ryeng@oracle.com-20141013122136-vzzemyd8syy2w47b

item_subselect.cc:3839: virtual bool subselect_hash_sj_engine::exec(): Assertion `item_in->left_expr->cols() == 1' failed.

How to repeat:
create table s(s int)engine=innodb;
insert into s values(1);
create table t like s;

set
optimizer_switch='materialization=on,subquery_materialization_cost_based=off';
select (select 1,2 from t) not in (select 1,2 from s) from s;
[23 Feb 2015 15:11] Paul DuBois 
Noted in 5.7.6 changelog.

Using "(row subquery1) NOT IN (row subquery2)" with NULL values in the
left argument could cause an assertion failure.
[14 Aug 2017 1:42] Roel Van de Paar 
If you execute the testcase twice in a row, it crashes 5.6.35.
[14 Aug 2017 1:44] Roel Van de Paar 
Also see bug 80440