Bug #31884 Assertion + crash in subquery in the SELECT clause.
Submitted: 26 Oct 2007 15:22 Modified: 29 Nov 2007 0:25
Reporter: Timour Katchaounov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.x-debug, 6.0-debug OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: crash, subquery

[26 Oct 2007 15:22] Timour Katchaounov
Description:
The query in the test case below causes an assertion to fail in
a debug build. The assertion is:
longlong Item_in_subselect::val_int()
{
  /*
    As far as Item_in_subselect called only from Item_in_optimizer this
    method should not be used
  */
  DBUG_ASSERT(0);
  .......
}

One should also check whether the correct result is returned both
in debug and non-debug builds.

How to repeat:
drop table if exists t1, t2;
create table t1 (a1 char(8), a2 char(8));
create table t2 (b1 char(8), b2 char(8));

insert into t1 values ('1 - 00', '2 - 00');
insert into t1 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 03', '2 - 03');

select ((a1,a2) IN (select * from t2 where b2 > 0)) IS NULL from t1;

Suggested fix:
Check why in this case we don't wrap Item_in_subselect inside an
Item_in_optimizer.
[30 Oct 2007 12:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36646

ChangeSet@1.2554, 2007-10-30 14:27:21+02:00, gkodinov@magare.gmz +3 -0
  Bug #31884: Assertion + crash in subquery in the SELECT clause.
  
  Item_in_subselect's only externally callable method is val_bool().
  However the nullability in the wrapper class (Item_in_optimizer) is 
  established by calling the "forbidden" method val_int().
  
  Fixed to use the correct method (val_bool() ) to establish nullability 
  of Item_in_subselect in Item_in_optimizer.
[16 Nov 2007 9:31] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36] Bugs System
Pushed into 6.0.4-alpha
[29 Nov 2007 0:25] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

In debug builds, testing the result of an IN subquery against NULL
caused an assertion failure.