Bug #74877 Assertion `false' failed in JOIN::decide_subquery_strategy
Submitted: 15 Nov 2014 17:00 Modified: 5 Dec 2016 5:50
Reporter: Ramesh Sivaraman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S6 (Debug Builds)
Version:5.6.21 OS:Linux (CentOS 7)
Assigned to: CPU Architecture:Any
Tags: debug

[15 Nov 2014 17:00] Ramesh Sivaraman
Description:
mysqld: /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:9558: bool JOIN::decide_subquery_strategy(): Assertion `false' failed.
16:50:15 UTC - mysqld got signal 6 ;

When we execute the testcase with optimized build "EXPLAIN INSERT INTO v1 VALUES (10);" statement is not responding. In processlist the transaction is in sleep mode.

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t2 (id int primary key,val varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t1 (a SET('x') NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> create view v1 as select * from t1 where a in (select * from t2);
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN INSERT INTO v1 VALUES (10);

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | test | Sleep   |  361 |       | NULL             |
|  3 | root | localhost | test | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql>

#0  0x00007f0c01a6f771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a954ea in my_write_core (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/mysys/stacktrace.c:422
#2  0x0000000000726494 in handle_fatal_signal (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f0c0087b5c9 in raise () from /lib64/libc.so.6
#5  0x00007f0c0087ccd8 in abort () from /lib64/libc.so.6
#6  0x00007f0c00874536 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f0c008745e2 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000009b9ea5 in JOIN::decide_subquery_strategy (this=0x7f0b64d50668) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:9558
#9  0x00000000009ab5c3 in make_join_statistics (join=0x7f0b64d50668, tables_arg=0x7f0b64c7e158, conds=0x0, keyuse_array=0x7f0b64d50890, first_optimization=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:3772
#10 0x00000000009a25fb in JOIN::optimize (this=0x7f0b64d50668) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:363
#11 0x0000000000800f52 in mysql_execute_select (thd=0x7f0b6e75d000, select_lex=0x7f0b64c7d570, free_join=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1086
#12 0x00000000008012a3 in mysql_select (thd=0x7f0b6e75d000, tables=0x7f0b64c7e158, wild_num=0, fields=..., conds=0x0, order=0x7f0b64c7d738, group=0x7f0b64c7d670, having=0x0, select_options=2147748612, result=0x7f0b64c7eb78, unit=0x7f0b64c7d870, select_lex=0x7f0b64c7d570) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1221
#13 0x000000000093e71d in mysql_explain_unit (thd=0x7f0b6e75d000, unit=0x7f0b64c7d870, result=0x7f0b64c7eb78) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:2132
#14 0x0000000000939634 in Explain::explain_subqueries (this=0x7f0c02023970, result=0x7f0b64c7eb78) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:602
#15 0x0000000000939d0d in Explain::send (this=0x7f0c02023970) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:713
#16 0x000000000093de03 in explain_no_table (thd=0x7f0b6e75d000, message=0xef8f6a "No tables used", rows=18446744073709551615) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:1886
#17 0x00000000007b1a4e in mysql_insert (thd=0x7f0b6e75d000, table_list=0x7f0b64c1f280, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_insert.cc:881
#18 0x00000000007d43b7 in mysql_execute_command (thd=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:3443
#19 0x00000000007dbdbc in mysql_parse (thd=0x7f0b6e75d000, rawbuf=0x7f0b64c1f010 "EXPLAIN INSERT INTO v1 VALUES (10)", length=34, parser_state=0x7f0c02024e70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245
#20 0x00000000007cf36f in dispatch_command (command=COM_QUERY, thd=0x7f0b6e75d000, packet=0x7f0b63266001 "", packet_length=34) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332
#21 0x00000000007ce45e in do_command (thd=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034
#22 0x0000000000796879 in do_handle_one_connection (thd_arg=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982
#23 0x0000000000796362 in handle_one_connection (arg=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898
#24 0x0000000000d71288 in pfs_spawn_thread (arg=0x7f0bfdffea60) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860
#25 0x00007f0c01a6adf3 in start_thread () from /lib64/libpthread.so.0
#26 0x00007f0c0093c01d in clone () from /lib64/libc.so.6

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
create table t2 (id int primary key,val varchar(20));
CREATE TABLE t1 (a SET('x') NOT NULL);
create view v1 as select * from t1 where a in (select * from t2);
EXPLAIN INSERT INTO v1 VALUES (10);
[16 Nov 2014 1:09] MySQL Verification Team
On Windows optimized version statement stays sleeping:

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23 Source distribution revno 6246

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:49318 | test | Sleep   |   84 |       | NULL             |
|  2 | root | localhost:49347 | NULL | Query   |    0 | init  | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
[16 Nov 2014 2:25] MySQL Verification Team
Thank you for the bug report.

[miguel@centosvb bin]$ mysql -uroot --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-debug Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t2 (id int primary key,val varchar(20));
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1 (a SET('x') NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> create view v1 as select * from t1 where a in (select * from t2);
Query OK, 0 rows affected (0.01 sec)

mysql> EXPLAIN INSERT INTO v1 VALUES (10);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[5 Dec 2016 5:32] Roel Van de Paar
If the statement stays sleeping on optimized, then this is a serious bug.
[5 Dec 2016 5:50] Ramesh Sivaraman
As explain statement goes into sleep mode on optimized build marking it as s1.