Bug #74418 Item_in_subselect::val_int(): Assertion `0' failed. in sql/item_subselect.cc:136

Submitted: 16 Oct 2014 10:17 Modified: 20 Nov 2019 21:26
Reporter: Roel Van de Paar Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S6 (Debug Builds)
Version:5.6.20, 5.6.22, 5.6.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: debug

[16 Oct 2014 10:17] Roel Van de Paar
Description:
2014-09-15 16:43:24 7fe580ffb700  InnoDB: Assertion failure in thread 140623688480512 in file srv0srv.cc line 2664
InnoDB: Failing assertion: purge_sys->n_stop == 0

(gdb) bt
#0  0x00007f4831e59771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a9035e in my_write_core (sig=6) at /bzr/mysql-5.6/mysys/stacktrace.c:422
#2  0x0000000000723688 in handle_fatal_signal (sig=6) at /bzr/mysql-5.6/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f4830c655c9 in raise () from /lib64/libc.so.6
#5  0x00007f4830c66cd8 in abort () from /lib64/libc.so.6
#6  0x00007f4830c5e536 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f4830c5e5e2 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000092baa1 in Item_in_subselect::val_int (this=0x7f477c012508) at /bzr/mysql-5.6/sql/item_subselect.cc:1361
#9  0x00000000006a1f93 in eval_const_cond (cond=0x7f477c012508) at /bzr/mysql-5.6/sql/item_func.cc:76
#10 0x00000000009b4876 in internal_remove_eq_conds (thd=0x2f790c0, cond=0x7f477c012508, cond_value=0x7f477c010e60) at /bzr/mysql-5.6/sql/sql_optimizer.cc:8623
#11 0x00000000009b4c41 in remove_eq_conds (thd=0x2f790c0, cond=0x7f477c012508, cond_value=0x7f477c010e60) at /bzr/mysql-5.6/sql/sql_optimizer.cc:8716
#12 0x00000000009b411d in optimize_cond (thd=0x2f790c0, conds=0x7f477c012508, cond_equal=0x7f477c006c70, join_list=0x7f477c010f10, build_equalities=true, cond_value=0x7f477c010e60) at /bzr/mysql-5.6/sql/sql_optimizer.cc:8453
#13 0x000000000099eb4c in JOIN::optimize (this=0x7f477c006908) at /bzr/mysql-5.6/sql/sql_optimizer.cc:236
#14 0x00000000007fe0ee in mysql_execute_select (thd=0x2f790c0, select_lex=0x7f477c010d90, free_join=true) at /bzr/mysql-5.6/sql/sql_select.cc:1086
#15 0x00000000007fe43f in mysql_select (thd=0x2f790c0, tables=0x7f477c018f48, wild_num=0, fields=..., conds=0x7f477c012508, order=0x7f477c010f58, group=0x7f477c010e90, having=0x0, select_options=2953054976, result=0x7f477c0194a8, unit=0x7f477c010748, select_lex=0x7f477c010d90) at /bzr/mysql-5.6/sql/sql_select.cc:1221
#16 0x00000000007fc4c7 in handle_select (thd=0x2f790c0, result=0x7f477c0194a8, setup_tables_done_option=0) at /bzr/mysql-5.6/sql/sql_select.cc:110
#17 0x00000000007d681b in execute_sqlcom_select (thd=0x2f790c0, all_tables=0x7f477c018f48) at /bzr/mysql-5.6/sql/sql_parse.cc:5103
#18 0x00000000007cf5cb in mysql_execute_command (thd=0x2f790c0) at /bzr/mysql-5.6/sql/sql_parse.cc:2649
#19 0x00000000007f07ea in Prepared_statement::execute (this=0x7f477c012970, expanded_query=0x7f479bc53d30, open_cursor=false) at /bzr/mysql-5.6/sql/sql_prepare.cc:4015
#20 0x00000000007ef792 in Prepared_statement::execute_loop (this=0x7f477c012970, expanded_query=0x7f479bc53d30, open_cursor=false, packet=0x0, packet_end=0x0) at /bzr/mysql-5.6/sql/sql_prepare.cc:3663
#21 0x00000000007ed6f5 in mysql_sql_stmt_execute (thd=0x2f790c0) at /bzr/mysql-5.6/sql/sql_prepare.cc:2755
#22 0x00000000007cf5f9 in mysql_execute_command (thd=0x2f790c0) at /bzr/mysql-5.6/sql/sql_parse.cc:2659
#23 0x00000000007d8fa4 in mysql_parse (thd=0x2f790c0, rawbuf=0x7f477c004ff0 "EXECUTE stmt", length=12, parser_state=0x7f479bc54eb0) at /bzr/mysql-5.6/sql/sql_parse.cc:6245
#24 0x00000000007cc557 in dispatch_command (command=COM_QUERY, thd=0x2f790c0, packet=0x8c186e1 "EXECUTE stmt", packet_length=12) at /bzr/mysql-5.6/sql/sql_parse.cc:1332
#25 0x00000000007cb646 in do_command (thd=0x2f790c0) at /bzr/mysql-5.6/sql/sql_parse.cc:1034
#26 0x0000000000793a61 in do_handle_one_connection (thd_arg=0x2f790c0) at /bzr/mysql-5.6/sql/sql_connect.cc:982
#27 0x000000000079354a in handle_one_connection (arg=0x2f790c0) at /bzr/mysql-5.6/sql/sql_connect.cc:898
#28 0x0000000000ade888 in pfs_spawn_thread (arg=0x8ad2250) at /bzr/mysql-5.6/storage/perfschema/pfs.cc:1860
#29 0x00007f4831e54df3 in start_thread () from /lib64/libpthread.so.0
#30 0x00007f4830d2601d in clone () from /lib64/libc.so.6

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
create table t1(a char(1)collate koi8r_general_ci);
prepare stmt from "show procedure status where(1)in (select * from t1)";
SET SESSION optimizer_switch='semijoin=off';
EXECUTE stmt;

mysql> EXECUTE stmt;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[16 Oct 2014 11:05] MySQL Verification Team
Hello Roel,

Thank you for the report and test case.
Confirmed that only 5.6 debug builds affected.

Thanks,
Umesh
[16 Oct 2014 11:06] MySQL Verification Team
// optimized build

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.22                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.22-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

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 t1(a char(1)collate koi8r_general_ci);
Query OK, 0 rows affected (0.02 sec)

mysql> prepare stmt from "show procedure status where(1)in (select * from t1)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)
[16 Oct 2014 11:07] MySQL Verification Team
// Debug only build of 5.6.22

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------------+
| Variable_name           | Value                                                         |
+-------------------------+---------------------------------------------------------------+
| innodb_version          | 5.6.22                                                        |
| protocol_version        | 10                                                            |
| slave_type_conversions  |                                                               |
| version                 | 5.6.22-enterprise-commercial-advanced-debug                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition Debug (Commercial) |
| version_compile_machine | x86_64                                                        |
| version_compile_os      | Linux                                                         |
+-------------------------+---------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t1(a char(1)collate koi8r_general_ci);
Query OK, 0 rows affected (0.04 sec)

mysql> prepare stmt from "show procedure status where(1)in (select * from t1)";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
ERROR 2013 (HY000): Lost connection to MySQL server during query

(gdb) bt
#0  0x0000003bf260c8ac in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000afe982 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/mysys/stacktrace.c:422
#2  0x000000000077e6cc in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x0000003bf2232635 in raise () from /lib64/libc.so.6
#5  0x0000003bf2233e15 in abort () from /lib64/libc.so.6
#6  0x0000003bf222b75e in __assert_fail_base () from /lib64/libc.so.6
#7  0x0000003bf222b820 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000000991eb3 in Item_in_subselect::val_int (this=0x7f46c8068a98) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/item_subselect.cc:1361
#9  0x00000000006fba25 in eval_const_cond (cond=0x7f46c8068a98) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/item_func.cc:76
#10 0x0000000000a1e19d in internal_remove_eq_conds (thd=0x8187500, cond=0x7f46c8068a98, cond_value=0x7f46c80673f0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_optimizer.cc:8651
#11 0x0000000000a1e54c in remove_eq_conds (thd=0x8187500, cond=0x7f46c8068a98, cond_value=0x7f46c80673f0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_optimizer.cc:8744
#12 0x0000000000a1da1f in optimize_cond (thd=0x8187500, conds=0x7f46c8068a98, cond_equal=0x7f46c8006c50, join_list=0x7f46c80674a0, build_equalities=true, cond_value=0x7f46c80673f0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_optimizer.cc:8481
#13 0x0000000000a07837 in JOIN::optimize (this=0x7f46c80068e8) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_optimizer.cc:236
#14 0x000000000085cc9b in mysql_execute_select (thd=0x8187500, select_lex=0x7f46c8067320, free_join=true)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_select.cc:1086
#15 0x000000000085d003 in mysql_select (thd=0x8187500, tables=0x7f46c8069ec8, wild_num=0, fields=..., conds=0x7f46c8068a98, order=0x7f46c80674e8, group=0x7f46c8067420, having=0x0,
    select_options=2953054976, result=0x7f46c806a428, unit=0x7f46c8066cd8, select_lex=0x7f46c8067320)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_select.cc:1221
#16 0x000000000085b00f in handle_select (thd=0x8187500, result=0x7f46c806a428, setup_tables_done_option=0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_select.cc:110
#17 0x0000000000834dfb in execute_sqlcom_select (thd=0x8187500, all_tables=0x7f46c8069ec8) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:5103
#18 0x000000000082d553 in mysql_execute_command (thd=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:2649
#19 0x000000000084f146 in Prepared_statement::execute (this=0x7f46c80665c0, expanded_query=0x7f46f141c010, open_cursor=false)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_prepare.cc:4015
#20 0x000000000084e09b in Prepared_statement::execute_loop (this=0x7f46c80665c0, expanded_query=0x7f46f141c010, open_cursor=false, packet=0x0, packet_end=0x0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_prepare.cc:3663
#21 0x000000000084bfb8 in mysql_sql_stmt_execute (thd=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_prepare.cc:2755
#22 0x000000000082d584 in mysql_execute_command (thd=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:2659
#23 0x00000000008375d1 in mysql_parse (thd=0x8187500, rawbuf=0x7f46c8004fd0 "EXECUTE stmt", length=12, parser_state=0x7f46f141d680)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:6245
#24 0x000000000082a3a2 in dispatch_command (command=COM_QUERY, thd=0x8187500, packet=0x82b75f1 "EXECUTE stmt", packet_length=12)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1332
#25 0x0000000000829407 in do_command (thd=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_parse.cc:1034
#26 0x00000000007f0887 in do_handle_one_connection (thd_arg=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_connect.cc:982
#27 0x00000000007f0358 in handle_one_connection (arg=0x8187500) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/sql_connect.cc:898
#28 0x0000000000e5c19d in pfs_spawn_thread (arg=0x8230540) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/storage/perfschema/pfs.cc:1860
#29 0x0000003bf26079d1 in start_thread () from /lib64/libpthread.so.0
#30 0x0000003bf22e886d in clone () from /lib64/libc.so.6
[16 Oct 2014 11:08] MySQL Verification Team
// 5.7.6 optimized/debug 

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t1(a char(1)collate koi8r_general_ci);
Query OK, 0 rows affected (0.03 sec)

mysql> prepare stmt from "show procedure status where(1)in (select * from t1)";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)

//
[root@cluster-repo mysql-advanced-5.7.6]# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16-enterprise-commercial-advanced-debug MySQL Enterprise Server - Advanced Edition Debug (Commercial)

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> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 1 row affected (0.08 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t1(a char(1)collate koi8r_general_ci);
Query OK, 0 rows affected (0.04 sec)

mysql> prepare stmt from "show procedure status where(1)in (select * from t1)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.01 sec)

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table t1(a char(1)collate koi8r_general_ci);
Query OK, 0 rows affected (0.04 sec)

mysql> prepare stmt from "show procedure status where(1)in (select * from t1)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET SESSION optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt;
Empty set (0.00 sec)
[16 Oct 2014 11:59] MySQL Verification Team
Synopsis should be "Assertion failed: 0, file ..\..\mysql-5.6.21\sql\item_subselect.cc, line 1361" or assertion in "Item_in_subselect::val_int()"  as it doesn't look like innodb related.

replacing "InnoDB: Failing assertion: purge_sys->n_stop == 0 | srv0srv.cc line 2664" with  Assertion failed: 0, file ..\..\mysql-5.6.21\sql\item_subselect.cc, line 1361
[16 Oct 2014 19:46] Roel Van de Paar
Confirmed assert in error log is different. Not sure what happened there

2014-10-17 16:42:16 2399 [Note] /sda/mysql-5.6.20-linux-x86_64-debug/bin/mysqld: ready for connections.
Version: '5.6.20-debug'  socket: '/sda/mysql-5.6.20-linux-x86_64-debug/socket.sock'  port: 17011  MySQL Community Server (GPL)
mysqld: /bzr/mysql-5.6/sql/item_subselect.cc:1361: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.
05:42:19 UTC - mysqld got signal 6 ;

Made small update to title to make it easier to find later. Thanks
[6 Feb 2017 9:15] Roel Van de Paar
This assert is seen a fair bit in runs, may make sense to evaluate and perhaps fix it.
[20 Nov 2019 21:26] Roy Lyseng
Posted by developer:
 
Not reproducible in 5.7.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.