Bug #74889 Sig-6 abort in Item_in_subselect::exec | sql/item_subselect.cc:730
Submitted: 17 Nov 2014 5:20 Modified: 25 Nov 2019 21:39
Reporter: Ramesh Sivaraman Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.21 OS:Linux (CentOS 7)
Assigned to: CPU Architecture:Any
Tags: debug

[17 Nov 2014 5:20] Ramesh Sivaraman
Description:
mysqld: /ssd/ramesh/mysql-server/mysql-5.6/sql/item_subselect.cc:732: virtual bool Item_in_subselect::exec(): Assertion `exec_method != EXEC_MATERIALIZATION || (exec_method == EXEC_MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)' failed.
05:04:09 UTC - mysqld got signal 6 ;

*** GDB info

#0  0x00007f164cac9771 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  0x00007f164b8d55c9 in raise () from /lib64/libc.so.6
#5  0x00007f164b8d6cd8 in abort () from /lib64/libc.so.6
#6  0x00007f164b8ce536 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f164b8ce5e2 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000092cf12 in Item_in_subselect::exec (this=0x7f15afc20a50) at /ssd/ramesh/mysql-server/mysql-5.6/sql/item_subselect.cc:730
#9  0x000000000092ed6e in Item_in_subselect::val_bool (this=0x7f15afc20a50) at /ssd/ramesh/mysql-server/mysql-5.6/sql/item_subselect.cc:1400
#10 0x000000000064751d in Item::val_bool_result (this=0x7f15afc20a50) at /ssd/ramesh/mysql-server/mysql-5.6/sql/item.h:1191
#11 0x00000000006774ee in Item_in_optimizer::val_int (this=0x7f15afc7dbc0) at /ssd/ramesh/mysql-server/mysql-5.6/sql/item_cmpfunc.cc:2099
#12 0x00000000007a3b4a in evaluate_join_record (join=0x7f15afc7d010, join_tab=0x7f15afc7e9d0) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_executor.cc:1461
#13 0x00000000007a3550 in sub_select (join=0x7f15afc7d010, join_tab=0x7f15afc7e9d0, end_of_records=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_executor.cc:1276
#14 0x00000000007a2e1d in do_select (join=0x7f15afc7d010) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_executor.cc:933
#15 0x00000000007a0d79 in JOIN::exec (this=0x7f15afc7d010) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_executor.cc:194
#16 0x0000000000800fa7 in mysql_execute_select (thd=0x7f15b975d000, select_lex=0x7f15b975f878, free_join=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1100
#17 0x00000000008012a3 in mysql_select (thd=0x7f15b975d000, tables=0x7f15afc1f240, wild_num=1, fields=..., conds=0x7f15afc20a50, order=0x7f15b975fa40, group=0x7f15b975f978, having=0x0, select_options=2147748608, result=0x7f15afc20c98, unit=0x7f15b975f230, select_lex=0x7f15b975f878) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1221
#18 0x00000000007ff32b in handle_select (thd=0x7f15b975d000, result=0x7f15afc20c98, setup_tables_done_option=0) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:110
#19 0x00000000007d9633 in execute_sqlcom_select (thd=0x7f15b975d000, all_tables=0x7f15afc1f240) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:5103
#20 0x00000000007d23e3 in mysql_execute_command (thd=0x7f15b975d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:2649
#21 0x00000000007dbdbc in mysql_parse (thd=0x7f15b975d000, rawbuf=0x7f15afc1f010 "SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1)", length=49, parser_state=0x7f164d07ee70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245
#22 0x00000000007cf36f in dispatch_command (command=COM_QUERY, thd=0x7f15b975d000, packet=0x7f15ae266001 "", packet_length=49) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332
#23 0x00000000007ce45e in do_command (thd=0x7f15b975d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034
#24 0x0000000000796879 in do_handle_one_connection (thd_arg=0x7f15b975d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982
#25 0x0000000000796362 in handle_one_connection (arg=0x7f15b975d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898
#26 0x0000000000d71288 in pfs_spawn_thread (arg=0x7f1648ffea60) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860
#27 0x00007f164cac4df3 in start_thread () from /lib64/libpthread.so.0
#28 0x00007f164b99601d in clone () from /lib64/libc.so.6

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
SET SESSION big_tables=1;
CREATE TABLE t1(a char(255)DEFAULT'',KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO t1 VALUES(0),(0),(0);
SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);
[17 Nov 2014 6:50] MySQL Verification Team
Hello Ramesh Sivaraman,

Thank you for the report and test case.
Confirmed that only 5.6.21 debug build is affected and latest(5.6.23/5.7.6) builds are not affected.

Thanks,
Umesh
[17 Nov 2014 6:50] MySQL Verification Team
// 5.6.21 only debug build affected

(gdb) bt
#0  __pthread_kill (threadid=<value optimized out>, signo=<value optimized out>) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:63
#1  0x0000000000aaa203 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/mysys/stacktrace.c:422
#2  0x00000000007140ea in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x0000003deac32625 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#5  0x0000003deac33e05 in abort () at abort.c:92
#6  0x0000003deac2b74e in __assert_fail_base (fmt=<value optimized out>,
    assertion=0xf973f0 "exec_method != EXEC_MATERIALIZATION || (exec_method == EXEC_MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)",
    file=0xf96718 "/export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item_subselect.cc", line=<value optimized out>, function=<value optimized out>) at assert.c:96
#7  0x0000003deac2b810 in __assert_fail (
    assertion=0xf973f0 "exec_method != EXEC_MATERIALIZATION || (exec_method == EXEC_MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)",
    file=0xf96718 "/export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item_subselect.cc", line=732, function=0xf987e0 "virtual bool Item_in_subselect::exec()") at assert.c:105
#8  0x0000000000933a8a in Item_in_subselect::exec (this=0x7f1678006a10) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item_subselect.cc:730
#9  0x000000000092ba3b in Item_in_subselect::val_bool (this=0x7f1678006a10) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item_subselect.cc:1400
#10 0x000000000062615e in Item::val_bool_result (this=0x7f1678006a10) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item.h:1191
#11 0x000000000065aea1 in Item_in_optimizer::val_int (this=0x7f167801fe20) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/item_cmpfunc.cc:2099
#12 0x0000000000796c78 in evaluate_join_record (join=0x7f167801f270, join_tab=0x7f1678020c30) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_executor.cc:1461
#13 0x000000000079b372 in sub_select (join=0x7f167801f270, join_tab=0x7f1678020c30, end_of_records=false)
    at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_executor.cc:1276
#14 0x000000000079cc1d in do_select (join=0x7f167801f270) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_executor.cc:933
#15 0x000000000079d529 in JOIN::exec (this=0x7f167801f270) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_executor.cc:194
#16 0x00000000007ff62c in mysql_execute_select (thd=0x36d20e0, select_lex=0x36d4958, free_join=true) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_select.cc:1100
#17 0x00000000007fffeb in mysql_select (thd=0x36d20e0, tables=0x7f1678005200, wild_num=1, fields=..., conds=0x7f1678006a10, order=0x36d4b20, group=0x36d4a58, having=0x0,
    select_options=2147748608, result=0x7f1678006c58, unit=0x36d4310, select_lex=0x36d4958) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_select.cc:1221
#18 0x00000000008001e7 in handle_select (thd=0x36d20e0, result=0x7f1678006c58, setup_tables_done_option=0)
    at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_select.cc:110
#19 0x00000000007c63bc in execute_sqlcom_select (thd=0x36d20e0, all_tables=0x7f1678005200) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_parse.cc:5103
#20 0x00000000007c78e7 in mysql_execute_command (thd=0x36d20e0) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_parse.cc:2649
#21 0x00000000007cee85 in mysql_parse (thd=0x36d20e0, rawbuf=0x7f1678004fd0 "SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1)", length=49, parser_state=0x7f169a7fe680)
    at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_parse.cc:6245
#22 0x00000000007d0a2f in dispatch_command (command=COM_QUERY, thd=0x36d20e0, packet=0x3eaedc1 "", packet_length=49)
    at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_parse.cc:1332
#23 0x00000000007d2269 in do_command (thd=0x36d20e0) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_parse.cc:1034
#24 0x0000000000788571 in do_handle_one_connection (thd_arg=0x36d20e0) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_connect.cc:982
#25 0x0000000000788637 in handle_one_connection (arg=0x36d20e0) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/sql/sql_connect.cc:898
#26 0x0000000000d9bbe2 in pfs_spawn_thread (arg=0x3e90940) at /export/home/pb2/build/sb_0-13191046-1410443906.78/mysql-5.6.21/storage/perfschema/pfs.cc:1860
#27 0x0000003deb0079d1 in start_thread (arg=0x7f169a7ff700) at pthread_create.c:301
#28 0x0000003deace89dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
[17 Nov 2014 6:51] MySQL Verification Team
// 5.6.23/5.7.6 not affected.. most likely this issue fixed?

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------------+
| Variable_name           | Value                                                         |
+-------------------------+---------------------------------------------------------------+
| innodb_version          | 5.6.23                                                        |
| protocol_version        | 10                                                            |
| slave_type_conversions  |                                                               |
| version                 | 5.6.23-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.00 sec)

mysql> SET SESSION big_tables=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(a char(255)DEFAULT'',KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);
+------+
| a    |
+------+
| 0    |
| 0    |
| 0    |
+------+
3 rows in set (0.01 sec)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-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, 1 row affected (0.00 sec)

mysql> SET SESSION big_tables=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(a char(255)DEFAULT'',KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO t1 VALUES(0),(0),(0);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);
+------+
| a    |
+------+
| 0    |
| 0    |
| 0    |
+------+
3 rows in set (0.00 sec)

// 5.7.6

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-debug                |
| version_comment         | MySQL Enterprise Server - Advanced Edition Debug (Commercial) |
| version_compile_machine | x86_64                                                        |
| version_compile_os      | linux-glibc2.5                                                |
+-------------------------+---------------------------------------------------------------+
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> SET SESSION big_tables=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(a char(255)DEFAULT'',KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO t1 VALUES(0),(0),(0);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(0),(0),(0);
SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);
+------+
| a    |
+------+
| 0    |
| 0    |
| 0    |
+------+
3 rows in set (0.00 sec)
[25 Nov 2019 21:39] Roy Lyseng
Posted by developer:
 
According to verifier, not reproducible on most recent 5.6 versions.