| Bug #36133 | Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && | ||
|---|---|---|---|
| Submitted: | 16 Apr 2008 11:31 | Modified: | 22 Nov 2010 1:13 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0.4-nt, 6.0.5-bk | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[16 Apr 2008 11:31]
Shane Bester
[16 Apr 2008 12:14]
MySQL Verification Team
Verified on Suse 10.3 X64. I will test 5.0/5.1.
080416 9:04:59 [Note] Event Scheduler: Loaded 0 events
080416 9:04:59 [Note] /home/miguel/dbs/6.0/libexec/mysqld: ready for connections.
Version: '6.0.5-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
[New Thread 0x49093950 (LWP 18280)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x49093950 (LWP 18280)]
0x000000000079c4d7 in free_tmp_table (thd=0x1f143e8, entry=0x1f9d7c8) at sql_select.cc:12801
12801 entry->file->ha_drop_table(entry->s->table_name.str);
(gdb) bt full
#0 0x000000000079c4d7 in free_tmp_table (thd=0x1f143e8, entry=0x1f9d7c8) at sql_select.cc:12801
own_root = {free = 0x8f8f8f8f8f8f8f8f, used = 0x8f8f8f8f8f8f8f8f, pre_alloc = 0x8f8f8f8f8f8f8f8f,
min_malloc = 10344644715844964239, block_size = 10344644715844964239, block_num = 2408550287, first_block_usage = 2408550287,
error_handler = 0x8f8f8f8f8f8f8f8f}
save_proc_info = 0xe242b6 "Sending data"
_db_func_ = 0xe2384e "JOIN::cleanup"
_db_file_ = 0xe22bb7 "sql_select.cc"
_db_level_ = 19
_db_framep_ = (char **) 0x4908c958
__FUNCTION__ = "free_tmp_table"
<cut>
[16 Apr 2008 12:20]
MySQL Verification Team
Thank you for the bug report. Only 6.0BK is affected.
[24 Apr 2008 8:40]
Sergey Petrunya
A somewhat reduced test query: select 1 from t2 where t2.a > 1 or t2.a= (select a from t2 limit 1) and not t2.a not in (select t2.b from t2); attempt to remove any remaining part of the WHERE will cause the crash to go away.
[24 Apr 2008 8:58]
Sergey Petrunya
Even more reduced query that still causes the crash:
select 1 from t2 where
t2.a > 1
or
t2.a = 3 and
not t2.a not in (select t2.b from t2);
The stacktrace is:
mysqld: item_subselect.cc:300: virtual bool Item_in_subselect::exec(): Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)' failed.
Program received signal SIGABRT, Aborted.
[Switching to Thread 0xad167b90 (LWP 11695)]
0xffffe410 in __kernel_vsyscall ()
(gdb) where
#0 0xffffe410 in __kernel_vsyscall ()
#1 0xb7d02101 in raise () from /lib/libc.so.6
#2 0xb7d038e8 in abort () from /lib/libc.so.6
#3 0xb7cfb7a5 in __assert_fail () from /lib/libc.so.6
#4 0x082d1981 in Item_in_subselect::exec (this=0x93a0c78) at item_subselect.cc:298
#5 0x082ca7b0 in Item_in_subselect::val_bool (this=0x93a0c78) at item_subselect.cc:899
#6 0x082633b8 in Item::val_bool_result (this=0x93a0c78) at ../item.h:742
#7 0x08292933 in Item_in_optimizer::val_int (this=0x93a1088) at item_cmpfunc.cc:1596
#8 0x084311a1 in get_mm_tree (param=0xad162ef0, cond=0x93a1088) at opt_range.cc:5342
#9 0x08430fa2 in get_mm_tree (param=0xad162ef0, cond=0x93a0de0) at opt_range.cc:5302
#10 0x08431091 in get_mm_tree (param=0xad162ef0, cond=0x93a0e98) at opt_range.cc:5319
#11 0x08437e8e in SQL_SELECT::test_quick_select (this=0x93a1878, thd=0x9360290, keys_to_use={map = 1}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2305
#12 0x083a6e71 in get_quick_record_count (thd=0x9360290, select=0x93a1878, table=0x9373290, keys=0x93a1538, limit=18446744073709551615) at sql_select.cc:3706
#13 0x083ab9a4 in make_join_statistics (join=0x93a7228, tables=0x0, conds=0x93a0e98, keyuse_array=0x93a8520) at sql_select.cc:4125
#14 0x083ae65a in JOIN::optimize (this=0x93a7228) at sql_select.cc:1522
#15 0x083b234e in mysql_select (thd=0x9360290, rref_pointer_array=0x9361830, tables=0x939fed8, wild_num=0, fields=@0x93617c0, conds=0x93a0e98, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x93a0fa0, unit=0x93614a4, select_lex=0x936172c) at sql_select.cc:2946
#16 0x083b74ac in handle_select (thd=0x9360290, lex=0x9361448, result=0x93a0fa0, setup_tables_done_option=0) at sql_select.cc:289
#17 0x0832a67b in execute_sqlcom_select (thd=0x9360290, all_tables=0x939fed8) at sql_parse.cc:4806
#18 0x0832bb3a in mysql_execute_command (thd=0x9360290) at sql_parse.cc:1994
#19 0x083349b0 in mysql_parse (thd=0x9360290, inBuf=0x939fd30 "select 1 from t2 where \n t2.a > 1 \n or \n t2.a = 3 and \n not t2.a not in (select t2b.b from t2b)", length=102, found_semicolon=0xad167260) at sql_parse.cc:5767
[24 Apr 2008 9:25]
Sergey Petrunya
Analysis:
The problem affects only subqueries that are executed using Materialization
strategy.
The problem is caused by this scenario:
> parent_join->prepare()
| > Item_subselect::fix_fields()
| | > child_join->prepare()
| | | subquery is slated for materialization, IN->EXISTS rewrite is not
| | | performed
| | < child_join->prepare()
| < Item_subselect::fix_fields()
< parent_join->prepare()
// irrelevant, but note location of this call
parent_join->flatten_subqueries() // ------ (1)
> parent_join->optimize()
| > invoke the range optimizer
| | > get_mm_parts()
| | | if (cond->const_item())
| | | {
| | | // evaluate the subquery predicate ------ (2)
| | | This fails the assert because setup_subquery_materialization()
| | | hasn't been called yet.
| | | }
| | < get_mm_parts()
| < invoke the range optimizer
|
| > parent_join->setup_subquery_materialization()
| |
| | it's too late now, we needed the setup to be done before location (2).
| | btw, semi-join subqueries don't have this problem because they are
| | handled at earlier stage at location (1)
| < parent_join->setup_subquery_materialization()
|
> parent_join->optimize()
I see two solutions:
1. Make IN-subquery predicates that use materialization survive such handling.
Let materialized IN-subquery predicate call setup_engine() for itself it
hasn't yet been done for him by join::setup_subquery_materialization()
2. Make the range optimizer to never evaluate subqueries. Use the
Item::is_expensive()
function to detect predicates that must not be evaluated by the optimizer.
This should cover subqueries.
I'm leaning towards Solution #2 as it solves the "long running EXPLAIN" problem. we might miss some good QEPs if there are cheap single-lookup subqueries, though.
[24 Apr 2008 22:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/45976 ChangeSet@1.2626, 2008-04-25 02:06:49+04:00, sergefp@mysql.com +5 -0 BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &" - The problem was that the range optimizer evaluated constant expressions, and among them it would try to evaluate IN-subquery predicates slated for handling with materialization strategy. However, these predicates require that parent_join->setup_subquery_materialization() is invoked before one attempts to evaluate them. - Fixed by making the range optimizer not to evaluate expressions that have item->is_expensive() == TRUE (these are materialization subqueries and stored function calls). This should also resolve the problem that EXPLAIN may be too long. This change cuts off some opportunities for range optimizer, but this is the price we're willing to pay for separation of query optimization and execution.
[25 Apr 2008 0:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/45978 ChangeSet@1.2626, 2008-04-25 03:59:38+04:00, sergefp@mysql.com +5 -0 BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &" - The problem was that the range optimizer evaluated constant expressions, and among them it would try to evaluate IN-subquery predicates slated for handling with materialization strategy. However, these predicates require that parent_join->setup_subquery_materialization() is invoked before one attempts to evaluate them. - Fixed by making the range optimizer not to evaluate expressions that have item->is_expensive() == TRUE (these are materialization subqueries and stored function calls). This should also resolve the problem that EXPLAIN may be too long. This change cuts off some opportunities for range optimizer, but this is the price we're willing to pay for separation of query optimization and execution.
[28 May 2008 10:01]
Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 18:15]
Paul DuBois
Noted in 6.0.6 changelog. Range optimizer evaluation of IN subqueries to be handled with the materialization strategy could lead to assertion failure.
[16 Aug 2010 6:37]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:08]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:13]
Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:28]
Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.
