[MDEV-21883] Server crashes when joining a subselect with 32 tables and GROUP BY Created: 2020-03-06  Updated: 2020-04-20  Resolved: 2020-04-01

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.3.15, 10.4.12, 10.3, 10.4, 10.5
Fix Version/s: 10.3.23, 10.4.13, 10.5.3

Type: Bug Priority: Major
Reporter: Laria Chabowski Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

$ 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

Issue Links:
Relates
relates to MDEV-19132 Strange combination of JOIN with NULL... Confirmed

 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.



 Comments   
Comment by Laria Chabowski [ 2020-03-06 ]

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).

Comment by Alice Sherepa [ 2020-03-06 ]

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;

Comment by Laria Chabowski [ 2020-03-09 ]

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.

Comment by Igor Babaev [ 2020-04-01 ]

A fix for this bug was pushed into 10.3

Comment by Laria Chabowski [ 2020-04-20 ]

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

Generated at Thu Feb 08 09:10:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.