Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21883

Server crashes when joining a subselect with 32 tables and GROUP BY

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.15, 10.4.12, 10.3(EOL), 10.4(EOL), 10.5
    • 10.3.23, 10.4.13, 10.5.3
    • N/A
    • None
    • $ uname -a
      Linux lchabowski 4.15.0-88-generic #88-Ubuntu SMP Tue Feb 11 20:11:34 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

    Description

      The following SQL queries crashes the mysqld binary:

      CREATE DATABASE crashtest;
       
      USE crashtest;
       
      CREATE TABLE dummy (id INT NOT NULL PRIMARY KEY);
       
      SELECT 1
      FROM dummy t
      LEFT JOIN (
          SELECT dummy0.id
          FROM dummy AS dummy0
          LEFT JOIN dummy AS dummy1 ON 0
          LEFT JOIN dummy AS dummy2 ON 0
          LEFT JOIN dummy AS dummy3 ON 0
          LEFT JOIN dummy AS dummy4 ON 0
          LEFT JOIN dummy AS dummy5 ON 0
          LEFT JOIN dummy AS dummy6 ON 0
          LEFT JOIN dummy AS dummy7 ON 0
          LEFT JOIN dummy AS dummy8 ON 0
          LEFT JOIN dummy AS dummy9 ON 0
          LEFT JOIN dummy AS dummy10 ON 0
          LEFT JOIN dummy AS dummy11 ON 0
          LEFT JOIN dummy AS dummy12 ON 0
          LEFT JOIN dummy AS dummy13 ON 0
          LEFT JOIN dummy AS dummy14 ON 0
          LEFT JOIN dummy AS dummy15 ON 0
          LEFT JOIN dummy AS dummy16 ON 0
          LEFT JOIN dummy AS dummy17 ON 0
          LEFT JOIN dummy AS dummy18 ON 0
          LEFT JOIN dummy AS dummy19 ON 0
          LEFT JOIN dummy AS dummy20 ON 0
          LEFT JOIN dummy AS dummy21 ON 0
          LEFT JOIN dummy AS dummy22 ON 0
          LEFT JOIN dummy AS dummy23 ON 0
          LEFT JOIN dummy AS dummy24 ON 0
          LEFT JOIN dummy AS dummy25 ON 0
          LEFT JOIN dummy AS dummy26 ON 0
          LEFT JOIN dummy AS dummy27 ON 0
          LEFT JOIN dummy AS dummy28 ON 0
          LEFT JOIN dummy AS dummy29 ON 0
          LEFT JOIN dummy AS dummy30 ON 0
          LEFT JOIN dummy AS dummy31 ON 0
          GROUP BY dummy0.id
      ) AS subquery
          ON subquery.id = t.id;
      

      Removing the GROUP BY or a LEFT JOIN and the query executes sucessfully.

      Tested on 10.3.15, 10.4.12 and on the HEAD of 10.5 (was 23685378bafa38a74957e8f07e2d56b57e90fa53 at the time).

      Here is the output of the mysqld process:

      $ ./sql/mysqld 
      2020-03-06 12:52:10 0 [Note] ./sql/mysqld (mysqld 10.5.2-MariaDB-debug) starting as process 11578 ...
      2020-03-06 12:52:10 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
      2020-03-06 12:52:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2020-03-06 12:52:10 0 [Note] InnoDB: Uses event mutexes
      2020-03-06 12:52:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2020-03-06 12:52:10 0 [Note] InnoDB: Number of pools: 1
      2020-03-06 12:52:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
      2020-03-06 12:52:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
      2020-03-06 12:52:10 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
      2020-03-06 12:52:10 0 [Note] InnoDB: Completed initialization of buffer pool
      2020-03-06 12:52:11 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
      2020-03-06 12:52:11 0 [Note] InnoDB: 128 rollback segments are active.
      2020-03-06 12:52:11 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2020-03-06 12:52:11 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2020-03-06 12:52:11 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2020-03-06 12:52:11 0 [Note] InnoDB: 10.5.2 started; log sequence number 45617; transaction id 21
      2020-03-06 12:52:11 0 [Note] InnoDB: Loading buffer pool(s) from /home/lchabowski/mariadb-dev/dbdata/ib_buffer_pool
      2020-03-06 12:52:11 0 [Note] Plugin 'FEEDBACK' is disabled.
      2020-03-06 12:52:11 0 [Note] InnoDB: Buffer pool(s) load completed at 200306 12:52:11
      2020-03-06 12:52:11 0 [Note] Server socket created on IP: '::'.
      2020-03-06 12:52:11 0 [Note] Reading of all Master_info entries succeeded
      2020-03-06 12:52:11 0 [Note] Added new Master_info '' to hash table
      2020-03-06 12:52:11 0 [Note] ./sql/mysqld: ready for connections.
      Version: '10.5.2-MariaDB-debug'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
      mysqld: /home/lchabowski/src/mariadb-server/sql/sql_select.cc:8679: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570814527423731704357e+308L)' failed.
      200306 12:52:31 [ERROR] mysqld got signal 6 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
       
      To report this bug, see https://mariadb.com/kb/en/reporting-bugs
       
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed, 
      something is definitely wrong and this may fail.
       
      Server version: 10.5.2-MariaDB-debug
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=1
      max_threads=153
      thread_count=2
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467885 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7fb30c000d60
      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...
      stack_bottom = 0x7fb35c117e50 thread_stack 0x49000
      addr2line: './sql/mysqld': No such file
      ./sql/mysqld(my_print_stacktrace+0x4f)[0x555e0fb0393e]
      Printing to addr2line failed
      ./sql/mysqld(handle_fatal_signal+0x3bd)[0x555e0f1b453c]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb365d25890]
      /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7fb364e1de97]
      /lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7fb364e1f801]
      /lib/x86_64-linux-gnu/libc.so.6(+0x3039a)[0x7fb364e0f39a]
      /lib/x86_64-linux-gnu/libc.so.6(+0x30412)[0x7fb364e0f412]
      addr2line: './sql/mysqld': No such file
      ./sql/mysqld(+0x8f33f7)[0x555e0eec03f7]
      ./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]
      ./sql/mysqld(_ZN13st_join_table21choose_best_splittingEdy+0x482)[0x555e0f0de706]
      ./sql/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyPK11st_positionjbdPS3_S6_+0x27b)[0x555e0eebbbb4]
      ./sql/mysqld(+0x8f5404)[0x555e0eec2404]
      ./sql/mysqld(+0x8f5b58)[0x555e0eec2b58]
      ./sql/mysqld(+0x8f334e)[0x555e0eec034e]
      ./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]
      ./sql/mysqld(+0x8e9d02)[0x555e0eeb6d02]
      ./sql/mysqld(_ZN4JOIN14optimize_innerEv+0x1de3)[0x555e0eeaa87b]
      ./sql/mysqld(_ZN4JOIN8optimizeEv+0xc6)[0x555e0eea8126]
      ./sql/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x430)[0x555e0eeb38e5]
      ./sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x19d)[0x555e0eea2ee7]
      ./sql/mysqld(+0x899695)[0x555e0ee66695]
      ./sql/mysqld(_Z21mysql_execute_commandP3THD+0x1faa)[0x555e0ee5d465]
      ./sql/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2ed)[0x555e0ee6b7a0]
      ./sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x11fe)[0x555e0ee570f0]
      ./sql/mysqld(_Z10do_commandP3THD+0x7a5)[0x555e0ee557dc]
      ./sql/mysqld(_Z24do_handle_one_connectionP7CONNECTb+0x1e0)[0x555e0efffded]
      ./sql/mysqld(handle_one_connection+0x5b)[0x555e0efffb0d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb365d1a6db]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb364f0088f]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fb30c014888): SELECT 1 FROM dummy t LEFT JOIN (     SELECT dummy0.id     FROM dummy AS dummy0     LEFT JOIN dummy AS dummy1 ON 0     LEFT JOIN dummy AS dummy2 ON 0     LEFT JOIN dummy AS dummy3 ON 0     LEFT JOIN dummy AS dummy4 ON 0     LEFT JOIN dummy AS dummy5 ON 0     LEFT JOIN dummy AS dummy6 ON 0     LEFT JOIN dummy AS dummy7 ON 0     LEFT JOIN dummy AS dummy8 ON 0     LEFT JOIN dummy AS dummy9 ON 0     LEFT JOIN dummy AS dummy10 ON 0     LEFT JOIN dummy AS dummy11 ON 0     LEFT JOIN dummy AS dummy12 ON 0     LEFT JOIN dummy AS dummy13 ON 0     LEFT JOIN dummy AS dummy14 ON 0     LEFT JOIN dummy AS dummy15 ON 0     LEFT JOIN dummy AS dummy16 ON 0     LEFT JOIN dummy AS dummy17 ON 0     LEFT JOIN dummy AS dummy18 ON 0     LEFT JOIN dummy AS dummy19 ON 0     LEFT JOIN dummy AS dummy20 ON 0     LEFT JOIN dummy AS dummy21 ON 0     LEFT JOIN dummy AS dummy22 ON 0     LEFT JOIN dummy AS dummy23 ON 0     LEFT JOIN dummy AS dummy24 ON 0     LEFT JOIN dummy AS dummy25 ON 0     LEFT JOIN dummy AS dummy26 ON 0     LEFT JOIN dummy AS dummy27 ON 0     LEFT JOIN dummy AS dummy28 ON 0     LEFT JOIN dummy AS dummy29 ON 0     LEFT JOIN dummy AS dummy30 ON 0     LEFT JOIN dummy AS dummy31 ON 0     GROUP BY dummy0.id ) AS subquery     ON subquery.id = t.id
      Connection ID (thread ID): 3
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
       
      The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      information that should help you find out what is causing the crash.
      

      Originally the query was more complex (there are NDAs in place, cant share it, sorry) but I reduced it to the above query. Although the original case crashed with a SIGSEGV and not with a failed assertion:

      200306 13:11:51 [ERROR] mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
       
      To report this bug, see https://mariadb.com/kb/en/reporting-bugs
       
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed, 
      something is definitely wrong and this may fail.
       
      Server version: 10.3.15-MariaDB-1:10.3.15+maria~bionic
      key_buffer_size=134217728
      read_buffer_size=2097152
      max_used_connections=4
      max_threads=10002
      thread_count=9
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61808382 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7fb020000c08
      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...
      stack_bottom = 0x7fb08062fdd8 thread_stack 0x49000
      mysqld(my_print_stacktrace+0x2e)[0x559f00b0606e]
      mysqld(handle_fatal_signal+0x515)[0x559f005a26d5]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb09bb4e890]
      mysqld(_Z45fix_semijoin_strategies_for_picked_join_orderP4JOIN+0x88)[0x559f004d7198]
      mysqld(_ZN4JOIN20get_best_combinationEv+0xba)[0x559f003f8fca]
      mysqld(_ZN4JOIN15optimize_stage2Ev+0x143)[0x559f00416553]
      mysqld(_ZN4JOIN8optimizeEv+0x75)[0x559f0041b0c5]
      mysqld(_Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST+0xef)[0x559f0038f8ff]
      mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xb1)[0x559f0038f1b1]
      mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x559f003aa2f7]
      mysqld(_ZN4JOIN15optimize_stage2Ev+0x16c)[0x559f0041657c]
      mysqld(_ZN4JOIN14optimize_innerEv+0xe69)[0x559f00419619]
      mysqld(_ZN4JOIN8optimizeEv+0x37)[0x559f0041b087]
      mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x886)[0x559f0041ce86]
      mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14d)[0x559f0041d0bd]
      mysqld(+0x5651d1)[0x559f003be1d1]
      mysqld(_Z21mysql_execute_commandP3THD+0x6015)[0x559f003caac5]
      mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20a)[0x559f003cd42a]
      mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1cd7)[0x559f003cfe57]
      mysqld(_Z10do_commandP3THD+0x178)[0x559f003d0bf8]
      mysqld(_Z24do_handle_one_connectionP7CONNECT+0x212)[0x559f004a1532]
      mysqld(handle_one_connection+0x3d)[0x559f004a170d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb09bb436db]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb09b14588f]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fb02000f5d0): [[ redacted ]]
      Connection ID (thread ID): 84
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
       
      The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      information that should help you find out what is causing the crash.
      

      Attachments

        Issue Links

          Activity

            I forgot to mention: It doesn't seem to matter what you LEFT JOIN in the subquery, you can also use (SELECT 1 FROM DUAL) instead of the dummy table. Or these could be themselves subqueries with complex ON conditions (in the original case some of these were subqueries themselves).

            laria Laria Chabowski added a comment - I forgot to mention: It doesn't seem to matter what you LEFT JOIN in the subquery, you can also use (SELECT 1 FROM DUAL) instead of the dummy table. Or these could be themselves subqueries with complex ON conditions (in the original case some of these were subqueries themselves).
            alice Alice Sherepa added a comment - - edited

            Thanks! Repeatable on current 10.3-10.5, with InnoDb.
            It seems to be related to MDEV-19132 (MDEV-21575)
            As a workaround please try to set optimizer_switch='split_materialized=off';

            10.3 f8ab5ca374243977f331c

            #3  <signal handler called>
            #4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
            #5  0x00007fc52dc53535 in __GI_abort () at abort.c:79
            #6  0x00007fc52dc5340f in __assert_fail_base (fmt=0x7fc52ddb5ee0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55bfa1a83db8 "join->best_read < double(1.79769313486231570814527423731704357e+308L)", file=0x55bfa1a82ea8 "/10.3/sql/sql_select.cc", line=8107, function=<optimized out>) at assert.c:92
            #7  0x00007fc52dc61102 in __GI___assert_fail (assertion=0x55bfa1a83db8 "join->best_read < double(1.79769313486231570814527423731704357e+308L)", file=0x55bfa1a82ea8 "/10.3/sql/sql_select.cc", line=8107, function=0x55bfa1a869a0 <greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:101
            #8  0x000055bfa0e41fbc in greedy_search (join=0x7fc4d8046c40, remaining_tables=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8107
            #9  0x000055bfa0e41227 in choose_plan (join=0x7fc4d8046c40, join_tables=0) at /10.3/sql/sql_select.cc:7675
            #10 0x000055bfa10061ee in st_join_table::choose_best_splitting (this=0x7fc4d80ea938, record_count=1, remaining_tables=2) at /10.3/sql/opt_split.cc:974
            #11 0x000055bfa0e3e5a1 in best_access_path (join=0x7fc4d80465f0, s=0x7fc4d80ea938, remaining_tables=2, join_positions=0x7fc4d80eaee8, idx=1, disable_jbuf=false, record_count=1, pos=0x7fc4d80eaff8, loose_scan_pos=0x7fc52806de50) at /10.3/sql/sql_select.cc:6882
            #12 0x000055bfa0e43e61 in best_extension_by_limited_search (join=0x7fc4d80465f0, remaining_tables=2, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8887
            #13 0x000055bfa0e443cf in best_extension_by_limited_search (join=0x7fc4d80465f0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8958
            #14 0x000055bfa0e41f13 in greedy_search (join=0x7fc4d80465f0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8099
            #15 0x000055bfa0e41227 in choose_plan (join=0x7fc4d80465f0, join_tables=3) at /10.3/sql/sql_select.cc:7675
            #16 0x000055bfa0e39b8e in make_join_statistics (join=0x7fc4d80465f0, tables_list=..., keyuse_array=0x7fc4d80468e0) at /10.3/sql/sql_select.cc:5143
            #17 0x000055bfa0e2e514 in JOIN::optimize_inner (this=0x7fc4d80465f0) at /10.3/sql/sql_select.cc:1942
            #18 0x000055bfa0e2ca47 in JOIN::optimize (this=0x7fc4d80465f0) at /10.3/sql/sql_select.cc:1488
            #19 0x000055bfa0e36a44 in mysql_select (thd=0x7fc4d8000d50, tables=0x7fc4d8013488, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc4d80465c8, unit=0x7fc4d8004c18, select_lex=0x7fc4d80053a0) at /10.3/sql/sql_select.cc:4283
            #20 0x000055bfa0e2831e in handle_select (thd=0x7fc4d8000d50, lex=0x7fc4d8004b58, result=0x7fc4d80465c8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:370
            #21 0x000055bfa0df0022 in execute_sqlcom_select (thd=0x7fc4d8000d50, all_tables=0x7fc4d8013488) at /10.3/sql/sql_parse.cc:6293
            #22 0x000055bfa0de6a97 in mysql_execute_command (thd=0x7fc4d8000d50) at /10.3/sql/sql_parse.cc:3820
            #23 0x000055bfa0df42fe in mysql_parse (thd=0x7fc4d8000d50, rawbuf=0x7fc4d8012a78 "SELECT 1\nFROM dummy t\nLEFT JOIN (\nSELECT dummy0.id\nFROM dummy AS dummy0\nLEFT JOIN dummy AS dummy1 ON 0\nLEFT JOIN dummy AS dummy2 ON 0\nLEFT JOIN dummy AS dummy3 ON 0\nLEFT JOIN dummy AS dummy4 ON 0\nLEFT"..., length=1109, parser_state=0x7fc52806f5c0, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:7817
            #24 0x000055bfa0de0f83 in dispatch_command (command=COM_QUERY, thd=0x7fc4d8000d50, packet=0x7fc4d8008ed1 "SELECT 1\nFROM dummy t\nLEFT JOIN (\nSELECT dummy0.id\nFROM dummy AS dummy0\nLEFT JOIN dummy AS dummy1 ON 0\nLEFT JOIN dummy AS dummy2 ON 0\nLEFT JOIN dummy AS dummy3 ON 0\nLEFT JOIN dummy AS dummy4 ON 0\nLEFT"..., packet_length=1109, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1855
            #25 0x000055bfa0ddf8d6 in do_command (thd=0x7fc4d8000d50) at /10.3/sql/sql_parse.cc:1401
            #26 0x000055bfa0f5586f in do_handle_one_connection (connect=0x55bfa3807330) at /10.3/sql/sql_connect.cc:1403
            #27 0x000055bfa0f555d1 in handle_one_connection (arg=0x55bfa3807330) at /10.3/sql/sql_connect.cc:1308
            #28 0x000055bfa18e8a51 in pfs_spawn_thread (arg=0x55bfa3819a70) at /10.3/storage/perfschema/pfs.cc:1869
            #29 0x00007fc52e37efa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
            #30 0x00007fc52dd2a4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
            

            --source include/have_innodb.inc
            CREATE TABLE t (id INT NOT NULL PRIMARY KEY) engine=innodb;
            insert into t values (1),(2),(3);
             
            SELECT 1 FROM t 
            LEFT JOIN (
                SELECT t0.id FROM t AS t0
                LEFT JOIN t AS t1 ON 0
                LEFT JOIN t AS t2 ON 0
                LEFT JOIN t AS t3 ON 0
                LEFT JOIN t AS t4 ON 0
                LEFT JOIN t AS t5 ON 0
                LEFT JOIN t AS t6 ON 0
                LEFT JOIN t AS t7 ON 0
                LEFT JOIN t AS t8 ON 0
                LEFT JOIN t AS t9 ON 0
                LEFT JOIN t AS t10 ON 0
                LEFT JOIN t AS t11 ON 0
                LEFT JOIN t AS t12 ON 0
                LEFT JOIN t AS t13 ON 0
                LEFT JOIN t AS t14 ON 0
                LEFT JOIN t AS t15 ON 0
                LEFT JOIN t AS t16 ON 0
                LEFT JOIN t AS t17 ON 0
                LEFT JOIN t AS t18 ON 0
                LEFT JOIN t AS t19 ON 0
                LEFT JOIN t AS t20 ON 0
                LEFT JOIN t AS t21 ON 0
                LEFT JOIN t AS t22 ON 0
                LEFT JOIN t AS t23 ON 0
                LEFT JOIN t AS t24 ON 0
                LEFT JOIN t AS t25 ON 0
                LEFT JOIN t AS t26 ON 0
                LEFT JOIN t AS t27 ON 0
                LEFT JOIN t AS t28 ON 0
                LEFT JOIN t AS t29 ON 0
                LEFT JOIN t AS t30 ON 0
                LEFT JOIN t AS t31 ON 0
                GROUP BY t0.id) AS dt ON dt.id = t.id;
            

            alice Alice Sherepa added a comment - - edited Thanks! Repeatable on current 10.3-10.5, with InnoDb. It seems to be related to MDEV-19132 ( MDEV-21575 ) As a workaround please try to set optimizer_switch='split_materialized=off'; 10.3 f8ab5ca374243977f331c #3 <signal handler called> #4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #5 0x00007fc52dc53535 in __GI_abort () at abort.c:79 #6 0x00007fc52dc5340f in __assert_fail_base (fmt=0x7fc52ddb5ee0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55bfa1a83db8 "join->best_read < double(1.79769313486231570814527423731704357e+308L)", file=0x55bfa1a82ea8 "/10.3/sql/sql_select.cc", line=8107, function=<optimized out>) at assert.c:92 #7 0x00007fc52dc61102 in __GI___assert_fail (assertion=0x55bfa1a83db8 "join->best_read < double(1.79769313486231570814527423731704357e+308L)", file=0x55bfa1a82ea8 "/10.3/sql/sql_select.cc", line=8107, function=0x55bfa1a869a0 <greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:101 #8 0x000055bfa0e41fbc in greedy_search (join=0x7fc4d8046c40, remaining_tables=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8107 #9 0x000055bfa0e41227 in choose_plan (join=0x7fc4d8046c40, join_tables=0) at /10.3/sql/sql_select.cc:7675 #10 0x000055bfa10061ee in st_join_table::choose_best_splitting (this=0x7fc4d80ea938, record_count=1, remaining_tables=2) at /10.3/sql/opt_split.cc:974 #11 0x000055bfa0e3e5a1 in best_access_path (join=0x7fc4d80465f0, s=0x7fc4d80ea938, remaining_tables=2, join_positions=0x7fc4d80eaee8, idx=1, disable_jbuf=false, record_count=1, pos=0x7fc4d80eaff8, loose_scan_pos=0x7fc52806de50) at /10.3/sql/sql_select.cc:6882 #12 0x000055bfa0e43e61 in best_extension_by_limited_search (join=0x7fc4d80465f0, remaining_tables=2, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8887 #13 0x000055bfa0e443cf in best_extension_by_limited_search (join=0x7fc4d80465f0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8958 #14 0x000055bfa0e41f13 in greedy_search (join=0x7fc4d80465f0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8099 #15 0x000055bfa0e41227 in choose_plan (join=0x7fc4d80465f0, join_tables=3) at /10.3/sql/sql_select.cc:7675 #16 0x000055bfa0e39b8e in make_join_statistics (join=0x7fc4d80465f0, tables_list=..., keyuse_array=0x7fc4d80468e0) at /10.3/sql/sql_select.cc:5143 #17 0x000055bfa0e2e514 in JOIN::optimize_inner (this=0x7fc4d80465f0) at /10.3/sql/sql_select.cc:1942 #18 0x000055bfa0e2ca47 in JOIN::optimize (this=0x7fc4d80465f0) at /10.3/sql/sql_select.cc:1488 #19 0x000055bfa0e36a44 in mysql_select (thd=0x7fc4d8000d50, tables=0x7fc4d8013488, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc4d80465c8, unit=0x7fc4d8004c18, select_lex=0x7fc4d80053a0) at /10.3/sql/sql_select.cc:4283 #20 0x000055bfa0e2831e in handle_select (thd=0x7fc4d8000d50, lex=0x7fc4d8004b58, result=0x7fc4d80465c8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:370 #21 0x000055bfa0df0022 in execute_sqlcom_select (thd=0x7fc4d8000d50, all_tables=0x7fc4d8013488) at /10.3/sql/sql_parse.cc:6293 #22 0x000055bfa0de6a97 in mysql_execute_command (thd=0x7fc4d8000d50) at /10.3/sql/sql_parse.cc:3820 #23 0x000055bfa0df42fe in mysql_parse (thd=0x7fc4d8000d50, rawbuf=0x7fc4d8012a78 "SELECT 1\nFROM dummy t\nLEFT JOIN (\nSELECT dummy0.id\nFROM dummy AS dummy0\nLEFT JOIN dummy AS dummy1 ON 0\nLEFT JOIN dummy AS dummy2 ON 0\nLEFT JOIN dummy AS dummy3 ON 0\nLEFT JOIN dummy AS dummy4 ON 0\nLEFT"..., length=1109, parser_state=0x7fc52806f5c0, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:7817 #24 0x000055bfa0de0f83 in dispatch_command (command=COM_QUERY, thd=0x7fc4d8000d50, packet=0x7fc4d8008ed1 "SELECT 1\nFROM dummy t\nLEFT JOIN (\nSELECT dummy0.id\nFROM dummy AS dummy0\nLEFT JOIN dummy AS dummy1 ON 0\nLEFT JOIN dummy AS dummy2 ON 0\nLEFT JOIN dummy AS dummy3 ON 0\nLEFT JOIN dummy AS dummy4 ON 0\nLEFT"..., packet_length=1109, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1855 #25 0x000055bfa0ddf8d6 in do_command (thd=0x7fc4d8000d50) at /10.3/sql/sql_parse.cc:1401 #26 0x000055bfa0f5586f in do_handle_one_connection (connect=0x55bfa3807330) at /10.3/sql/sql_connect.cc:1403 #27 0x000055bfa0f555d1 in handle_one_connection (arg=0x55bfa3807330) at /10.3/sql/sql_connect.cc:1308 #28 0x000055bfa18e8a51 in pfs_spawn_thread (arg=0x55bfa3819a70) at /10.3/storage/perfschema/pfs.cc:1869 #29 0x00007fc52e37efa3 in start_thread (arg=<optimized out>) at pthread_create.c:486 #30 0x00007fc52dd2a4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 --source include/have_innodb.inc CREATE TABLE t (id INT NOT NULL PRIMARY KEY ) engine=innodb; insert into t values (1),(2),(3);   SELECT 1 FROM t LEFT JOIN ( SELECT t0.id FROM t AS t0 LEFT JOIN t AS t1 ON 0 LEFT JOIN t AS t2 ON 0 LEFT JOIN t AS t3 ON 0 LEFT JOIN t AS t4 ON 0 LEFT JOIN t AS t5 ON 0 LEFT JOIN t AS t6 ON 0 LEFT JOIN t AS t7 ON 0 LEFT JOIN t AS t8 ON 0 LEFT JOIN t AS t9 ON 0 LEFT JOIN t AS t10 ON 0 LEFT JOIN t AS t11 ON 0 LEFT JOIN t AS t12 ON 0 LEFT JOIN t AS t13 ON 0 LEFT JOIN t AS t14 ON 0 LEFT JOIN t AS t15 ON 0 LEFT JOIN t AS t16 ON 0 LEFT JOIN t AS t17 ON 0 LEFT JOIN t AS t18 ON 0 LEFT JOIN t AS t19 ON 0 LEFT JOIN t AS t20 ON 0 LEFT JOIN t AS t21 ON 0 LEFT JOIN t AS t22 ON 0 LEFT JOIN t AS t23 ON 0 LEFT JOIN t AS t24 ON 0 LEFT JOIN t AS t25 ON 0 LEFT JOIN t AS t26 ON 0 LEFT JOIN t AS t27 ON 0 LEFT JOIN t AS t28 ON 0 LEFT JOIN t AS t29 ON 0 LEFT JOIN t AS t30 ON 0 LEFT JOIN t AS t31 ON 0 GROUP BY t0.id) AS dt ON dt.id = t.id;

            As a workaround please try to set optimizer_switch='split_materialized=off';

            This works like a charm. Both in the reduced test case and with the original query.

            laria Laria Chabowski added a comment - As a workaround please try to set optimizer_switch='split_materialized=off'; This works like a charm. Both in the reduced test case and with the original query.
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.3

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3

            Finally took the time to check it out. It works. Thank you!

            laria Laria Chabowski added a comment - Finally took the time to check it out. It works. Thank you!

            People

              igor Igor Babaev
              laria Laria Chabowski
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.