Details
Blocker Description
How to trigger
CREATE TABLE t0 ( c0 INT ( 1 ) NOT NULL , c1 INT NOT NULL PRIMARY KEY ) ; |
SELECT ra4 . c1 ca6 FROM t0 ra3 LEFT OUTER JOIN t0 ra4 ON ra3 . c0 = ra4 . c1 WHERE ra3 . c0 IN ( SELECT CAST( ra6 . c1 IS NULL AS SIGNED ) ca0 FROM t0 ra5 LEFT JOIN ( t0 ra6 LEFT JOIN t0 ra7 ON ra3 . c0 = ra7 . c1 ) ON ra5 . c1 = ra3 . c0 ) ; |
Server error log
Server version: 10.11.5-MariaDB source revision: 7875294b6b74b53dd3aaa723e6cc103d2bb47b2c
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=1
|
max_threads=153
|
thread_count=21
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 468037 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x7faadc0022c8
|
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 = 0x7fab4404ad98 thread_stack 0x49000
|
mysys/stacktrace.c:216(my_print_stacktrace)[0x56430268725e]
|
sql/signal_handler.cc:241(handle_fatal_signal)[0x5643021f8765]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7fab4742f980]
|
sql/opt_split.cc:1341(JOIN::fix_all_splittings_in_plan())[0x564302174f10]
|
sql/sql_select.cc:2576(JOIN::optimize_inner())[0x56430203b8a5]
|
sql/sql_select.cc:1897(JOIN::optimize())[0x56430203baf3]
|
sql/sql_select.cc:5098(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56430203bbd9]
|
sql/sql_select.cc:586(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x56430203c3be]
|
sql/sql_parse.cc:6289(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564301fc7cee]
|
sql/sql_parse.cc:6025(mysql_execute_command(THD*, bool))[0x564301fd13f1]
|
sql/sql_parse.cc:8051(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x564301fc33af]
|
sql/sql_parse.cc:1865(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x564301fce104]
|
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x564301fcf60b]
|
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5643020d9bbf]
|
sql/sql_connect.cc:1318(handle_one_connection)[0x5643020d9efd]
|
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x5643023d9e85]
|
nptl/pthread_create.c:463(start_thread)[0x7fab474246db]
|
x86_64/clone.S:97(clone)[0x7fab4658661f]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7faadc016850): SELECT ra4 . c1 ca6 FROM t0 ra3 LEFT OUTER JOIN t0 ra4 ON ra3 . c0 = ra4 . c1 WHERE ra3 . c0 IN ( SELECT CAST( ra6 . c1 IS NULL AS SIGNED ) ca0 FROM t0 ra5 LEFT JOIN ( t0 ra6 LEFT JOIN t0 ra7 ON ra3 . c0 = ra7 . c1 ) ON ra5 . c1 = ra3 . c0 )
|
|
|
Connection ID (thread ID): 23
|
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,hash_join_cardinality=off
|
Attachments
Issue Links
- links to
Activity
Analysis:
The IN subquery is converted into a semi-join:
t1 SEMI JOIN ( |
t1 c LEFT JOIN (t1 a LEFT JOIN t1 b ON t1.i = b.i) ON c.i = t1.i |
)
|
(Note that the ON t1.i = b.i refers to the first table t1, not to t1 c. In the subquery, this was an outer reference)
then optimizer trace shows table dependencies:
a.dependent= { t1, c } -- from the "ON c.i = t1.i"
|
b.dependent= { t1 } --
|
Then, we build a join prefix of
t1, b
|
and then we fail an assertion because we can't extend it any further.
We can't extend it, because
- we are inside a nested join, so we need to put table a into the join order before we can put any table that's outside of the nested join.
- We cannot put table a, because a.dependent includes c which is not in the join prefix.
The patch looks reasonable, properly sets table dependencies for the target test case and does not break any existing tests. In my opinion, it is legitimate for pushing, however I must say I'm not an expert in the `simplify_joins()` logic.
For the release notes:
A query using a subquery in form:
WHERE col IN (SELECT ... LEFT JOIN tbl ON tbl.column=reference_outside_subquery)
could cause a crash in the optimizer. The essential part is that ON expression has
only two kinds of references: 1.to inner side of the outer join and 2. to outside the subquery.
Thank you for the report!
I repeated on 10.10-11.3 with InnoDb engine, no crash with Myisam/Aria. Not repeatable on 10.4-10.9
--source include/have_innodb.incVersion: '10.10.7-MariaDB-debug-log'mariadbd: /10.10/src/sql/sql_select.cc:10633: enum_best_search best_extension_by_limited_search(JOIN*, table_map, uint, double, double, uint, uint, table_map*): Assertion `found_tables > 0' failed.230913 15:40:25 [ERROR] mysqld got signal 6 ;Server version: 10.10.7-MariaDB-debug-log source revision: cb384d0d04bed63b714d6d7b02d26b19d22740ccsql/signal_handler.cc:241(handle_fatal_signal)[0x5641ebd4a2bc]/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb)[0x7fc02b94d00b]sql/sql_select.cc:10638(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x5641eb59c4ff]sql/sql_select.cc:10832(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x5641eb59dc40]sql/sql_select.cc:10832(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x5641eb59dc40]sql/sql_select.cc:9619(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int))[0x5641eb596dab]sql/sql_select.cc:9181(choose_plan(JOIN*, unsigned long long))[0x5641eb594c1d]sql/sql_select.cc:6036(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5641eb57dc06]sql/sql_select.cc:2561(JOIN::optimize_inner())[0x5641eb559b86]sql/sql_select.cc:1895(JOIN::optimize())[0x5641eb552aa7]sql/sql_select.cc:5106(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5641eb574774]sql/sql_select.cc:586(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5641eb54435f]sql/sql_parse.cc:6287(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5641eb4658ce]sql/sql_parse.cc:3958(mysql_execute_command(THD*, bool))[0x5641eb4540ef]sql/sql_parse.cc:8057(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5641eb470afc]sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5641eb4467c6]sql/sql_parse.cc:1407(do_command(THD*, bool))[0x5641eb443524]sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5641eb90c817]sql/sql_connect.cc:1320(handle_one_connection)[0x5641eb90c174]perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5641ec542288]nptl/pthread_create.c:478(start_thread)[0x7fc02be58609]/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fc02ba29133]Query (0x6290001092a8): SELECT 1 FROM t1 WHERE i IN(SELECT 1 FROM t1 cLEFT JOIN (t1 a LEFT JOIN t1 b ON t1.i = b.i) ON c.i = t1.i)