MySQL Bugs: #36137: virtual longlong Item_in_subselect::val

Bug #36137	virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.
Submitted:	16 Apr 2008 12:48	Modified:	20 Nov 2010 23:33
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0.4-debug-nt, 6.0.5-bk	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
another 6.0-debug specific assertion.

assertion from linux 6.0.5-bk:

Version: '6.0.5-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
mysqld: item_subselect.cc:855: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.

stack trace from windows 6.0.4:

mysqld-debug.exe!_NMSG_WRITE
mysqld-debug.exe!abort
mysqld-debug.exe!_assert
mysqld-debug.exe!Item_in_subselect::val_int
mysqld-debug.exe!eval_const_cond
mysqld-debug.exe!remove_eq_conds
mysqld-debug.exe!optimize_cond
mysqld-debug.exe!JOIN::optimize
mysqld-debug.exe!subselect_hash_sj_engine::exec
mysqld-debug.exe!Item_subselect::exec
mysqld-debug.exe!Item_in_subselect::exec
mysqld-debug.exe!Item_in_subselect::val_bool
mysqld-debug.exe!Item::val_bool_result
mysqld-debug.exe!Item_in_optimizer::val_int
mysqld-debug.exe!Item::val_bool
mysqld-debug.exe!Item_cond_and::val_int
mysqld-debug.exe!evaluate_join_record
mysqld-debug.exe!sub_select
mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!execute_sqlcom_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_threadstart

workaround is to disable new optimizer functions by running this first:
set @@optimizer_switch='no_semijoin,no_materialization';

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` int,`b` int,key (`a`),key (`b`)) engine=myisam;
insert into `t1` values (2,4),(2,4),(2,4);
select t1.a from t1 where t1.a not in (select 1 from t1) and
t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by  t1.a );

Thank you for the bug report. Verified as described on Suse 10.3 X64:

miguel@hegel:~/dbs> 6.0/bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1` (`a` int,`b` int,key (`a`),key (`b`)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into `t1` values (2,4),(2,4),(2,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t1.a from t1 where t1.a not in (select 1 from t1) and
    -> t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by  t1.a );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

The crash happens only when both semi-join and materialization are enabled. If I set @@optimizer switch to disable semi-join, or materialization, or both, the crash goes away.

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/45856

ChangeSet@1.2625, 2008-04-23 06:56:36+04:00, sergefp@mysql.com +3 -0
  BUG#36137: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.
  - Call join->flatten_subqueries() before we call join->optimize(). 
    If we don't do this, subquery predicates which were slated for conversion
    it semi-join nests will remain in the WHERE clause and will fire an assert
    when we attempt to evaluate them (this happens because these predicates
    have not undergone the mandatory IN->EXISTS rewrite)

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/45861

ChangeSet@1.2623, 2008-04-23 09:26:34+04:00, sergefp@mysql.com +3 -0
  BUG#36137: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.
  - Call join->flatten_subqueries() before we call join->optimize()
    If we don't do this, subquery predicates which were slated for conversion
    into semi-join nests will remain half-converted and will fire an assert
    when we attempt to evaluate them.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

The combination of semi-join and materialization both being enabled
could lead to assertion failure during subquery processing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.