Bug #100288 Assertion ".remaining_tables_after .= 0"
Submitted: 22 Jul 2020 12:07 Modified: 13 Feb 2021 0:02
Reporter: Hrvoje Matijakovic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.21 OS:Linux
Assigned to: CPU Architecture:x86

[22 Jul 2020 12:07] Hrvoje Matijakovic
Description:

gdb from debug build:
Core was generated by `/sdc/MS140720-mysql-8.0.21-linux-x86_64-debug/bin/mysqld --no-defaults --basedi'.
Program terminated with signal 6, Aborted.
#0  0x00007f60071c2aa1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
59        val = INTERNAL_SYSCALL (tgkill, err, 3, THREAD_GETMEM (THREAD_SELF, pid),
(gdb) bt
+bt
#0  0x00007f60071c2aa1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
#1  0x0000000004c07dd1 in my_write_core (sig=6) at /home/hrvoje/sdc/MS-8.0.21_dbg/mysys/stacktrace.cc:308
#2  0x00000000038874d1 in handle_fatal_signal (sig=6) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/signal_handler.cc:171
#3  <signal handler called>
#4  0x00007f6004fbf387 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:55
#5  0x00007f6004fc0a78 in __GI_abort () at abort.c:90
#6  0x00007f6004fb81a6 in __assert_fail_base (fmt=0x7f6005113ce0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x5faff28 "(remaining_tables_after != 0) || ((cur_embedding_map == 0) && (join->positions[idx].dups_producing_tables == 0) && (join->deps_of_remaining_lateral_derived_tables == 0))", file=file@entry=0x5faf860 "/home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_planner.cc", line=line@entry=2896, function=function@entry=0x5fb0b20 <Optimize_table_order::best_extension_by_limited_search(unsigned long, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool Optimize_table_order::best_extension_by_limited_search(table_map, uint, uint)") at assert.c:92
#7  0x00007f6004fb8252 in __GI___assert_fail (assertion=0x5faff28 "(remaining_tables_after != 0) || ((cur_embedding_map == 0) && (join->positions[idx].dups_producing_tables == 0) && (join->deps_of_remaining_lateral_derived_tables == 0))", file=0x5faf860 "/home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_planner.cc", line=2896, function=0x5fb0b20 <Optimize_table_order::best_extension_by_limited_search(unsigned long, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool Optimize_table_order::best_extension_by_limited_search(table_map, uint, uint)") at assert.c:101
#8  0x00000000036cb661 in Optimize_table_order::best_extension_by_limited_search (this=0x7f6003916e20, remaining_tables=16, idx=4, current_search_depth=1) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_planner.cc:2893
#9  0x00000000036c9d98 in Optimize_table_order::greedy_search (this=0x7f6003916e20, remaining_tables=16) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_planner.cc:2320
#10 0x00000000036c957b in Optimize_table_order::choose_table_order (this=0x7f6003916e20) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_planner.cc:2004
#11 0x0000000003686889 in JOIN::make_join_plan (this=0x7f5fad168df8) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_optimizer.cc:4999
#12 0x000000000367a18e in JOIN::optimize (this=0x7f5fad168df8) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_optimizer.cc:505
#13 0x0000000003728b4f in SELECT_LEX::optimize (this=0x7f5fad062768, thd=0x7f5fad020000) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_select.cc:1811
#14 0x00000000037c5ad2 in SELECT_LEX_UNIT::optimize (this=0x7f5fad0620a8, thd=0x7f5fad020000, materialize_destination=0x0) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_union.cc:656
#15 0x0000000003726e62 in Sql_cmd_dml::execute_inner (this=0x7f5fad063d18, thd=0x7f5fad020000) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_select.cc:930
#16 0x0000000003726413 in Sql_cmd_dml::execute (this=0x7f5fad063d18, thd=0x7f5fad020000) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_select.cc:722
#17 0x00000000036ac93e in mysql_execute_command (thd=0x7f5fad020000, first_level=true) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_parse.cc:4573
#18 0x00000000036af397 in mysql_parse (thd=0x7f5fad020000, parser_state=0x7f6003918b20) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_parse.cc:5393
#19 0x00000000036a43d8 in dispatch_command (thd=0x7f5fad020000, com_data=0x7f6003919bd0, command=COM_QUERY) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_parse.cc:1810
#20 0x00000000036a28ed in do_command (thd=0x7f5fad020000) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/sql_parse.cc:1294
#21 0x0000000003871d6b in handle_connection (arg=0x7f5ffabf6ec0) at /home/hrvoje/sdc/MS-8.0.21_dbg/sql/conn_handler/connection_handler_per_thread.cc:302
#22 0x0000000005443ae5 in pfs_spawn_thread (arg=0x7f600306cde0) at /home/hrvoje/sdc/MS-8.0.21_dbg/storage/perfschema/pfs.cc:2880
#23 0x00007f60071bdea5 in start_thread (arg=0x7f600391a700) at pthread_create.c:307
#24 0x00007f60050878dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

I'm able to reproduce this on the debug build of 8.0.21. I can't reproduce this on a release build.

How to repeat:
SET @@session.optimizer_search_depth=1;
SELECT table_name FROM information_schema.key_column_usage;
[22 Jul 2020 12:18] MySQL Verification Team
Hello Hrvoje Matijakovic,

Thank you for the bug report and test case.
Observed that 8.0.21 debug build is affected.

regards,
Umesh
[13 Feb 2021 0:02] Jon Stephens
Documented fix as follows in the MySQL 8.0.24 changelog:

    The debug server hit an assert when optimizer_search_depth was
    less than the number of JOIN_TAB structures for a JOIN.

Closed.