Bug #49453 | re-execution of prepared statement with view and semijoin crashes | ||
---|---|---|---|
Submitted: | 4 Dec 2009 10:31 | Modified: | 23 Nov 2010 3:06 |
Reporter: | Tor Didriksen | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 6.0 | OS: | Any |
Assigned to: | Jørgen Løland | CPU Architecture: | Any |
Tags: | optimizer_switch, semijoin, subquery |
[4 Dec 2009 10:31]
Tor Didriksen
[10 Dec 2009 13:16]
Jørgen Løland
Why the crash happens: In the first execution, the parser sets up the item_list for the subquery (item_list in st_select_lex). The item_list contains one Item_field which is the view field. During Item_field::fix_fields (part of setup_fields), this Item_field is substituted with an Item_direct_view_ref to the t2 field, and the substitution is stored in ref_pointer_array in st_select_lex. In convert_subq_to_sj(), this Item_direct_view_ref is pulled out to the main query (using an Item_func_eq). In the second execution, the item_list of the subquery is first emptied before the parser once more sets up the item_list. Like before, the list for the subquery lex contains an Item_field for the view field and nothing more. However, the subselect is no longer part of the query (the substituted subquery was pulled out), so the Item_field is not fixed (and hence not substituted) this time. Even so, optimize_semijoin_nests() tries to find the best access plan by reading information about the fields in the subquery: sql_select.cc#optimize_semijoin_nests(): List<Item> &right_expr_list= sj_nest->sj_subq_pred->unit->first_select()->item_list; Note: similar code ^ in setup_sj_materialization() that must be fixed as well. So, in the first execution of the PS, right_expr_list contains the substituted Item (the Item_direct_view_ref). In the second execution, the right_expr_list contains the Item_field. The Item_field has not been fixed, so when we do this: map |= item->used_tables(); Item_field::used_tables() crashes because it's field is a null-pointer.
[10 Dec 2009 13:17]
Jørgen Løland
Some suggestions for how to fix the bug: 1. During the first execution, the st_select_lex of the subselect stored the substituted item in it's ref_pointer_array. Theory: The ref_pointer_array will remain constant after the first execution because setup_fields() is not performed on the items in this select lex. Therefore, the references to sj_nest->sj_subq_pred->unit->first_select()->item_list can be replaced with sj_nest->sj_subq_pred->unit->first_select()->ref_pointer_array; 2. The Item_field stored in the subselect item_list is the same object in all executions of the statement. By adding "Item *replaced_with_item" to Item_field, we can get the Item_direct_view_ref from the Item_field by doing while (item->type() == Item::FIELD_ITEM && ((Item_field*)item)->replaced_with) item= ((Item_field*)item)->replaced_with; on the items in the item_list 3. A variable "List<Item> sjnest_resolved_item_list" can be added to TABLE_LIST. The items in the item_list of the subselect can be stored here for all semijoin nests during convert_subq_to_sj(). 4. "Manually" call setup_fields on the subselect of a sj_nest so that the Item_field is replaced with a new Item_direct_view_ref. Not sure if this will work yet. I have verified that #1 and #2 work, but the theory of #1 needs to be confirmed. However, after removing the crash using #1 or #2, I get a wrong result set for the second execution. This is very similar to BUG#49198 and might be a duplicate.
[16 Dec 2009 13:44]
Jørgen Løland
Reason for wrong result on second execution: * In optimize_semijoin_nests(), the pulled out view item, an Item_ref, returns OUTER_REF_TABLE_BIT (via the Item_field it refers to) instead of it's table map here: map |= item->used_tables(); * It returns OUTER_REF_TABLE_BIT instead of the table map because Item_field depended_from points to the select lex of the inner select. * depended_from points to the inner select because when the pulled out Item_field is fixed (Item_field::fix_item), it's select lex is not the same select lex as that of the context. Specifically, the context is the outer query, while the item's select lex is that of the inner query. * The select lex of the pulled out item wrongly points to the select lex of the inner query because fix_after_pullout() was never called for the Item_field. The reason is this code in Item_ref::fix_after_pullout(): if (depended_from == new_parent) { (*ref)->fix_after_pullout(new_parent, ref); depended_from= NULL; } Since the view is referred only in the sub select, depended_from is NULL in our case.
[16 Dec 2009 13:46]
Jørgen Løland
Combining strategy #1 above with this change gives correct result of the query: void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) { + (*ref)->fix_after_pullout(new_parent, ref); if (depended_from == new_parent) { - (*ref)->fix_after_pullout(new_parent, ref); depended_from= NULL; }
[17 Dec 2009 13:12]
Jørgen Løland
The patch implements a variant of strategy #4 above. Views pulled out to semijoin nests are reresolved on next PS execution. This ensures that the Item_field is properly replaced with the Item_direct_view_ref just as in the first execution of the PS.
[17 Dec 2009 13:37]
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/94742 3778 Jorgen Loland 2009-12-17 Bug#49453: "re-execution of prepared statement with view and semijoin crashes" There were two bugs: 1) When semijoin was applied on a view, the replaced items were pulled out instead of Item_field since the items referring to view columns had already been fixed. However, the semijoin nest still stored the item list in sj_subq_pred, and on next execution of the PS, this item list was reset during parsing. Since this list is not attached to the query, Item_fields referring to view columns were not replaced with Item_direct_view_ref as would happen in normal execution. The fix for this is to reresolve views pulled out to semijoin nests on next execute. 2) After pulling views out to semijoin nests, fix_after_pullout is called on all the items. However, this call did not propagate to items referred to from views because Item_ref::fix_after_pullout() did only recursively call this method on it's children if the item had depended_from set to the select lex of the semijoin nest. The fix is to call fix_after_pullout() on the children even if the view was not dependent on that select lex. The Item_ref's depended_from will, e.g., be null if the view is only used inside the subselect. @ mysql-test/r/subselect_sj.result Added test for BUG#49453 @ mysql-test/r/subselect_sj_jcl6.result Added test for BUG#49453 @ mysql-test/t/subselect_sj.test Added test for BUG#49453 @ sql/item.cc Make Item_ref::fix_after_pullout() call fix_after_pullout() on children even if this Item_ref wasn't dependent on the select lex it has been pulled out to. @ sql/item_cmpfunc.cc REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing @ sql/sql_base.cc REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing @ sql/sql_class.h REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing @ sql/sql_select.cc Make views pulled out to a semijoin-nest be reresolved on next PS/SP execution. @ sql/sql_view.cc Resolve views over again if execution of PS/SP requires it. This happens if the view has been pulled out to a semijoin nest. @ sql/table.h Added variable bool view_needs_new_resolve, used to indicate that a view needs to be resolved on next PS/SP execution even though it was already resolved on a previous execution. This happens if the view has been pulled out to a semijoin nest.
[5 Jan 2010 15:22]
Jørgen Løland
An alternative to adding the view_needs_new_resolve variable in the patch above, is to reset the table_list's view pointer like this: while ((tl= li++)) { tl->embedding= sj_nest; tl->join_list= &nested_join->join_list; nested_join->join_list.push_back(tl); + if (tl->view) + tl->view= NULL; } However, it is expected that WL#4389 will make the handling of the subselect's select list cleaner. Thus, this bug is postponed for now.
[22 Jan 2010 12:18]
Roy Lyseng
Ran this test on the wl4389 branch. One additional changed code line makes the bug go away: Function optimize_semijoin_nests(): replace the assignment to right_expr_list with the expression sj_nest->nested_join->sj_inner_exprs.
[16 Mar 2010 10:04]
Tor Didriksen
See also Bug #50996 Wrong result for second call of prepared statement with view in subselect
[3 Aug 2010 9:20]
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/114909 3222 Jorgen Loland 2010-08-03 Bug#49453: "re-execution of prepared statement with view and semijoin crashes" When semijoin was applied on a view, the replaced items were pulled out instead of Item_field since the items referring to view columns had already been fixed. However, the semijoin nest still stored the item list in sj_subq_pred, and on next execution of the PS, this item list was reset during parsing. Since this list is not attached to the query, Item_fields referring to view columns were not replaced with Item_direct_view_ref as would happen in normal execution. The fix for this is to use the sj_inner_exprs list of the semijoin nest instead of the item list. The sj_inner_exprs list contains the resolved items in the subquery. @ mysql-test/include/subquery_sj.inc Added test for BUG#49453 @ mysql-test/r/subquery_sj_all.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_all_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_all_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_nosj.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none_jcl7.result Added test for BUG#49453 @ sql/sql_select.cc Use semijoin's sj_inner_exprs list instead of the subquery's item_list in optimize_semijoin_nest() since the item_list is not re-resolved on second execution of PS.
[9 Aug 2010 7:35]
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/115279 3223 Jorgen Loland 2010-08-09 Bug#49453: "re-execution of prepared statement with view and semijoin crashes" When semijoin was applied on a view, the replaced items were pulled out instead of Item_field since the items referring to view columns had already been fixed. However, the semijoin nest still stored the item list in sj_subq_pred, and on next execution of the PS, this item list was reset during parsing. Since this list is not attached to the query, Item_fields referring to view columns were not replaced with Item_direct_view_ref as would happen in normal execution. The fix for this is to use the sj_inner_exprs list of the semijoin nest instead of the item list. The sj_inner_exprs list contains the resolved items in the subquery. @ mysql-test/include/subquery_sj.inc Added test for BUG#49453 @ mysql-test/r/subquery_sj_all.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_all_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_all_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_dupsweed_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_firstmatch_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_loosescan_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_jcl7.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_mat_nosj.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none_jcl6.result Added test for BUG#49453 @ mysql-test/r/subquery_sj_none_jcl7.result Added test for BUG#49453 @ sql/sql_select.cc Use semijoin's sj_inner_exprs list instead of the subquery's item_list in optimize_semijoin_nest() since the item_list is not re-resolved on second execution of PS.
[9 Aug 2010 7:41]
Jørgen Løland
Pushed to mysql-next-mr-opt-backporting
[2 Oct 2010 18:15]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:10]
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)
[23 Nov 2010 3:06]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.