An error occurred fetching the project authors.
- 23 Aug, 2011 1 commit
-
-
unknown authored
Analysis: During the first execution of the query through the stored procedure, the optimization phase calls substitute_for_best_equal_field(), which calls Item_in_optimizer::transform(). The latter replaces Item_in_subselect::left_expr with args[0] via assignment. In this test case args[0] is an Item_outer_ref which is created/deallocated for each re-execution. As a result, during the second execution Item_in_subselect::left_expr pointed to freed memory, which resulted in a crash. Solution: The solution is to use change_item_tree(), so that the origianal left expression is restored after each execution.
-
- 18 Jul, 2011 1 commit
-
-
unknown authored
Analysis: Both the wrong result and the valgrind warning were a result of incomplete cleanup of the MIN/MAX subquery rewrite. At the first execution of the query, the non-aggregate subquery is transformed into an aggregate MIN/MAX subquery. During the fix_fields phase of the MIN/MAX function, it sets the property st_select_lex::with_sum_func to true. The second execution of the query finds this flag to be ON. When optimization reaches the same MIN/MAX subquery transformation, it tests if the subquery is an aggregate or not. Since select_lex->with_sum_func == true from the previous execution, the transformation executes the second branch that handles aggregate subqueries. This substitutes the subquery Item into a Item_maxmin_subselect. At the same time elsewhere it is assumed that the subquery Item is of type Item_allany_subselect. Ultimately this results in casting the actual object to the wrong class, and calling the wrong any_value() method from empty_underlying_subquery(). Solution: Cleanup the st_select_lex::with_sum_func property in the case when the MIN/MAX transformation was performed for a non-aggregate subquery, so that the transformation can be repeated.
-
- 14 Jul, 2011 1 commit
-
-
unknown authored
Analysis: For some of the re-executions of the correlated subquery the where clause is false. In these cases the execution of the subquery detects that it must generate a NULL row because of implicit grouping. In this case the subquery execution reaches the following code in do_select(): while ((table= li++)) mark_as_null_row(table->table); This code marks all rows in the table as complete NULL rows. In the example, when evaluating the field t2.f10 for the second row, all bits of Field::null_ptr[0] are set by the previous call to mark_as_null_row(). Then the call to Field::is_null() returns true, resulting in a NULL for the MAX function. Thus the lines above are not suitable for subquery re-execution because mark_as_null_row() changes the NULL bits of each table field, and there is no logic to restore these fields. Solution: The call to mark_as_null_row() was added by the fix for bug lp:613029. Therefore removing the fix for lp:613029 corrects this wrong result. At the same time the test for lp:613029 behaves correctly because the changes of MWL#89 result in a different execution path where: - the constant subquery is evaluated via JOIN::exec_const_cond - detecting that it has an empty result triggers the branch if (zero_result_cause) return_zero_rows() - return_zero_rows() calls mark_as_null_row().
-
- 08 Jul, 2011 3 commits
-
-
Sergey Petrunya authored
- Set the default - Adjust the testcases so that 'new' tests are run with optimizations turned on. - Pull out relevant tests from "irrelevant" tests and run them with optimizations on. - Run range.test and innodb.test with both mrr=on and mrr=off
-
unknown authored
The bug itself has been fixed by MWL#89.
-
unknown authored
The bug itself has been fixed by MWL#89.
-
- 07 Jul, 2011 3 commits
-
-
unknown authored
The bug itself has been fixed by MWL#89.
-
unknown authored
The bug itself has been fixed by MWL#89.
-
unknown authored
Analysis: This bug is yet another incarnation of the generic problem where optimization of the outer query triggers evaluation of a subquery, and this evaluation performs a destructive change to the subquery plan. Specifically a temp table is created for the DISTINCT operation that replaces the original subquery table. Later, select_describe() attempts to print the table name, however, there is no corresponding TABLE_LIST object to the internal temp table, so we get a crash. Execution works fine because it is not interested in the corresponding TABLE_LIST object (or its name). Solution: Similar to other such bugs, block the evaluation of expensive Items in convert_const_to_int().
-
- 04 Jul, 2011 2 commits
-
-
Sergey Petrunya authored
semijoin=on,firstmatch=on,loosescan=on to semijoin=off,firstmatch=off,loosescan=off Adjust the testcases: - Modify subselect*.test and join_cache.test so that all tests use the same execution paths as before (i.e. optimizations that are being tested are enabled) - Let all other test files run with the new default settings (i.e. with new optimizations disabled) - Copy subquery testcases from these files into t/subselect_extra.test which will run them with new optimizations enabled.
-
unknown authored
Analysis: This bug consists of two related problems that are result of too early evaluation of single-row subqueries during the optimization phase of the outer query. Several optimizer code paths try to evaluate single-row subqueries in order to produce a constant and use that constant for further optimzation. When the execution of the subquery peforms destructive changes to the representation of the subquery, and these changes are not anticipated by the subsequent optimization phases of the outer query, we tipically get a crash or failed assert. Specifically, in this bug the inner-most suqbuery with DISTINCT triggers a substitution of the original JOIN object by a single-table JOIN object with a temp table needed to perform the DISTINCT operation (created by JOIN::make_simple_join). This substitution breaks EXPLAIN because: a) in the first example JOIN::cleanup no longer can reach the original table of the innermost subquery, and close all indexes, and b) in this second test query, EXPLAIN attempts to print the name of the internal temp table, and crashes because the temp table has no name (NULL pointer instead). Solution: a) fully disable subquery evaluation during optimization in all cases - both for constant propagation and range optimization, and b) change JOIN::join_free() to perform cleanup irrespective of EXPLAIN or not.
-
- 02 Jun, 2011 1 commit
-
-
Sergey Petrunya authored
to be optimizer_switch flags mrr=on|off and mrr_cost_based=on|off.
-
- 26 May, 2011 1 commit
-
-
Sergey Petrunya authored
-
- 23 May, 2011 1 commit
-
-
unknown authored
mysql-test/r/subselect4.result: Moved test case for LP BUG#718593 into the correct test file subselect_mat_cost_bugs.test. mysql-test/t/subselect4.test: Moved test case for LP BUG#718593 into the correct test file subselect_mat_cost_bugs.test.
-
- 16 May, 2011 1 commit
-
-
unknown authored
Analysis: The subquery is evaluated first during ref-optimization of the outer query because the subquery is considered constant from the perspective of the outer query. Thus an attempt is made to evaluate the MAX subquery and use the new constant to drive an index nested loops join. During this evaluation the inner-most subquery replaces the JOIN_TAB with a new one that fetches the data from a temp table. The function select_describe crashes at the lines: TABLE_LIST *real_table= table->pos_in_table_list; item_list.push_back(new Item_string(real_table->alias, strlen(real_table->alias), cs)); because 'table' is a temp table, and it has no corresponding table reference. This 'real_table' is NULL, and real_table->alias results in a crash. Solution: In the spirit of MWL#89 prevent the evaluation of expensive predicates during optimization. This patch prevents the evaluation of expensive predicates during ref optimization. sql/item_subselect.h: Remove unused class member. Not needed for the fix, but noticed now and removed.
-
- 13 May, 2011 1 commit
-
-
unknown authored
Analysis: During optimization of the subquery, in the call chain: update_ref_and_keys -> add_key_fields -> merge_key_fields -> Item_direct_ref::is_null -> Item_cache::is_null The call to Item_cache::is_null() returns TRUE, which is wrong. This results in Item_null replacing the field 'f3' in the KEY_FIELD, then this Item_null is used for index access, producing a wrong result. The reason why Item_cache::is_null returns wrong result is that this Item_cache object is a cache of the left operand of IN, and was updated in Item_in_optimizer::val_int. In MWL#89 the latter method is called during the execution phase, which is after we optimize the subquery. Therefore during the optization phase the left operand cache of IN was not updated. Solution: Update the left operand cache during optimization if it is a constant. This bug fix also discoveres and fixes a wrong IF statement in convert_constant_item().
-
- 05 May, 2011 1 commit
-
-
unknown authored
Analysis: The method st_select_lex::optimize_unflattened_subqueries() incorrectly propagated to each subquery the complete select_options flag set for the whole query. Among other flags in select_options, this propagated incorrectly the STRAIGHT_JOIN flag from the upper query to the subquery. Solution: During EXPLAIN set only the SELECT_DESCRIBE bit in the select_options of the subquery.
-
- 04 May, 2011 1 commit
-
-
unknown authored
Pushing test case only. The bug itself was fixed by the combined patches for LP BUG#717577 and LP BUG#776274.
-
- 28 Apr, 2011 1 commit
-
-
unknown authored
Analysis: Build_equal_items_for_cond() rewrites the WHERE clause in such a way, that it may merge the list join->cond_equal->current_level with the list of child Items in an AND condition of the WHERE clause. The place where this is done is: static COND *build_equal_items_for_cond(THD *thd, COND *cond, COND_EQUAL *inherited) { ... if (and_level) { args->concat(&eq_list); args->concat((List<Item> *)&cond_equal.current_level); } ... } As a result, later transformations on the WHERE clause may change the structure of the list join->cond_equal->current_level without knowing this. Specifically in this bug, Item_in_subselect::inject_in_to_exists_cond creates a new AND of the old WHERE clause and the IN->EXISTS conditions. It then calls fix_fields() for the new AND. Among other things, fix_fields flattens all nested ANDs into one by merging the AND argument lists. When there is a cond_equal for the JOIN, its list of Item_equal objects is attached to the end of the original AND. When a lower-level AND is merged into the top-level one, the argument list of the lower-level AND is concatenated to the list of multiple equalities in the upper-level AND. As a result, when substitute_for_best_equal_field processes the multiple equalities, it turns out that the multiple equality list contains the Items from the lower-level AND which were concatenated to the end of the join->cond_equal->current_level list. This results in a crash because this list must not contain any other Items except for the previously found Item_equal ones. Solution: When performing IN->EXIST predicate injection, and the where clause is an AND, detach the list of Item_equal objects before calling fix_fields on the injected where clause. After fix_fields is done, reattach back the multiple equalities list to the end of the argument list of the new AND.
-
- 20 Apr, 2011 1 commit
-
-
unknown authored
Analysis: The wrong result is a consquence of sorting the subquery result and then selecting only the first row due to the artificial LIMIT 1 introduced by the fix_fields phase. Normally, if there is an ORDER BY in a subquery, the ORDER is removed (Item_in_subselect::select_in_like_transformer), however if a GROUP BY is transformed into ORDER, this happens later, after the removal of the ORDER clause of subqueries, so we end up with a subquery with an ORDER clause, and an artificially added LIMIT 1. The reason why the same works in the main 5.3 without MWL#89, is that the 5.3 performs all subquery transformations, including IN->EXISTS before JOIN::optimize(). The beginning of JOIN::optimize does: if (having || (select_options & OPTION_FOUND_ROWS)) select_limit= HA_POS_ERROR; which sets the limit back to infinity, thus 5.3 sorts the whole subquery result, and IN performs the lookup into all subquery result rows. Solution: Sorting of subqueries without LIMIT is meaningless. Since LIMIT in subqueries is not supported, the patch removes sorting by setting join->skip_sort_order= true for each subquery JOIN object. This improves a number of execution plans to not perform unnecessary sorting at all.
-
- 04 Apr, 2011 1 commit
-
-
unknown authored
There is no patch because the bug cannot be reproduced any more.
-
- 28 Mar, 2011 1 commit
-
-
unknown authored
Analysis: There are two code paths through which JOIN::exec may produce an all-NULL row for an empty result set. One goes via the function return_zero_rows(), when query processing detectes early that the where clause is false, the other one is via do_select() in the case of join execution. In the case of do_select(), the problem was that the executioner didn't set TABLE::null_row to 1. As result when sending the only result row, the evaluation of each field didn't detect that all non-aggregated fields are NULL, because Field::is_null returned true, after checking that field->table->null_row was false. Given that the each non-aggregated field was not considered NULL, select_result::send_data sent whatever was in the buffer of each field. However, since there was no actual data in the field buffer, send_data() accessed and sent whatever junk was in the field's data buffer. Solution: Similar to the analogous case in return_zero_rows() mark all tables that their current row is NULL before sending the artificailly created NULL row.
-
- 24 Mar, 2011 1 commit
-
-
unknown authored
Analysis: A query with implicit grouping is one with aggregate functions and no GROUP BY clause. MariaDB inherits from MySQL an SQL extenstion that allows mixing aggregate functions with non-aggregate fields. If a query with such mixed select clause produces an empty result set, the meaning of aggregate functions is well defined - either NULL (MIN, MAX, etc.), or 0 (count(*)). However the non-aggregated fields must also have some value, and the only reasonable value in the case of empty result is NULL. The cause of the many wrong results was that if a field is declared as non-nullable (e.g. because it is a PK or NOT NULL), the semantic analysis and the optimization phases treat this field as non-nullable, and generate all related query plan elements based on this assumption. Later during execution, these incorrectly configured/generated query plan elements result in a wrong result because the selected fields are not null due to the not-null assumption during optimization. Solution: Detect before the context analysys phase that a query uses implicit grouping with mixed aggregates/non-aggregates, and set all fields as nullable. The parser already walks the SELECT clause, and already sets Item::with_sum_func for Items that reference aggreagate functions. The patch adds a symmetric Item::with_field so that all Items that reference an Item_field are marked during their construction at parse time in the same way as with aggregate function use.
-
- 03 Mar, 2011 1 commit
-
-
unknown authored
Analysis: The reason for the crash was that the inner subquery was executed via a scan on a final temporary table applied after all other operations. This final operation is implemented by changing the contents of the JOIN object of the subquery to represent a table scan over the temp table. At the same time query optimization of the outer subquery required evaluation of the inner subquery, which happened before the actual EXPLAIN. The evaluation left the JOIN object of the inner subquery in the changed state, where it represented a table scan over a temp table, and EXPLAIN crashed because the temp table is not associated with any table reference (TABLE_LIST object). The reason the JOIN was not restored was because its saving/restoration was controlled by the join->select_lex->uncacheable flag, which was not set in the case of materialization. Solution: In the methods Item_in_subselect::[single | row]_value_transformer() set: select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; In addition, for symmetry, change: master_unit->uncacheable|= UNCACHEABLE_EXPLAIN; instead of UNCACHEABLE_DEPENDENT because if a subquery was not dependent initially, the changed methods do not change this fact. The subquery may later become correlated if it is transformed to an EXISTS query, but it may stay uncorrelated if executed via materialization.
-
- 25 Feb, 2011 1 commit
-
-
Sergey Petrunya authored
- Make get_constant_key_infix() take into account that there may be SEL_TREEs with type=SEL_ARG::MAYBE_KEY, which it cannot process, because they are not real ranges but rather indications that we might have been able to construct a range if we had values for some other tables' fields. (check_quick_select() already has such check)
-
- 10 Feb, 2011 1 commit
-
-
Sergey Petrunya authored
- Assume that outside subquery references are known when doing "Range-checked-for-each-record" check.
-
- 14 Jan, 2011 1 commit
-
-
Sergey Petrunya authored
Backport testcase: BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(), file item.cc, line 4448" (The fix was backported with subquery code backport)
-
- 12 Jan, 2011 1 commit
-
-
Martin Hansson authored
tmptable needed The function DEFAULT() works by modifying the the data buffer pointers (often referred to as 'record' or 'table record') of its argument. This modification is done during name resolution (fix_fields().) Unfortunately, the same modification is done when creating a temporary table, because default values need to propagate to the new table. Fixed by skipping the pointer modification for fields that are arguments to the DEFAULT function.
-
- 16 Dec, 2010 1 commit
-
-
Martin Hansson authored
file .\item_subselect.cc, line 836 IN quantified predicates are never executed directly. They are rather wrapped inside nodes called IN Optimizers (Item_in_optimizer) which take care of the execution. However, this is not done during query preparation. Unfortunately the LIKE predicate pre-evaluates constant right-hand side arguments even during name resolution. Likely this is meant as an optimization. Fixed by not pre-evaluating LIKE arguments in view prepare mode. Back-ported to 5.0s
-
- 02 Dec, 2010 2 commits
-
-
unknown authored
Analysis: The fix for LP BUG#680846 avoids evaluation of constant expressions with subqueries in the GROUP/ORDER clauses in the procedure remove_const(). The purpose of remove_const is to remove constant expressions in the GROUP/ORDER clauses. In order delay until execution the evaluation of such subqueries, they were not removed in the GROUP/ORDER clause. As a result temp table creation during execution attempted to create a column in the temp table for each constant GROUP/ORDER expression. However, the logic in create_tmp_table is to not create temp table columns for constant items. The crash was due to a group Item without a corresponding column in the temp table for GROUP BY. Solution: The patch adds back removal of constant expressions with subqueries. In order for such expressions to be evaluated, so that the server can ensure that such subquries return 1 row, the evaluation of these expressions is delayed until execution.
-
unknown authored
Analysis: The problem lies in filesort.cc:find_all_keys(). When find_all_keys() is called for the outer query, it resets all of the tree sets of fields - [read,write,vcol]_set and recomputes them with respect to sorting. However, in the loop for each current record the procedure calls select->skip_record(thd), which evaluates the where clause, which in turns evaluates the subquery. The JOIN evaluation of the subquery eventually calls Field_long::val_int to evaluate the field alias1.f1. The assertion condition "bitmap_is_set(table->read_set, field_index)" fails, because the outer query changed the read_set of table "alias1". Solution: Restore the original read_set of the table before calling SQL_SELECT::skip_record, then revert back to the read_set used in find_all_keys.
-
- 26 Nov, 2010 1 commit
-
-
unknown authored
Analysis: JOIN::optimize performs constant optimization of GROUP by clauses by calling remove_const(): group_list= remove_const(this, (old_group_list= group_list), conds, rollup.state == ROLLUP::STATE_NONE, &simple_group); If it turns out that a GROUP clause references a field that is computed by a single-row subquery, then the said optimization performs premature execution of the subquery referenced by the group clause. Solution: Block the evaluation of subqueries similarly to the approach for the WHERE and JOIN..ON clauses.
-
- 22 Nov, 2010 3 commits
-
-
unknown authored
Analysis: Single-row subqueries are not considered expensive and are evaluated both during EXPLAIN in to detect errors like "Subquery returns more than 1 row", and during optimization to perform constant optimization. The cause for the failed ASSERT is in JOIN::join_free, where we set bool full= (!select_lex->uncacheable && !thd->lex->describe); Thus for EXPLAIN statements full == FALSE, and as a result the call to JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have called table->disable_keyread(). Solution: Consider all kinds of subquery predicates as expensive.
-
unknown authored
Analysis: This another instance of the problem fixed in LP BUG#675981 - evaluation of subqueries during EXPLAIN when the subquery plan is incomplete because JOIN::optimize() generally doesn't create complete execution plans for EXPLAIN statements. In this case the call path is: mysql_explain_union -> outer_join.exec -> outer_join.init_execution -> create_sort_index -> filesort -> find_all_keys -> SQL_SELECT::skip_record -> outer_where_clause.val_int -> ... -> subselect_join.exec -> ... -> sub_select_cache When calling sub_select_cache JOIN_TAB::cache is NULL because the cache objects are not created for EXPLAIN statements. Solution: Delay the call to init_execution() after all EXPLAIN related processing is completed. Thus init_execution() is not called at all during EXPLAIN.
-
unknown authored
Cause: The optimize() phase for the subquery selected to use join buffering via setting JOIN_TAB::next_select= sub_select_cache in make_join_readinfo, however, the call to check_join_cache_usage() from make_join_readinfo didn't create the corresponding JOIN_CACHE_BNL object because of the condition: if ((options & SELECT_DESCRIBE) || (((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) && !tab->cache->init())) Since EXPLAIN for subqueries runs regular execution, the constant predicates that were delayed to be evaluated at the exec() phase, were evaluated during EXPLAIN. As a result the outer JOIN::exec called JOIN::exec for the subquery, while the subquery execution plan was no properly created, which resulted in an failed ASSERT. Fix: The patch blocks evaluation of constant expensive conditions during EXPLAIN. Notice that these conditions are "constant" with respect to the outer query, thus in general they could be arbitrarily expensive, which may result in very slow EXPLAINs.
-
- 19 Nov, 2010 2 commits
-
-
unknown authored
This is a backport of the fix for MySQL BUG#52317: Assertion failing in Field_varstring::store () at field.cc:6833 The orginal comment by Oystein is: In order for EXPLAIN to print const-refs, a Store_key_const_item object is created. This is different for normal execution of subqueries where a temporary store_key_item object is used instead. The problem is that EXPLAIN will execute subqueries. This leads to a scenario where a store_key_const_item object it told to write to its underlying field. This results in a failing assert since the write set of the underlying table does not reflect this. The resolution is to do the same trick as for store_key_item::copy_inner(). That is, temporarily change the write set to allow writes to all columns. This is only necessary in debug version since non-debug version does not contain asserts on write_set. sql/sql_select.h: Temporarily change write_set in store_key_const_item::copy_inner() to allow initialization of underlying field. This is necessary since subqueries are executed for EXPLAIN. (For normal execution, store_key_item::copy_inner is used.)
-
unknown authored
The cause for the bug was two-fold: 1. Incorrect detection of whether a table is the first one in a query plan - "used_table & 1" actually checks if used_table is table with number "1". 2. Missing logic to delay the evaluation of (expensive) constant conditions during the execution phase. The fix adds/changes: The patch: - removes incorrect treatment of expensive predicates from make_cond_for_table, and lets the caller decide when to evaluate expensive predicates. - saves expensive constant conditions in JOIN::exec_const_cond, which is evaluated once in the beginning of JOIN::exec.
-
- 07 Sep, 2010 1 commit
-
-
Martin Hansson authored
The EXISTS transformation has additional switches to catch the known corner cases that appear when transforming an IN predicate into EXISTS. Guarded conditions are used which are deactivated when a NULL value is seen in the outer expression's row. When the inner query block supplies NULL values, however, they are filtered out because no distinction is made between the guarded conditions; guarded NOT x IS NULL conditions in the HAVING clause that filter out NULL values cannot be de-activated in isolation from those that match values or from the outer expression or NULL's. The above problem is handled by making the guarded conditions remember whether they have rejected a NULL value or not, and index access methods are taking this into account as well. The bug consisted of 1) Not resetting the property for every nested loop iteration on the inner query's result. 2) Not propagating the NULL result properly from inner query to IN optimizer. 3) A hack that may or may not have been needed at some point. According to a comment it was aimed to fix #2 by returning NULL when FALSE was actually the result. This caused failures when #2 was properly fixed. The hack is now removed. The fix resolves all three points.
-
- 23 Aug, 2010 1 commit
-
-
Michael Widenius authored
Fix for LP#612894 Some aggregate functions (such as MIN MAX) work incorrectly in subqueries after getting NULL value mysql-test/r/group_by.result: Added test that showed problems that no_rows_in_results() didn't work for expressions mysql-test/r/subselect4.result: Test case for LP#612894 mysql-test/t/group_by.test: Added test that showed problems that no_rows_in_results() didn't work for expressions mysql-test/t/subselect4.test: Test case for LP#612894 sql/item.h: Added restore_to_before_no_rows_in_result() Added function processor for no_rows_in_results() and restore_to_before_no_rows_in_results() to ensure it works with functions Fix that above functions are handled by Item_ref() sql/item_func.h: Ensure that no_rows_in_results() and restore_to_before_no_rows_in_result() are called for all function arguments sql/item_sum.cc: Added restore_to_before_no_rows_in_result() to restore settings after Item_sum_hybrid::no_rows_in_result() was called. This is needed to handle the case where we have made 'make_const()' on the item in opt_sum(), but the item will be reused again in a sub query. Ignore multiple calls to no_rows_in_result() as Item_ref is calling it twice. sql/item_sum.h: Added restore_to_before_no_rows_in_result(); sql/sql_select.cc: Added reset of no_rows_in_result() for JOIN::reinit() sql/sql_select.h: Added marker if no_rows_in_result() is called.
-
- 05 Aug, 2010 1 commit
-
-
Martin Hansson authored
file .\item_subselect.cc, line 836 IN quantified predicates are never executed directly. They are rather wrapped inside nodes called IN Optimizers (Item_in_optimizer) which take care of the execution. However, this is not done during query preparation. Unfortunately the LIKE predicate pre-evaluates constant right-hand side arguments even during name resolution. Likely this is meant as an optimization. Fixed by not pre-evaluating LIKE arguments in view prepare mode.
-