An error occurred fetching the project authors.
- 28 Aug, 2012 1 commit
-
-
Sergey Petrunya authored
- Don't do early cleanup of uncorrelated subqueries if we're running an EXPLAIN.
-
- 21 Aug, 2012 1 commit
-
-
unknown authored
The fix backports from MWL#182: Explain running statements the logic that saves the original JOIN_TAB array of a query plan after optimization. This array is later used during EXPLAIN to iterate over the original JOIN plan nodes in the cases when this plan could be changed by early subquery execution during the optimization phase of the outer query.
-
- 25 Jul, 2012 1 commit
-
-
Sergey Petrunya authored
- Make Item_subselect::fix_fields() ignore UNCACHEABLE_EXPLAIN flag when deciding whether the subquery item should be marked as constant.
-
- 15 Jun, 2012 1 commit
-
-
unknown authored
Analysis: The fix for bug lp:985667 implements the method Item_subselect::no_rows_in_result() for all main kinds of subqueries. The purpose of this method is to be called from return_zero_rows() and set Items to some default value in the case when a query returns no rows. Aggregates and subqueries require special treatment in this case. Every implementation of Item_subselect::no_rows_in_result() called Item_subselect::make_const() to set the subquery predicate to its default value irrespective of where the predicate was located in the query. Once the predicate was set to a constant it was never executed. At the same time, the JOIN object of the fake select for UNIONs (the one used for the final result of the UNION), was set after all subqueries in the union were executed. Since we set the subquery as constant, it was never executed, and the corresponding JOIN was never created. In order to decide whether the result of NOT IN is NULL or FALSE, Item_in_optimizer needs to check if the subquery result was empty or not. This is where we got the crash, because subselect_union_engine::no_rows() checks for unit->fake_select_lex->join->send_records, and the join object was NULL. Solution: If a subquery is in the HAVING clause it must be evaluated in order to know its result, so that we can properly filter the result records. Once subqueries in the HAVING clause are executed even in the case of no result rows, this specific crash will be solved, because the UNION will be executed, and its JOIN will be constructed. Therefore the fix for this crash is to narrow the fix for lp:985667, and to apply Item_subselect::no_rows_in_result() only when the subquery predicate is in the SELECT clause.
-
- 05 Jun, 2012 1 commit
-
-
unknown authored
Analysis: When the method JOIN::choose_subquery_plan() decided to apply the IN-TO-EXISTS strategy, it set the unit and select_lex uncacheable flag to UNCACHEABLE_DEPENDENT_INJECTED unconditionally. As result, even if IN-TO-EXISTS injected non-correlated predicates, the subquery was still treated as correlated. Solution: Set the subquery as correlated only if the injected predicate(s) depend on the outer query.
-
- 01 Jun, 2012 1 commit
-
-
unknown authored
CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin Analysis: The memory leak was a result of the interaction of semi-join optimization with early optimization of constant subqueries. The function: setup_jtbm_semi_joins() created a dummy temporary table "dummy_table" in order to make some JOIN_TAB objects complete. Normally, such temporary tables are freed inside JOIN_TAB::cleanup. However, the inner-most subquery is pre-optimized, which allows the optimization fo the MAX subquery to determine that its WHERE is TRUE, and thus to compute the result of the MAX during optimization. This ultimately allows the optimize phase of the outer query to find that it WHERE clause is FALSE. Once JOIN::optimize finds that the result set is empty, it sets zero_result_cause, and returns *before* it ever reached make_join_statistics(). As a result the query plan has no JOIN_TABs at all. Since the temporary table is supposed to be cleanup via JOIN_TAB::cleanup, this never happens because there is no JOIN_TAB for this table. Hence we get a memory leak. Solution: Whenever there are no JOIN_TABs, iterate over all table reference in JOIN::join_list, and free the ones that contain semi-join temporary tables.
-
- 29 May, 2012 1 commit
-
-
unknown authored
Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer Analysis: The fix for lp:944706 introduces early subquery optimization. While a subquery is being optimized some of its predicates may be removed. In the test case, the EXISTS subquery is constant, and is evaluated to TRUE. As a result the whole OR is TRUE, and thus the correlated condition "b = alias1.b" is optimized away. The subquery becomes non-correlated. The subquery cache is designed to work only for correlated subqueries. If constant subquery optimization is disallowed, then the constant subquery is not evaluated, the subquery remains correlated, and its execution is cached. As a result execution is fast. However, when the constant subquery was optimized away, it was neither cached by the subquery cache, nor it was cached by the internal subquery caching. The latter was due to the fact that the subquery still appeared as correlated to the subselect_XYZ_engine::exec methods, and they re-executed the subquery on each call to Item_subselect::exec. Solution: The solution is to update the correlated status of the subquery after it has been optimized. This status consists of: - st_select_lex::is_correlated - Item_subselect::is_correlated - SELECT_LEX::uncacheable - SELECT_LEX_UNIT::uncacheable The status is updated by st_select_lex::update_correlated_cache(), and its caller st_select_lex::optimize_unflattened_subqueries. The solution relies on the fact that the optimizer already called st_select_lex::update_used_tables() for each subquery. This allows to efficiently update the correlated status of each subquery without walking the whole subquery tree. Notice that his patch is an improvement over MySQL 5.6 and older, where subqueries are not pre-optimized, and the above analysis is not possible.
-
- 23 May, 2012 1 commit
-
-
Sergey Petrunya authored
- Disable IndexConditionPushdown for reverse scans.
-
- 22 May, 2012 1 commit
-
-
unknown authored
Analysis: The optimizer detects an empty result through constant table optimization. Then it calls return_zero_rows(), which in turns calls inderctly Item_maxmin_subselect::no_rows_in_result(). The latter method set "value=0", however "value" is pointer to Item_cache, and not just an integer value. All of the Item_[maxmin | singlerow]_subselect::val_XXX methods does: if (forced_const) return value->val_real(); which of course crashes when value is a NULL pointer. Solution: When the optimizer discovers an empty result set, set Item_singlerow_subselect::value to a FALSE constant Item instead of NULL.
-
- 18 May, 2012 1 commit
-
-
unknown authored
The cause for this bug is that the method JOIN::get_examined_rows iterates over all JOIN_TABs of the join assuming they are just a sequence. In the query above, the innermost subquery is merged into its parent query. When we call JOIN::get_examined_rows for the second-level subquery, the iteration that assumes sequential order of join tabs goes outside the join_tab array and calls the method JOIN_TAB::get_examined_rows on uninitialized memory. The fix is to iterate over JOIN_TABs in a way that takes into account the nested semi-join structure of JOIN_TABs. In particular iterate as select_describe.
-
- 17 May, 2012 1 commit
-
-
unknown authored
The patch enables back constant subquery execution during query optimization after it was disabled during the development of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION). The main idea is that constant subqueries are allowed to be executed during optimization if their execution is not expensive. The approach is as follows: - Constant subqueries are recursively optimized in the beginning of JOIN::optimize of the outer query. This is done by the new method JOIN::optimize_constant_subqueries(). This is done so that the cost of executing these queries can be estimated. - Optimization of the outer query proceeds normally. During this phase the optimizer may request execution of non-expensive constant subqueries. Each place where the optimizer may potentially execute an expensive expression is guarded with the predicate Item::is_expensive(). - The implementation of Item_subselect::is_expensive has been extended to use the number of examined rows (estimated by the optimizer) as a way to determine whether the subquery is expensive or not. - The new system variable "expensive_subquery_limit" controls how many examined rows are considered to be not expensive. The default is 100. In addition, multiple changes were needed to make this solution work in the light of the changes made by MWL#89. These changes were needed to fix various crashes and wrong results, and legacy bugs discovered during development.
-
- 27 Apr, 2012 1 commit
-
-
unknown authored
Analysis: The reason for the wrong result is the interaction between constant optimization (in this case 1-row table) and subquery optimization. - First the outer query is optimized, and 'make_join_statistics' finds that table t2 has one row, reads that row, and marks the whole table as constant. This also means that all fields of t2 are constant. - Next, we optimize the subquery in the end of the outer 'make_join_statistics'. The field 'f2' is considered constant, with value '3'. The subquery predicate is rewritten as the constant TRUE. - The outer query execution detects early that the whole query result is empty and calls 'return_zero_rows'. Since the query is with implicit grouping, we have to produce one row with special values for the aggregates (depending on each aggregate function), and NULL values for all non-aggregate fields. This function calls 'no_rows_in_result' to set each aggregate function to the default value when it aggregates over an empty result, and then calls 'send_data', which in turn evaluates each Item in the SELECT list. - When evaluation reaches the subquery predicate, it executes the subquery with field 'f2' having a constant value '3', and the subquery produces the incorrect result '7'. Solution: Implement Item::no_rows_in_result for all subquery predicates. In order to make this work, it is also needed to make all val_* methods of all subquery predicates respect the Item_subselect::forced_const flag. Otherwise subqueries are executed anyways, and override the default value set by no_rows_in_result with whatever result is produced from the subquery evaluation.
-
- 05 Mar, 2012 1 commit
-
-
unknown authored
Problem is that subquery execution can't be called during prepare/optimize phase. Also small fix for subquery test suite.
-
- 09 Feb, 2012 1 commit
-
-
unknown authored
The bug itself is fixed by the patch for bug lp:908269.
-
- 10 Jan, 2012 1 commit
-
-
unknown authored
Fix for LP BUG#908269 Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table. Problem: When building the condition for JOIN::outer_ref_cond the optimizer forgot to take into account that this condition could depend on constant tables as well.
-
- 24 Dec, 2011 1 commit
-
-
Igor Babaev authored
fixed several defects in the greedy optimization: 1) The greedy optimizer calculated the 'compare-cost' (CPU-cost) for iterating over the partial plan result at each level in the query plan as 'record_count / (double) TIME_FOR_COMPARE' This cost was only used locally for 'best' calculation at each level, and *not* accumulated into the total cost for the query plan. This fix added the 'CPU-cost' of processing 'current_record_count' records at each level to 'current_read_time' *before* it is used as 'accumulated cost' argument to recursive best_extension_by_limited_search() calls. This ensured that the cost of a huge join-fanout early in the QEP was correctly reflected in the cost of the final QEP. To get identical cost for a 'best' optimized query and a straight_join with the same join order, the same change was also applied to optimize_straight_join() and get_partial_join_cost() 2) Furthermore to get equal cost for 'best' optimized query and a straight_join the new code substrcated the same '0.001' in optimize_straight_join() as it had been already done in best_extension_by_limited_search() 3) When best_extension_by_limited_search() aggregated the 'best' plan a plan was 'best' by the check : 'if ((search_depth == 1) || (current_read_time < join->best_read))' The term '(search_depth == 1' incorrectly caused a new best plan to be collected whenever the specified 'search_depth' was reached - even if this partial query plan was more expensive than what we had already found.
-
- 19 Dec, 2011 2 commits
-
-
unknown authored
The patch differs from the original MySQL patch as follows: - All test case differences have been reviewed one by one, and care has been taken to restore the original plan so that each test case executes the code path it was designed for. - A bug was found and fixed in MariaDB 5.3 in Item_allany_subselect::cleanup(). - ORDER BY is not removed because we are unsure of all effects, and it would prevent enabling ORDER BY ... LIMIT subqueries. - ref_pointer_array.m_size is not adjusted because we don't do array bounds checking, and because it looks risky. Original comment by Jorgen Loland: ------------------------------------------------------------- WL#5953 - Optimize away useless subquery clauses For IN/ALL/ANY/SOME/EXISTS subqueries, the following clauses are meaningless: * ORDER BY (since we don't support LIMIT in these subqueries) * DISTINCT * GROUP BY if there is no HAVING clause and no aggregate functions This WL detects and optimizes away these useless parts of the query during JOIN::prepare()
-
Igor Babaev authored
Do not perform index condition pushdown for conditions containing subqueries and stored functions.
-
- 15 Dec, 2011 2 commits
-
-
Igor Babaev authored
-
Igor Babaev authored
set to 'on' by default.
-
- 12 Dec, 2011 1 commit
-
-
Sergei Golubchik authored
* rename all debugging related command-line options and variables to start from "debug-", and made them all OFF by default. * replace "MySQL" with "MariaDB" in error messages * "Cast ... converted ... integer to it's ... complement" is now a note, not a warning * @@query_cache_strip_comments now has a session scope, not global.
-
- 04 Dec, 2011 1 commit
-
-
Sergey Petrunya authored
in EXPLAIN as select_type==MATERIALIZED. Before, we had select_type==SUBQUERY and it was difficult to tell materialized subqueries from uncorrelated scalar-context subqueries.
-
- 29 Nov, 2011 2 commits
-
-
unknown authored
-
unknown authored
The cause of the wrong result was that Item_ref_null_helper::get_date() didn't use a method of the *_result() family, and fetched the data for the field from the current row instead of result_field. Changed to use the correct *_result() method, like to all other similar methods of Item_ref_null_helper.
-
- 28 Nov, 2011 2 commits
-
-
unknown authored
Analysis: lp:894397 was a consequence of a prior incorrect fix of lp:833777 which didn't take into account that even when all tables are constant there may be correlated conditions, and the where clause is not equivalent to the constant conditions. Solution: When there are constant tables only, evaluate only the conditions that reference outer fields, because the constant conditions are already checked, and the where clause doesn't have other conditions than constant ones, and outer referencing ones. The fix for lp:894397 also fixes lp:833777.
-
unknown authored
The problem was that when we have single row subquery with no rows Item_cache(es) which represent result row was not null and being requested via element_index() returned random value. The fix is setting all Item_cache(es) in NULL before executing the query (reset() method) which guaranty NULL value of whole query or its elements requested in any way if no rows was found. set_null() method was added to Item_cache to guaranty correct NULL value in case of reseting the cache.
-
- 26 Nov, 2011 1 commit
-
-
Igor Babaev authored
and 'derived_with_keys'. Now they are set on by default.
-
- 24 Nov, 2011 1 commit
-
-
unknown authored
Stop attempts to apply IN/ALL/ANY optimizations to so called "fake_select" (used for ordering and filtering results of union) in union subquery execution.
-
- 21 Nov, 2011 2 commits
-
-
unknown authored
the depth of subquery nestedness to less than 31 (sizeof(ulong)-1).
-
unknown authored
Analysis: The optimizer distinguishes two kinds of 'constant' conditions: expensive ones, and non-expensive ones. The non-expensive conditions are evaluated inside make_join_select(), and if false, already the optimizer detects empty query results. In order to avoid arbitrarily expensive optimization, the evaluation of expensive constant conditions is delayed until execution. These conditions are attached to JOIN::exec_const_cond and evaluated in the beginning of JOIN::exec. The relevant execution logic is: JOIN::exec() { if (! join->exec_const_cond->val_int()) { produce an empty result; stop execution } continue execution execute the original WHERE clause (that contains exec_const_cond) ... } As a result, when an expensive constant condition is TRUE, it is evaluated twice - once through JOIN::exec_const_cond, and once through JOIN::cond. When the expensive constant condition is a subquery, predicate, the subquery is evaluated twice. If we have many levels of subqueries, this logic results in a chain of recursive subquery executions that walk a perfect binary tree. The result is that for subquries with depth N, JOIN::exec is executed O(2^N) times. Solution: Notice that the second execution of the constant conditions happens inside do_select(), in the branch: if (join->table_count == join->const_tables) { ... } In this case exec_const_cond is equivalent to the whole WHERE clause, therefore the WHERE clause has already been checked in the beginnig of JOIN::exec, and has been found to be true. The bug is addressed by not evaluating the WHERE clause if there was exec_const_conds, and it was TRUE.
-
- 15 Nov, 2011 1 commit
-
-
Igor Babaev authored
If the optimizer switch 'semijoin_with_cache' is set to 'off' then join cache cannot be used to join inner tables of a semijoin. Also fixed a bug in the function check_join_cache_usage() that led to wrong output of the EXPLAIN commands for some test cases.
-
- 12 Nov, 2011 1 commit
-
-
unknown authored
In MariaDB, when running in ONLY_FULL_GROUP_BY mode, the server produced in incorrect error message that there is an aggregate function without GROUP BY, for artificially created MIN/MAX functions during subquery MIN/MAX optimization. The fix introduces a way to distinguish between artifially created MIN/MAX functions as a result of a rewrite, and normal ones present in the query. The test for ONLY_FULL_GROUP_BY violation now tests in addition if a MIN/MAX function was part of a MIN/MAX subquery rewrite. In order to be able to distinguish these MIN/MAX functions, the patch introduces an additional flag in Item_in_subselect::in_strategy - SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its final choice of a subuqery strategy. In order to make the choice consistent, access to Item_in_subselect::in_strategy is provided via new class methods. ****** Fix MySQL BUG#12329653 In MariaDB, when running in ONLY_FULL_GROUP_BY mode, the server produced in incorrect error message that there is an aggregate function without GROUP BY, for artificially created MIN/MAX functions during subquery MIN/MAX optimization. The fix introduces a way to distinguish between artifially created MIN/MAX functions as a result of a rewrite, and normal ones present in the query. The test for ONLY_FULL_GROUP_BY violation now tests in addition if a MIN/MAX function was part of a MIN/MAX subquery rewrite. In order to be able to distinguish these MIN/MAX functions, the patch introduces an additional flag in Item_in_subselect::in_strategy - SUBS_STRATEGY_CHOSEN. This flag is set when the optimizer makes its final choice of a subuqery strategy. In order to make the choice consistent, access to Item_in_subselect::in_strategy is provided via new class methods.
-
- 09 Nov, 2011 1 commit
-
-
unknown authored
-
- 01 Nov, 2011 2 commits
-
-
unknown authored
Analysis: Equality propagation propagated the constant '7' into args[0] of the Item_in_optimizer that stands for the "< ANY" predicate. At the same the min/max subquery rewrite swapped the order of the left and right operands of the "<" predicate, but used Item_in_subselect::left_expr. As a result, when the <ANY predicate is executed early in the execution phase as a contant condition, instead of a constant right (swapped) argument of the < predicate, there was a field (t3.a). This field had no data, since the whole predicate is considered constant, and it is evaluated before any tables are read. Having junk in the field row buffer produced wrong result Solution: Fix create_swap to pick the correct Item_in_optimizer left argument.
-
Igor Babaev authored
Fixed a bug in select_describe. Adjusted results for affected test cases.
-
- 13 Oct, 2011 1 commit
-
-
Sergei Golubchik authored
restore the status quo from before the microsecond patch
-
- 05 Oct, 2011 1 commit
-
-
unknown authored
-
- 26 Aug, 2011 1 commit
-
-
unknown authored
Analysis: Constant table optimization of the outer query finds that the right side of the equality is a constant that can be used for an eq_ref access to fetch one row from t1, and substitute t1 with a constant. Thus constant optimization triggers evaluation of the subquery during the optimize phase of the outer query. The innermost subquery requires a plan with a temporary table because with InnoDB tables the exact count of rows is not known, and the empty tables cannot be optimzied way. JOIN::exec for the innermost subquery substitutes the subquery tables with a temporary table. When EXPLAIN gets to print the tables in the innermost subquery, EXPLAIN needs to print the name of each table through the corresponding TABLE_LIST object. However, the temporary table created during execution doesn't have a corresponding TABLE_LIST, so we get a null pointer exception. Solution: The solution is to forbid using expensive constant expressions for eq_ref access for contant table optimization. Notice that eq_ref with a subquery providing the value is still possible during regular execution.
-
- 20 Aug, 2011 1 commit
-
-
Igor Babaev authored
When the WHERE/HAVING condition of a subquery has been transformed by the optimizer the pointer stored the 'where'/'having' field of the SELECT_LEX structure used for the subquery must be updated accordingly. Otherwise the pointer may refer to an invalid item. This can lead to the reported assertion failure for some queries with correlated subqueries
-
- 17 Aug, 2011 1 commit
-
-
unknown authored
The bug is a duplicate of MySQL's Bug#11764086, however MySQL's fix is incomplete for MariaDB, so this fix is slightly different. In addition, this patch renames Item_func_not_all::top_level() to is_top_level_item() to make it in line with the analogous methods of Item_in_optimizer, and Item_subselect. Analysis: It is possible to determine whether a predicate is NULL-rejecting only if it is a top-level one. However, this was not taken into account for Item_in_optimizer. As a result, a NOT IN predicate was erroneously considered as NULL-rejecting, and the NULL-complemented rows generated by the outer join were rejected before being checked by the NOT IN predicate. Solution: Change Item_in_optimizer to be considered as NULL-rejecting only if it a top-level predicate.
-