Bug #98258 Assertion in sql_tmp_table.cc::trace_tmp_table
Submitted: 16 Jan 20:43 Modified: 29 Jan 2:57
Reporter: Kamil Holubicki (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S6 (Debug Builds)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[16 Jan 20:43] Kamil Holubicki
Description:
Debug build crashes on assert "m_idx >= 0" in sql_tmp_table.cc, trace_tmp_table()

How to repeat:
CREATE TABLE t1(name VARCHAR(64), type TEXT) ENGINE=innodb;
INSERT INTO t1(name, type) VALUES ("t1", "table");
INSERT INTO t1(name, type) VALUES ("t2", "table");
INSERT INTO t1(name, type) VALUES ("t3", "table");
INSERT INTO t1(name, type) VALUES ("t4", "table");
INSERT INTO t1(name, type) VALUES ("t5", "table");
INSERT INTO t1(name, type) VALUES ("t6", "table");
INSERT INTO t1(name, type) VALUES ("t7", "table");
INSERT INTO t1(name, type) VALUES ("t8", "table");
INSERT INTO t1(name, type) VALUES ("t9", "table");
INSERT INTO t1(name, type) VALUES ("t10", "table");
INSERT INTO t1(name, type) VALUES ("t11", "table");

CREATE TABLE t2(name VARCHAR(64), data TEXT) ENGINE=innodb;
INSERT INTO t2(name, data) VALUES("t1", "data");
INSERT INTO t2(name, data) VALUES("t2", "data");
INSERT INTO t2(name, data) VALUES("t3", "data");
INSERT INTO t2(name, data) VALUES("t4", "data");
INSERT INTO t2(name, data) VALUES("t5", "data");
INSERT INTO t2(name, data) VALUES("t6", "data");
INSERT INTO t2(name, data) VALUES("t7", "data");
INSERT INTO t2(name, data) VALUES("t8", "data");

SET SESSION internal_tmp_mem_storage_engine=MEMORY;
SET @@session.optimizer_trace="enabled=on";
SET @@session.tmp_table_size = 1024;
SET @@optimizer_switch="semijoin=off";

SELECT name, data FROM t2 WHERE name IN ( SELECT name FROM t1 WHERE type='table');

Suggested fix:
When tmp table is created in item_subselect.cc, subselect_hash_sj_engine::setup() after assigning QEP_TAB to table, initialize idx to non-negative value like: 
tab->set_idx(0);
[16 Jan 21:33] MySQL Verification Team
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-debug Source distribution BUILT: 2019/12/22

Copyright (c) 2000, 2020, 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 8.0 > CREATE DATABASE q;
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > USE q
Database changed
mysql 8.0 > CREATE TABLE t1(name VARCHAR(64), type TEXT) ENGINE=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t1", "table");
Query OK, 1 row affected (0.03 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t2", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t3", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t4", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t5", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t6", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t7", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t8", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t9", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t10", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t1(name, type) VALUES ("t11", "table");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 >
mysql 8.0 > CREATE TABLE t2(name VARCHAR(64), data TEXT) ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t1", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t2", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t3", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t4", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t5", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t6", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t7", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > INSERT INTO t2(name, data) VALUES("t8", "data");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 >
mysql 8.0 > SET SESSION internal_tmp_mem_storage_engine=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@session.optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@session.tmp_table_size = 1024;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 >
mysql 8.0 > SELECT name, data FROM t2 WHERE name IN ( SELECT name FROM t1 WHERE type='table');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 8.0 >

2020-01-16T21:30:54.330304Z 0 [System] [MY-010931] [Server] d:\dbs\8.0\bin\mysqld: ready for connections. Version: '8.0.20-debug'  socket: ''  port: 3580  Source distribution BUILT: 2019/12/22.
2020-01-16T21:30:54.339441Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
Assertion failed: m_idx >= 0, file D:\build\2019DEZ22\mysql-8.0\sql\sql_opt_exec_shared.h, line 255
abort() has been called21:31:59 UTC - mysqld got exception 0x80000003 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x2a8d38dd050
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff7bbec97c5    mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:372]
7ffe5229c3e1    ucrtbased.dll!raise()
7ffe5229e039    ucrtbased.dll!abort()
7ffe522a3c65    ucrtbased.dll!_get_wide_winmain_command_line()
7ffe522a37d7    ucrtbased.dll!_get_wide_winmain_command_line()
7ffe522a1868    ucrtbased.dll!_get_wide_winmain_command_line()
7ffe522a41cf    ucrtbased.dll!_wassert()
7ff7b9c3d5f2    mysqld.exe!QEP_shared_owner::idx()[sql_opt_exec_shared.h:482]
7ff7ba4c758c    mysqld.exe!trace_tmp_table()[sql_tmp_table.cc:2214]
7ff7ba4c26aa    mysqld.exe!create_ondisk_from_heap()[sql_tmp_table.cc:2549]
7ff7ba3a8c1a    mysqld.exe!Query_result_union::send_data()[sql_union.cc:119]
7ff7ba3ad1dd    mysqld.exe!SELECT_LEX_UNIT::ExecuteIteratorQuery()[sql_union.cc:1530]
7ff7ba1f1ce3    mysqld.exe!subselect_hash_sj_engine::exec()[item_subselect.cc:3526]
7ff7ba1e2a39    mysqld.exe!Item_subselect::exec()[item_subselect.cc:680]
7ff7ba1ec9be    mysqld.exe!Item_in_subselect::exec()[item_subselect.cc:752]
7ff7ba1ee535    mysqld.exe!Item_in_subselect::val_bool_naked()[item_subselect.cc:1591]
7ff7b9c0d334    mysqld.exe!Item_in_optimizer::val_int()[item_cmpfunc.cc:2277]
7ff7ba722114    mysqld.exe!FilterIterator::Read()[composite_iterators.cc:81]
7ff7ba3aced9    mysqld.exe!SELECT_LEX_UNIT::ExecuteIteratorQuery()[sql_union.cc:1516]
7ff7ba3ad7ad    mysqld.exe!SELECT_LEX_UNIT::execute()[sql_union.cc:1588]
7ff7ba3e4523    mysqld.exe!Sql_cmd_dml::execute_inner()[sql_select.cc:910]
7ff7ba3e3de4    mysqld.exe!Sql_cmd_dml::execute()[sql_select.cc:715]
7ff7ba02250a    mysqld.exe!mysql_execute_command()[sql_parse.cc:4484]
7ff7ba01b7d2    mysqld.exe!mysql_parse()[sql_parse.cc:5300]
7ff7ba02604c    mysqld.exe!dispatch_command()[sql_parse.cc:1781]
7ff7ba023f04    mysqld.exe!do_command()[sql_parse.cc:1275]
7ff7b9c46b98    mysqld.exe!handle_connection()[connection_handler_per_thread.cc:302]
7ff7bc8cd695    mysqld.exe!pfs_spawn_thread()[pfs.cc:2856]
7ff7bbec8327    mysqld.exe!win_thread_start()[my_thread.cc:52]
7ffe522a542c    ucrtbased.dll!_register_onexit_function()
7ffec2197bd4    KERNEL32.DLL!BaseThreadInitThunk()
7ffec22eced1    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2a8d3d1c138): SELECT name, data FROM t2 WHERE name IN ( SELECT name FROM t1 WHERE type='table')
Connection ID (thread ID): 8
Status: NOT_KILLED
[16 Jan 22:36] MySQL Verification Team
Thank you for the bug report.

Your MySQL connection id is 9
Server version: 8.0.20-debug Source distribution BUILT: 2020-JAN-13

Copyright (c) 2000, 2020, 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 8.0 > USE y
Database changed
mysql 8.0 > CREATE TABLE t1(name VARCHAR(64), type TEXT) ENGINE=innodb;
Query OK, 0 rows affected (0.55 sec)

<CUT>

INSERT INTO t1(name, type) VALUES ("t11", "table");Query OK, 1 row affected (0.19 sec)
<CUT>

mysql 8.0 > CREATE TABLE t2(name VARCHAR(64), data TEXT) ENGINE=innodb;

<CUT>
mysql 8.0 > SET SESSION internal_tmp_mem_storage_engine=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@session.optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@session.tmp_table_size = 1024;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SET @@optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SELECT name, data FROM t2 WHERE name IN ( SELECT name FROM t1 WHERE type='table');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 8.0 >
[17 Jan 10:32] Kamil Holubicki
proposed fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug98258.patch (text/x-patch), 4.42 KiB.

[29 Jan 2:57] Paul Dubois
Posted by developer:
 
Fixed in 8.0.21.

For debug builds, the server could exit trying to print an optimizer
trace.
[29 Jan 15:16] Tor Didriksen
Posted by developer:
 
We decided not to use the contributed patch, but rather fix trace_tmp_table():
+  if (tab != nullptr && tab->join() != nullptr)
+    trace_tmp.add("in_plan_at_position", tab->idx());

The QEP_tab which had no index belonged to a QEP_TAB_standalone
which is documented as "not connected to any JOIN_TAB"
[29 Jan 15:26] Paul Dubois
Posted by developer:
 
Fixed in 8.0.20.