- developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 30 Mar '10

30 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 20:02)=-=- Low Level Design modified. --- /tmp/wklog.90.old.27113 2010-03-29 20:02:22.000000000 +0000 +++ /tmp/wklog.90.new.27113 2010-03-29 20:02:22.000000000 +0000 @@ -2,16 +2,15 @@ 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST -2.2 On subquery predicate removal -2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away -2.3 What is expected of the result of conversion +2.1.1 On subquery predicate removal +2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 Conversion process and its result 3. Pre-optimization steps -3.1 Constant detection -3.3 update_ref_and_keys -3.4 JOIN_TAB sorting criteria -4. Optimization +3.1 update_ref_and_keys +3.2 Constant detection +4. Join optimization 5. Execution -User interface. +6. User interface </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of @@ -19,48 +18,60 @@ 1. Applicability check ====================== -The criteria for checking whether a subquery can be processed with -jtbm-materialization can be checked at JOIN::prepare stage (like it -happens with semi-join check) +Applicability criteria of jtbm-materialization can be checked at +JOIN::prepare() stage (same as with with semi-join applicability checks) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ -Make it work like semi-join nests: each jtbm-predicate is converted into a -TABLE_LIST object. This will make it +Make it work like semi-join nests: a jtbm-predicate is converted into a +TABLE_LIST object that is injected into select's join tree. This will make it - - uniform with semi-joins (we've stepped on all rakes there) - - allow to process JTBM-subqueries in ON expressions + - uniform with semi-join processing (and we've stepped on all rakes there) + - allow to process JTBM-subqueries in LEFT JOINs' ON expressions + (as TABLE_LIST hierarchy is what is used to encode outer joins) simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. -for EXPLAIN EXTENDED, it would be natural to print something semi-join like, -i.e. for +EXPLAIN EXTENDED will display the conversion result as join, that is, for +subquery like SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) -we'll print +it will print - SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + SELECT ... FROM ot, <materialize>(SELECT ie FROM materialized-non-sj-select) -the XX part is not clear. we don't want to print 'ie' the second time here? +After we've added a TABLE_LIST element, we'll need to remove the subquery +predicate from the WHERE/On clause, in the same way as SJ-converter does it. -2.2 On subquery predicate removal ---------------------------------- -Q: if we remove the subquery predicate permanently, who will run -fix_fields() for it? For semi-joins we don't have the problem as we -inject into ON expression (right? or not? we have sj_on_expr, too... -(Investigation: we the the same Item* pointer both in WHERE and -as sj_on_expr. fix_fields() is called for the WHERE part and that's -how sj_on_expr gets fixed. This works as long as -Item_func_eq::fix_fields() does not try to substitute itself with -another item). +TODO: note: we inject a new kind of base table and then next PS re-execution +will re-run name resolution/etc on TABLE_LIST tree that includes this table. +We'll need to make sure that materialized-table doesn't interfere with +anything. + +TODO: Separation of steps that need to be done once and steps that need to be +re-done for every PS execution. + +2.1.1 On subquery predicate removal +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Q: if we remove the subquery predicate permanently, who will call +fix_fields() for the oe=ie IN-equality? For semi-joins we don't have this +problem as we inject into ON expression + +(right? or not? we have sj_on_expr, too... + (Investigation: we the the same Item* pointer both in WHERE and + as sj_on_expr. fix_fields() is called for the WHERE part and that's + how sj_on_expr gets fixed. This works as long as + Item_func_eq::fix_fields() does not try to substitute itself with + another item) +) A: ? -2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable @@ -70,71 +81,96 @@ Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. -2.3 What is expected of the result of conversion ------------------------------------------------- -Join [pre]optimization relies on each optimized entity to have a bit in -table_map. - -TODO: where do we check if there will be enough bits for everyone? (at the - point where we assign them?) +Note: if we convert that late, we could remove the subquery predicate in the +same way as remove_eq_conds() removes known-to-be-false AND-parts. +This operation is undoable. + +2.3 Conversion process and its result +------------------------------------- +At the moment, the following is being implemented: + +* convert_join_subqueries_to_semijoins() handles both conversions to + semi-joins (with convert_subq_to_sj()) and to JTBM nests (with + convert_subq_to_jtbm()). + - This means the process is done in bottom-up way (we walk down into + semi-join subselects but not jtbm-selects), with protection against + exhaustion of table bits beyond #MAX_TABLES, etc. + +* convert_subq_to_jtbm() invokes + + subq_predicate->setup_engine() + + which creates the temporary table, and then we use it as the TABLE* object + that we put into TABLE_LIST::table, JOIN_TAB::table, etc. + One of the first actions is that we assign it a bit and store it in + table->map. + (Note that this won't work when we add a choice between Materialization and + IN->EXISTS) -The bit stored in join_tab->table->map, and the apparent problem is that JTBM -join_tabs do not naturally have TABLE* object. + (Another option is do like done in FROM optimization: create a 'preliminary' + TABLE* object which has only some of the fields filled in). -We could use the the one that will be used for Materialization, but that will -stop working when we will have to include IN->EXISTS in the choice. - -Current approach: don't create a table. create a table_map element in JOIN_TAB -instead. Evgen has probably done something like that already. +QQ: Do we create/inject IN-equality? why not.. it will be used everywhere + where needed... 3. Pre-optimization steps ========================= -JOIN_TABs are allocated in make_join_statistics(). This where the changes will -be needed: for JOIN_TABs that correspond to JTBM-tables: - -- don't set tab->table, set tab->jtbm_select (or whatever) +JOIN_TABs are allocated in make_join_statistics(). For JTBM join_tabs we'll +need: - run subquery's optimizer to get its output cardinality +- todo what else? + +3.1 update_ref_and_keys +----------------------- +Since we've injected the equality into WHERE/ON and created a TABLE* with +appropriate index no special steps are necessary. The regular procedure will +add the needed KEYUSE elements. -3.1 Constant detection +3.2 Constant detection ---------------------- -What about subqueries that "are constant"? - const_item IN (SELECT uncorrelated) -> is constant, but not something - we would want to evaluate. - something IN (SELECT from_constant_join) -> is constant +* We should not let const table detection code to look at the materialized + table, find it to be empty and mark it as constant. -Do we need to mark their JOIN_TABs as constant? +* We also should disallow eq_ref(const)-based detection for materialized table + That is, if outer_expr is found to be constant, the code should not attempt + to make a eq_ref(const) lookup in the materialized table. -3.3 update_ref_and_keys ------------------------ -* Walk through JTBM elements and inject KEYUSE elements for their - IN-equalities. +TODO { + Second: What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate at optimization phase. + + something IN (SELECT from_constant_join) -> is constant + Do we need to mark their JOIN_TABs as constant?) +} -TODO: KEYUSE elements imply presense of KEYs! Which we don't have! +4. Join optimization +==================== +Should just work, with exception that we need to make best_access_path not +to use table->stats.records but use JOIN_TAB::records instead (which already +exists and is filled) -3.4 JOIN_TAB sorting criteria ------------------------------ -Q: Where do we put JTBM's join_tab when pre-sorting records? -A: it should sort as regular table. - -TODO: where do we remove the predicates from the WHERE? - - remove them like SJ-converter does - - remove them with optimizer (like remove_eq_conds does) - -4. Optimization -=============== -Add a branch in best_access_path to account for -- JTBM-Materialization -- JTBM-Materialization-Scan. +NOTE: FROM-optimization does set table->stats.records. Whose approach is + wrong? 5. Execution ============ -* We should be able to reuse item_subselect.cc code for lookups -* But will have to use our own temptable scan code +We need that: +1. Materialization operation (i.e. execute the subquery and fill the + materialized temptable) is done at start of execution +2. Materialized table is accessed during execution (either by doing lookups or + full table scan) +3. IN-equality is checked at the right moment. + +#2 is achieved by putting the materialized table into join_tab->table. +#3 is achieved by injecting IN-equality into the WHERE. -TODO: is it possible to have any unification with SJ-Materialization? +for #1, we need to place a call that fills the temptable at the right location. -User interface --------------- -Any @@optimizer_switch flags for all this? +6. User interface +================= +No @@optimizer_switch flags are planned at the moment. JTBM-materialization +will be controlled by existing materialization=on|off switch. +We could also add a generic materialization_scan=on|off switch. -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. ------------------------------------------------------------ -=-=(View All Progress Notes, 12 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.1.1 On subquery predicate removal 2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 Conversion process and its result 3. Pre-optimization steps 3.1 update_ref_and_keys 3.2 Constant detection 4. Join optimization 5. Execution 6. User interface </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== Applicability criteria of jtbm-materialization can be checked at JOIN::prepare() stage (same as with with semi-join applicability checks) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: a jtbm-predicate is converted into a TABLE_LIST object that is injected into select's join tree. This will make it - uniform with semi-join processing (and we've stepped on all rakes there) - allow to process JTBM-subqueries in LEFT JOINs' ON expressions (as TABLE_LIST hierarchy is what is used to encode outer joins) simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. EXPLAIN EXTENDED will display the conversion result as join, that is, for subquery like SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) it will print SELECT ... FROM ot, <materialize>(SELECT ie FROM materialized-non-sj-select) After we've added a TABLE_LIST element, we'll need to remove the subquery predicate from the WHERE/On clause, in the same way as SJ-converter does it. TODO: note: we inject a new kind of base table and then next PS re-execution will re-run name resolution/etc on TABLE_LIST tree that includes this table. We'll need to make sure that materialized-table doesn't interfere with anything. TODO: Separation of steps that need to be done once and steps that need to be re-done for every PS execution. 2.1.1 On subquery predicate removal ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Q: if we remove the subquery predicate permanently, who will call fix_fields() for the oe=ie IN-equality? For semi-joins we don't have this problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item) ) A: ? 2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. Note: if we convert that late, we could remove the subquery predicate in the same way as remove_eq_conds() removes known-to-be-false AND-parts. This operation is undoable. 2.3 Conversion process and its result ------------------------------------- At the moment, the following is being implemented: * convert_join_subqueries_to_semijoins() handles both conversions to semi-joins (with convert_subq_to_sj()) and to JTBM nests (with convert_subq_to_jtbm()). - This means the process is done in bottom-up way (we walk down into semi-join subselects but not jtbm-selects), with protection against exhaustion of table bits beyond #MAX_TABLES, etc. * convert_subq_to_jtbm() invokes subq_predicate->setup_engine() which creates the temporary table, and then we use it as the TABLE* object that we put into TABLE_LIST::table, JOIN_TAB::table, etc. One of the first actions is that we assign it a bit and store it in table->map. (Note that this won't work when we add a choice between Materialization and IN->EXISTS) (Another option is do like done in FROM optimization: create a 'preliminary' TABLE* object which has only some of the fields filled in). QQ: Do we create/inject IN-equality? why not.. it will be used everywhere where needed... 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). For JTBM join_tabs we'll need: - run subquery's optimizer to get its output cardinality - todo what else? 3.1 update_ref_and_keys ----------------------- Since we've injected the equality into WHERE/ON and created a TABLE* with appropriate index no special steps are necessary. The regular procedure will add the needed KEYUSE elements. 3.2 Constant detection ---------------------- * We should not let const table detection code to look at the materialized table, find it to be empty and mark it as constant. * We also should disallow eq_ref(const)-based detection for materialized table That is, if outer_expr is found to be constant, the code should not attempt to make a eq_ref(const) lookup in the materialized table. TODO { Second: What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate at optimization phase. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant?) } 4. Join optimization ==================== Should just work, with exception that we need to make best_access_path not to use table->stats.records but use JOIN_TAB::records instead (which already exists and is filled) NOTE: FROM-optimization does set table->stats.records. Whose approach is wrong? 5. Execution ============ We need that: 1. Materialization operation (i.e. execute the subquery and fill the materialized temptable) is done at start of execution 2. Materialized table is accessed during execution (either by doing lookups or full table scan) 3. IN-equality is checked at the right moment. #2 is achieved by putting the materialized table into join_tab->table. #3 is achieved by injecting IN-equality into the WHERE. for #1, we need to place a call that fills the temptable at the right location. 6. User interface ================= No @@optimizer_switch flags are planned at the moment. JTBM-materialization will be controlled by existing materialization=on|off switch. We could also add a generic materialization_scan=on|off switch. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 30 Mar '10

30 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 20:02)=-=- Low Level Design modified. --- /tmp/wklog.90.old.27113 2010-03-29 20:02:22.000000000 +0000 +++ /tmp/wklog.90.new.27113 2010-03-29 20:02:22.000000000 +0000 @@ -2,16 +2,15 @@ 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST -2.2 On subquery predicate removal -2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away -2.3 What is expected of the result of conversion +2.1.1 On subquery predicate removal +2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 Conversion process and its result 3. Pre-optimization steps -3.1 Constant detection -3.3 update_ref_and_keys -3.4 JOIN_TAB sorting criteria -4. Optimization +3.1 update_ref_and_keys +3.2 Constant detection +4. Join optimization 5. Execution -User interface. +6. User interface </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of @@ -19,48 +18,60 @@ 1. Applicability check ====================== -The criteria for checking whether a subquery can be processed with -jtbm-materialization can be checked at JOIN::prepare stage (like it -happens with semi-join check) +Applicability criteria of jtbm-materialization can be checked at +JOIN::prepare() stage (same as with with semi-join applicability checks) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ -Make it work like semi-join nests: each jtbm-predicate is converted into a -TABLE_LIST object. This will make it +Make it work like semi-join nests: a jtbm-predicate is converted into a +TABLE_LIST object that is injected into select's join tree. This will make it - - uniform with semi-joins (we've stepped on all rakes there) - - allow to process JTBM-subqueries in ON expressions + - uniform with semi-join processing (and we've stepped on all rakes there) + - allow to process JTBM-subqueries in LEFT JOINs' ON expressions + (as TABLE_LIST hierarchy is what is used to encode outer joins) simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. -for EXPLAIN EXTENDED, it would be natural to print something semi-join like, -i.e. for +EXPLAIN EXTENDED will display the conversion result as join, that is, for +subquery like SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) -we'll print +it will print - SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + SELECT ... FROM ot, <materialize>(SELECT ie FROM materialized-non-sj-select) -the XX part is not clear. we don't want to print 'ie' the second time here? +After we've added a TABLE_LIST element, we'll need to remove the subquery +predicate from the WHERE/On clause, in the same way as SJ-converter does it. -2.2 On subquery predicate removal ---------------------------------- -Q: if we remove the subquery predicate permanently, who will run -fix_fields() for it? For semi-joins we don't have the problem as we -inject into ON expression (right? or not? we have sj_on_expr, too... -(Investigation: we the the same Item* pointer both in WHERE and -as sj_on_expr. fix_fields() is called for the WHERE part and that's -how sj_on_expr gets fixed. This works as long as -Item_func_eq::fix_fields() does not try to substitute itself with -another item). +TODO: note: we inject a new kind of base table and then next PS re-execution +will re-run name resolution/etc on TABLE_LIST tree that includes this table. +We'll need to make sure that materialized-table doesn't interfere with +anything. + +TODO: Separation of steps that need to be done once and steps that need to be +re-done for every PS execution. + +2.1.1 On subquery predicate removal +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Q: if we remove the subquery predicate permanently, who will call +fix_fields() for the oe=ie IN-equality? For semi-joins we don't have this +problem as we inject into ON expression + +(right? or not? we have sj_on_expr, too... + (Investigation: we the the same Item* pointer both in WHERE and + as sj_on_expr. fix_fields() is called for the WHERE part and that's + how sj_on_expr gets fixed. This works as long as + Item_func_eq::fix_fields() does not try to substitute itself with + another item) +) A: ? -2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable @@ -70,71 +81,96 @@ Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. -2.3 What is expected of the result of conversion ------------------------------------------------- -Join [pre]optimization relies on each optimized entity to have a bit in -table_map. - -TODO: where do we check if there will be enough bits for everyone? (at the - point where we assign them?) +Note: if we convert that late, we could remove the subquery predicate in the +same way as remove_eq_conds() removes known-to-be-false AND-parts. +This operation is undoable. + +2.3 Conversion process and its result +------------------------------------- +At the moment, the following is being implemented: + +* convert_join_subqueries_to_semijoins() handles both conversions to + semi-joins (with convert_subq_to_sj()) and to JTBM nests (with + convert_subq_to_jtbm()). + - This means the process is done in bottom-up way (we walk down into + semi-join subselects but not jtbm-selects), with protection against + exhaustion of table bits beyond #MAX_TABLES, etc. + +* convert_subq_to_jtbm() invokes + + subq_predicate->setup_engine() + + which creates the temporary table, and then we use it as the TABLE* object + that we put into TABLE_LIST::table, JOIN_TAB::table, etc. + One of the first actions is that we assign it a bit and store it in + table->map. + (Note that this won't work when we add a choice between Materialization and + IN->EXISTS) -The bit stored in join_tab->table->map, and the apparent problem is that JTBM -join_tabs do not naturally have TABLE* object. + (Another option is do like done in FROM optimization: create a 'preliminary' + TABLE* object which has only some of the fields filled in). -We could use the the one that will be used for Materialization, but that will -stop working when we will have to include IN->EXISTS in the choice. - -Current approach: don't create a table. create a table_map element in JOIN_TAB -instead. Evgen has probably done something like that already. +QQ: Do we create/inject IN-equality? why not.. it will be used everywhere + where needed... 3. Pre-optimization steps ========================= -JOIN_TABs are allocated in make_join_statistics(). This where the changes will -be needed: for JOIN_TABs that correspond to JTBM-tables: - -- don't set tab->table, set tab->jtbm_select (or whatever) +JOIN_TABs are allocated in make_join_statistics(). For JTBM join_tabs we'll +need: - run subquery's optimizer to get its output cardinality +- todo what else? + +3.1 update_ref_and_keys +----------------------- +Since we've injected the equality into WHERE/ON and created a TABLE* with +appropriate index no special steps are necessary. The regular procedure will +add the needed KEYUSE elements. -3.1 Constant detection +3.2 Constant detection ---------------------- -What about subqueries that "are constant"? - const_item IN (SELECT uncorrelated) -> is constant, but not something - we would want to evaluate. - something IN (SELECT from_constant_join) -> is constant +* We should not let const table detection code to look at the materialized + table, find it to be empty and mark it as constant. -Do we need to mark their JOIN_TABs as constant? +* We also should disallow eq_ref(const)-based detection for materialized table + That is, if outer_expr is found to be constant, the code should not attempt + to make a eq_ref(const) lookup in the materialized table. -3.3 update_ref_and_keys ------------------------ -* Walk through JTBM elements and inject KEYUSE elements for their - IN-equalities. +TODO { + Second: What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate at optimization phase. + + something IN (SELECT from_constant_join) -> is constant + Do we need to mark their JOIN_TABs as constant?) +} -TODO: KEYUSE elements imply presense of KEYs! Which we don't have! +4. Join optimization +==================== +Should just work, with exception that we need to make best_access_path not +to use table->stats.records but use JOIN_TAB::records instead (which already +exists and is filled) -3.4 JOIN_TAB sorting criteria ------------------------------ -Q: Where do we put JTBM's join_tab when pre-sorting records? -A: it should sort as regular table. - -TODO: where do we remove the predicates from the WHERE? - - remove them like SJ-converter does - - remove them with optimizer (like remove_eq_conds does) - -4. Optimization -=============== -Add a branch in best_access_path to account for -- JTBM-Materialization -- JTBM-Materialization-Scan. +NOTE: FROM-optimization does set table->stats.records. Whose approach is + wrong? 5. Execution ============ -* We should be able to reuse item_subselect.cc code for lookups -* But will have to use our own temptable scan code +We need that: +1. Materialization operation (i.e. execute the subquery and fill the + materialized temptable) is done at start of execution +2. Materialized table is accessed during execution (either by doing lookups or + full table scan) +3. IN-equality is checked at the right moment. + +#2 is achieved by putting the materialized table into join_tab->table. +#3 is achieved by injecting IN-equality into the WHERE. -TODO: is it possible to have any unification with SJ-Materialization? +for #1, we need to place a call that fills the temptable at the right location. -User interface --------------- -Any @@optimizer_switch flags for all this? +6. User interface +================= +No @@optimizer_switch flags are planned at the moment. JTBM-materialization +will be controlled by existing materialization=on|off switch. +We could also add a generic materialization_scan=on|off switch. -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. ------------------------------------------------------------ -=-=(View All Progress Notes, 12 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.1.1 On subquery predicate removal 2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 Conversion process and its result 3. Pre-optimization steps 3.1 update_ref_and_keys 3.2 Constant detection 4. Join optimization 5. Execution 6. User interface </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== Applicability criteria of jtbm-materialization can be checked at JOIN::prepare() stage (same as with with semi-join applicability checks) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: a jtbm-predicate is converted into a TABLE_LIST object that is injected into select's join tree. This will make it - uniform with semi-join processing (and we've stepped on all rakes there) - allow to process JTBM-subqueries in LEFT JOINs' ON expressions (as TABLE_LIST hierarchy is what is used to encode outer joins) simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. EXPLAIN EXTENDED will display the conversion result as join, that is, for subquery like SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) it will print SELECT ... FROM ot, <materialize>(SELECT ie FROM materialized-non-sj-select) After we've added a TABLE_LIST element, we'll need to remove the subquery predicate from the WHERE/On clause, in the same way as SJ-converter does it. TODO: note: we inject a new kind of base table and then next PS re-execution will re-run name resolution/etc on TABLE_LIST tree that includes this table. We'll need to make sure that materialized-table doesn't interfere with anything. TODO: Separation of steps that need to be done once and steps that need to be re-done for every PS execution. 2.1.1 On subquery predicate removal ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Q: if we remove the subquery predicate permanently, who will call fix_fields() for the oe=ie IN-equality? For semi-joins we don't have this problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item) ) A: ? 2.2 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. Note: if we convert that late, we could remove the subquery predicate in the same way as remove_eq_conds() removes known-to-be-false AND-parts. This operation is undoable. 2.3 Conversion process and its result ------------------------------------- At the moment, the following is being implemented: * convert_join_subqueries_to_semijoins() handles both conversions to semi-joins (with convert_subq_to_sj()) and to JTBM nests (with convert_subq_to_jtbm()). - This means the process is done in bottom-up way (we walk down into semi-join subselects but not jtbm-selects), with protection against exhaustion of table bits beyond #MAX_TABLES, etc. * convert_subq_to_jtbm() invokes subq_predicate->setup_engine() which creates the temporary table, and then we use it as the TABLE* object that we put into TABLE_LIST::table, JOIN_TAB::table, etc. One of the first actions is that we assign it a bit and store it in table->map. (Note that this won't work when we add a choice between Materialization and IN->EXISTS) (Another option is do like done in FROM optimization: create a 'preliminary' TABLE* object which has only some of the fields filled in). QQ: Do we create/inject IN-equality? why not.. it will be used everywhere where needed... 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). For JTBM join_tabs we'll need: - run subquery's optimizer to get its output cardinality - todo what else? 3.1 update_ref_and_keys ----------------------- Since we've injected the equality into WHERE/ON and created a TABLE* with appropriate index no special steps are necessary. The regular procedure will add the needed KEYUSE elements. 3.2 Constant detection ---------------------- * We should not let const table detection code to look at the materialized table, find it to be empty and mark it as constant. * We also should disallow eq_ref(const)-based detection for materialized table That is, if outer_expr is found to be constant, the code should not attempt to make a eq_ref(const) lookup in the materialized table. TODO { Second: What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate at optimization phase. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant?) } 4. Join optimization ==================== Should just work, with exception that we need to make best_access_path not to use table->stats.records but use JOIN_TAB::records instead (which already exists and is filled) NOTE: FROM-optimization does set table->stats.records. Whose approach is wrong? 5. Execution ============ We need that: 1. Materialization operation (i.e. execute the subquery and fill the materialized temptable) is done at start of execution 2. Materialized table is accessed during execution (either by doing lookups or full table scan) 3. IN-equality is checked at the right moment. #2 is achieved by putting the materialized table into join_tab->table. #3 is achieved by injecting IN-equality into the WHERE. for #1, we need to place a call that fills the temptable at the right location. 6. User interface ================= No @@optimizer_switch flags are planned at the moment. JTBM-materialization will be controlled by existing materialization=on|off switch. We could also add a generic materialization_scan=on|off switch. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2835: Fixed random failure in test system
by noreply＠launchpad.net 30 Mar '10

30 Mar '10

------------------------------------------------------------ revno: 2835 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Mon 2010-03-29 22:07:45 +0300 message: Fixed random failure in test system Removed and suppressed compiler warnings modified: mysql-test/suite/parts/t/rpl_partition.test mysql-test/suite/rpl/t/rpl_row_trig003.test sql/item_create.cc sql/net_serv.cc support-files/compiler_warnings.supp -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2835)
by Michael Widenius 30 Mar '10

30 Mar '10

#At lp:maria based on revid:monty@askmonty.org-20100328181000-qyehmg51jkgpht8w 2835 Michael Widenius 2010-03-29 Fixed random failure in test system Removed and suppressed compiler warnings modified: mysql-test/suite/parts/t/rpl_partition.test mysql-test/suite/rpl/t/rpl_row_trig003.test sql/item_create.cc sql/net_serv.cc support-files/compiler_warnings.supp per-file messages: mysql-test/suite/parts/t/rpl_partition.test Mark as big test (as test takes > 5 minutes) mysql-test/suite/rpl/t/rpl_row_trig003.test Fixed random failure sql/item_create.cc Renamed create -> create_builder() to avoid compiler warnings about function hidden by others sql/net_serv.cc Removed compiler warning support-files/compiler_warnings.supp Supress some warnings Remove suppresstion from set_var.cc, as this has already been fixed Added missing \ before .c Added suppression of warnings that are due to gcc bug (.*discards qualifiers from pointer target type.*) === modified file 'mysql-test/suite/parts/t/rpl_partition.test' --- a/mysql-test/suite/parts/t/rpl_partition.test 2009-02-01 12:00:48 +0000 +++ b/mysql-test/suite/parts/t/rpl_partition.test 2010-03-29 19:07:45 +0000 @@ -1,6 +1,7 @@ --source include/have_partition.inc --source include/have_innodb.inc --source include/master-slave.inc +--source include/big_test.inc --vertical_results === modified file 'mysql-test/suite/rpl/t/rpl_row_trig003.test' --- a/mysql-test/suite/rpl/t/rpl_row_trig003.test 2007-12-06 15:27:10 +0000 +++ b/mysql-test/suite/rpl/t/rpl_row_trig003.test 2010-03-29 19:07:45 +0000 @@ -149,6 +149,8 @@ DROP TABLE IF EXISTS test.t2; DROP TABLE IF EXISTS test.t3; --enable_warnings +sync_slave_with_master; + diff_files $MYSQLTEST_VARDIR/tmp/trg003_master.sql $MYSQLTEST_VARDIR/tmp/trg003_slave.sql; # End of 5.0 test case === modified file 'sql/item_create.cc' --- a/sql/item_create.cc 2010-03-28 18:10:00 +0000 +++ b/sql/item_create.cc 2010-03-29 19:07:45 +0000 @@ -76,7 +76,7 @@ public: @param thd The current thread @return An item representing the function call */ - virtual Item *create(THD *thd) = 0; + virtual Item *create_builder(THD *thd) = 0; protected: /** Constructor. */ @@ -498,7 +498,7 @@ protected: class Create_func_connection_id : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_connection_id s_singleton; @@ -943,7 +943,7 @@ protected: class Create_func_found_rows : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_found_rows s_singleton; @@ -1647,7 +1647,7 @@ protected: class Create_func_pi : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_pi s_singleton; @@ -1766,7 +1766,7 @@ protected: class Create_func_row_count : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_row_count s_singleton; @@ -2136,7 +2136,7 @@ protected: class Create_func_uuid : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_uuid s_singleton; @@ -2149,7 +2149,7 @@ protected: class Create_func_uuid_short : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_uuid_short s_singleton; @@ -2162,7 +2162,7 @@ protected: class Create_func_version : public Create_func_arg0 { public: - virtual Item *create(THD *thd); + virtual Item *create_builder(THD *thd); static Create_func_version s_singleton; @@ -2538,7 +2538,7 @@ Create_func_arg0::create(THD *thd, LEX_S return NULL; } - return create(thd); + return create_builder(thd); } @@ -2882,7 +2882,7 @@ Create_func_compress::create_1_arg(THD * Create_func_connection_id Create_func_connection_id::s_singleton; Item* -Create_func_connection_id::create(THD *thd) +Create_func_connection_id::create_builder(THD *thd) { thd->lex->safe_to_cache_query= 0; return new (thd->mem_root) Item_func_connection_id(); @@ -3361,7 +3361,7 @@ Create_func_format::create_2_arg(THD *th Create_func_found_rows Create_func_found_rows::s_singleton; Item* -Create_func_found_rows::create(THD *thd) +Create_func_found_rows::create_builder(THD *thd) { thd->lex->set_stmt_unsafe(); thd->lex->safe_to_cache_query= 0; @@ -4120,7 +4120,7 @@ Create_func_period_diff::create_2_arg(TH Create_func_pi Create_func_pi::s_singleton; Item* -Create_func_pi::create(THD *thd) +Create_func_pi::create_builder(THD *thd) { return new (thd->mem_root) Item_static_float_func("pi()", M_PI, 6, 8); } @@ -4274,7 +4274,7 @@ Create_func_round::create_native(THD *th Create_func_row_count Create_func_row_count::s_singleton; Item* -Create_func_row_count::create(THD *thd) +Create_func_row_count::create_builder(THD *thd) { thd->lex->set_stmt_unsafe(); thd->lex->safe_to_cache_query= 0; @@ -4584,7 +4584,7 @@ Create_func_unix_timestamp::create_nativ Create_func_uuid Create_func_uuid::s_singleton; Item* -Create_func_uuid::create(THD *thd) +Create_func_uuid::create_builder(THD *thd) { thd->lex->set_stmt_unsafe(); thd->lex->safe_to_cache_query= 0; @@ -4595,7 +4595,7 @@ Create_func_uuid::create(THD *thd) Create_func_uuid_short Create_func_uuid_short::s_singleton; Item* -Create_func_uuid_short::create(THD *thd) +Create_func_uuid_short::create_builder(THD *thd) { thd->lex->set_stmt_unsafe(); thd->lex->safe_to_cache_query= 0; @@ -4606,7 +4606,7 @@ Create_func_uuid_short::create(THD *thd) Create_func_version Create_func_version::s_singleton; Item* -Create_func_version::create(THD *thd) +Create_func_version::create_builder(THD *thd) { thd->lex->set_stmt_unsafe(); return new (thd->mem_root) Item_static_string_func("version()", === modified file 'sql/net_serv.cc' --- a/sql/net_serv.cc 2010-02-23 12:04:58 +0000 +++ b/sql/net_serv.cc 2010-03-29 19:07:45 +0000 @@ -279,7 +279,7 @@ static int net_data_is_ready(my_socket s @param clear_buffer if <> 0, then clear all data from comm buff */ -void net_clear(NET *net, my_bool clear_buffer) +void net_clear(NET *net, my_bool clear_buffer __attribute__((unused))) { #if !defined(EMBEDDED_LIBRARY) && defined(DBUG_OFF) size_t count; === modified file 'support-files/compiler_warnings.supp' --- a/support-files/compiler_warnings.supp 2010-03-28 18:10:00 +0000 +++ b/support-files/compiler_warnings.supp 2010-03-29 19:07:45 +0000 @@ -39,6 +39,7 @@ sync/sync0rw\.c: unused parameter sync/sync0sync\.c: unused parameter sync/sync0sync\.c: unused variable ut/ut0ut\.c: ignoring return value of +srv/srv0srv\.c: value computed is not used # # bdb is not critical to keep up to date @@ -128,18 +129,18 @@ include/runtime.hpp: .*pure_error.* # listener.cc : .*conversion from 'SOCKET' to 'int'.* net_serv.cc : .*conversion from 'SOCKET' to 'int'.* -set_var.cc: right-hand operand of comma has no effect : 1000-1400 - # allow a little moving space for the warning below -mi_packrec.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 560-600 -ma_packrec.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 550-650 +mi_packrec\.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 560-600 +ma_packrec\.c : .*result of 32-bit shift implicitly converted to 64 bits.* : 550-650 # # Wrong compiler warnings # .* : .*no matching operator delete found; memory will not be freed if initialization throws an exception.* -ctype-simple.c : .*unary minus operator applied to unsigned type, result still unsigned.* +ctype-simple\.c : .*unary minus operator applied to unsigned type, result still unsigned.* # Wrong warning due to GCC bug: http://gcc.gnu.org/bugzilla/show_bug.cgi?id=29478 -regexec\.c : .*passing argument 3 of.*matcher.* discards qualifiers from pointer target type.* +regexec\.c : passing argument 3 of.*matcher.* discards qualifiers from pointer target type +libmysql\.c: passing argument 2 of .*memcpy.* discards qualifiers from pointer target type : 3000-4000 +storage/xtradb/dict/dict0dict\.c: passing argument 1 of .*strcpy.* discards qualifiers from pointer target type : 2500-3500

1 0

[Maria-developers] Rev 2758: Atomic operation removed because we do not need it. in file:///home/bell/maria/bzr/work-maria-5.2-sync/
by sanja＠askmonty.org 30 Mar '10

30 Mar '10

At file:///home/bell/maria/bzr/work-maria-5.2-sync/ ------------------------------------------------------------ revno: 2758 revision-id: sanja(a)askmonty.org-20100329185318-s8s6lsuvm12oi8yb parent: sergii(a)pisem.net-20100329151428-zkvrkidiyqmb19pn committer: sanja(a)askmonty.org branch nick: work-maria-5.2-sync timestamp: Mon 2010-03-29 21:53:18 +0300 message: Atomic operation removed because we do not need it. === modified file 'storage/maria/ma_loghandler.c' --- a/storage/maria/ma_loghandler.c 2010-03-15 11:51:23 +0000 +++ b/storage/maria/ma_loghandler.c 2010-03-29 18:53:18 +0000 @@ -8060,18 +8060,14 @@ /* keep values for soft sync() and forced sync() actual */ { uint32 fileno= LSN_FILE_NO(lsn); - my_atomic_rwlock_wrlock(&soft_sync_rwl); - my_atomic_store32(&soft_sync_min, fileno); - my_atomic_store32(&soft_sync_max, fileno); - my_atomic_rwlock_wrunlock(&soft_sync_rwl); + soft_sync_min= fileno; + soft_sync_max= fileno; } } else { - my_atomic_rwlock_wrlock(&soft_sync_rwl); - my_atomic_store32(&soft_sync_max, LSN_FILE_NO(lsn)); - my_atomic_store32(&soft_need_sync, 1); - my_atomic_rwlock_wrunlock(&soft_sync_rwl); + soft_sync_max= lsn; + soft_need_sync= 1; } DBUG_ASSERT(flush_horizon <= log_descriptor.horizon); @@ -8464,9 +8460,7 @@ translog_status == TRANSLOG_READONLY); soft= soft_sync; - my_atomic_rwlock_wrlock(&soft_sync_rwl); - min_unsync= my_atomic_load32(&soft_sync_min); - my_atomic_rwlock_wrunlock(&soft_sync_rwl); + min_unsync= soft_sync_min; DBUG_PRINT("info", ("min_unsync: %lu", (ulong) min_unsync)); if (soft && min_unsync < last_need_file) { @@ -8748,9 +8742,7 @@ uint32 min; DBUG_ENTER("ma_translog_sync"); - my_atomic_rwlock_rdlock(&soft_sync_rwl); - min= my_atomic_load32(&soft_sync_min); - my_atomic_rwlock_rdunlock(&soft_sync_rwl); + min= soft_sync_min; if (!min) min= max; @@ -8796,13 +8788,11 @@ ulonglong prev_loop= my_micro_time(); ulonglong time, sleep; uint32 min, max, sync_request; - my_atomic_rwlock_rdlock(&soft_sync_rwl); - min= my_atomic_load32(&soft_sync_min); - max= my_atomic_load32(&soft_sync_max); - sync_request= my_atomic_load32(&soft_need_sync); - my_atomic_store32(&soft_sync_min, max); - my_atomic_store32(&soft_need_sync, 0); - my_atomic_rwlock_rdunlock(&soft_sync_rwl); + min= soft_sync_min; + max= soft_sync_max; + sync_request= soft_need_sync; + soft_sync_min= max; + soft_need_sync= 0; sleep= group_commit_wait; if (sync_request) @@ -8834,15 +8824,13 @@ DBUG_ENTER("translog_soft_sync_start"); /* check and init variables */ - my_atomic_rwlock_rdlock(&soft_sync_rwl); - min= my_atomic_load32(&soft_sync_min); - max= my_atomic_load32(&soft_sync_max); + min= soft_sync_min; + max= soft_sync_max; if (!max) - my_atomic_store32(&soft_sync_max, (max= get_current_logfile()->number)); + soft_sync_max= max= get_current_logfile()->number; if (!min) - my_atomic_store32(&soft_sync_min, max); - my_atomic_store32(&soft_need_sync, 1); - my_atomic_rwlock_rdunlock(&soft_sync_rwl); + soft_sync_min= max; + soft_need_sync= 1; if (!(res= ma_service_thread_control_init(&soft_sync_control))) if (!(res= pthread_create(&th, NULL, ma_soft_sync_background, NULL)))

1 0

[Maria-developers] Updated (by Knielsen): Merge OQGraph into MariaDB (112)
by worklog-noreply＠askmonty.org 30 Mar '10

30 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Merge OQGraph into MariaDB CREATION DATE..: Mon, 29 Mar 2010, 18:00 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 112 (http://askmonty.org/worklog/?tid=112) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 15 (hours remain) ORIG. ESTIMATE.: 15 PROGRESS NOTES: -=-=(Knielsen - Mon, 29 Mar 2010, 18:09)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.23061 2010-03-29 18:09:27.000000000 +0000 +++ /tmp/wklog.112.new.23061 2010-03-29 18:09:27.000000000 +0000 @@ -1 +1,28 @@ +Tasks: + +Find the latest version of OQGraph to base this on (there should be a +Launchpad branch somewhere, match it up with what is in the OQGraph patch for +MySQL 5.0 in the ourdelta stuff). + +Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This +is a patched version of Boost fixing a bug that is supposedly fatal for +OQGraph (details are not known at the time of writing). + +Document in OQGraph README the need for boost of a specific version, and point +to where it can be obtained. Also include the patch for boost if the correct +base version of boost to do this against can be determined. + +Install the patched boost in /usr/local/ on the build machines (release builds +and selected Buildbot slaves). + +Fix OQGraph plug.in to detect correct version of OQGraph that makes the build +not break. Check which version in Ubuntu starts working (I think it was +Jaunty), and require at least that version. + +Setup some repository or source tarball of the patched boost +somewhere. Preferably a Launchpad branch or similar (if upstream project can +be found). + +Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, +we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. DESCRIPTION: Get the OQGraph storage engine merged into MariaDB, fixing the remaining problems blocking the merge. HIGH-LEVEL SPECIFICATION: Tasks: Find the latest version of OQGraph to base this on (there should be a Launchpad branch somewhere, match it up with what is in the OQGraph patch for MySQL 5.0 in the ourdelta stuff). Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This is a patched version of Boost fixing a bug that is supposedly fatal for OQGraph (details are not known at the time of writing). Document in OQGraph README the need for boost of a specific version, and point to where it can be obtained. Also include the patch for boost if the correct base version of boost to do this against can be determined. Install the patched boost in /usr/local/ on the build machines (release builds and selected Buildbot slaves). Fix OQGraph plug.in to detect correct version of OQGraph that makes the build not break. Check which version in Ubuntu starts working (I think it was Jaunty), and require at least that version. Setup some repository or source tarball of the patched boost somewhere. Preferably a Launchpad branch or similar (if upstream project can be found). Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Knielsen): Merge OQGraph into MariaDB (112)
by worklog-noreply＠askmonty.org 30 Mar '10

30 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Merge OQGraph into MariaDB CREATION DATE..: Mon, 29 Mar 2010, 18:00 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 112 (http://askmonty.org/worklog/?tid=112) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 15 (hours remain) ORIG. ESTIMATE.: 15 PROGRESS NOTES: DESCRIPTION: Get the OQGraph storage engine merged into MariaDB, fixing the remaining problems blocking the merge. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 29 Mar 2010, 14:09)=-=- Status updated. --- /tmp/wklog.110.old.11193 2010-03-29 14:09:27.000000000 +0000 +++ /tmp/wklog.110.new.11193 2010-03-29 14:09:27.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Complete -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 29 Mar 2010, 14:09)=-=- Status updated. --- /tmp/wklog.110.old.11193 2010-03-29 14:09:27.000000000 +0000 +++ /tmp/wklog.110.new.11193 2010-03-29 14:09:27.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Complete -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2786: MWL#110: Make EXPLAIN always show materialization separately in file:///home/psergey/dev/maria-5.3-subqueries-r10/
by Sergey Petrunya 29 Mar '10

29 Mar '10

At file:///home/psergey/dev/maria-5.3-subqueries-r10/ ------------------------------------------------------------ revno: 2786 revision-id: psergey(a)askmonty.org-20100329140435-bb21mofh3i85tt4q parent: psergey(a)askmonty.org-20100323145750-sr9oucry979i3p60 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r10 timestamp: Mon 2010-03-29 18:04:35 +0400 message: MWL#110: Make EXPLAIN always show materialization separately - Add Item_in_subselect::get_identifier() that returns subquery's id - Change select_describe() to produce output in new format - Update test results (checked) === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect3.result 2010-03-29 14:04:35 +0000 @@ -1017,10 +1017,11 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan -1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer +2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where +2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; a b c @@ -1034,7 +1035,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize +2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq NULL @@ -1156,8 +1158,9 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); a filler 1 filler @@ -1204,8 +1207,9 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where @@ -1233,14 +1237,16 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where +2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1264,13 +1270,15 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; drop table t0, t1, t2; @@ -1308,9 +1316,10 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer -1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer +2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 @@ -1380,9 +1389,10 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan -1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer +2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) drop table t1,t2,t3; === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-03-11 21:43:31 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-03-29 14:04:35 +0000 @@ -1021,10 +1021,11 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan -1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer +2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where +2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; a b c @@ -1039,7 +1040,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize +2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq NULL @@ -1161,8 +1163,9 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); a filler 1 filler @@ -1209,8 +1212,9 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where @@ -1238,14 +1242,16 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer +2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1269,13 +1275,15 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; drop table t0, t1, t2; @@ -1313,9 +1321,10 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer -1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer +2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 @@ -1385,9 +1394,10 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan -1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize; Using join buffer +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer +2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) drop table t1,t2,t3; === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect4.result 2010-03-29 14:04:35 +0000 @@ -216,8 +216,9 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -230,13 +231,15 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; CREATE INDEX t1_IDX ON t1(EMPNUM); @@ -251,8 +254,9 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -265,13 +269,15 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; EXPLAIN SELECT EMPNAME @@ -285,8 +291,9 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -299,13 +306,15 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize -1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; SET SESSION optimizer_switch = @old_optimizer_switch; SET SESSION join_cache_level = @old_join_cache_level; === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2010-03-13 21:11:06 +0000 +++ b/mysql-test/r/subselect_mat.result 2010-03-29 14:04:35 +0000 @@ -1190,8 +1190,9 @@ SET @@optimizer_switch='default,semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) 2 @@ -1211,7 +1212,8 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk 2 === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-15 19:52:58 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 @@ -848,7 +848,8 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); @@ -1016,7 +1017,8 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) SELECT varchar_nokey === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-03-29 14:04:35 +0000 @@ -32,8 +32,9 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -73,8 +74,9 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 +2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -99,7 +101,8 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 index b b 5 NULL 10 Using index; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b 1 1 @@ -126,8 +129,9 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer +2 SUBQUERY it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -159,7 +163,8 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY it ALL NULL NULL NULL NULL 32 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -192,8 +197,9 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer +2 SUBQUERY it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -225,7 +231,8 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY it ALL NULL NULL NULL NULL 52 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -341,7 +348,8 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -684,7 +692,8 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY t3 index a a 5 NULL 30000 Using index; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-29 14:04:35 +0000 @@ -36,8 +36,9 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -77,8 +78,9 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer +2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -103,7 +105,8 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 index b b 5 NULL 10 Using index; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b 1 1 @@ -130,8 +133,9 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer +2 SUBQUERY it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -163,7 +167,8 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY it ALL NULL NULL NULL NULL 32 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -196,8 +201,9 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer +2 SUBQUERY it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -229,7 +235,8 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY it ALL NULL NULL NULL NULL 52 Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -345,7 +352,8 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -690,7 +698,8 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY t3 index a a 5 NULL 30000 Using index; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-15 19:52:58 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 @@ -852,7 +852,8 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); @@ -1020,7 +1021,8 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where; Materialize +1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) SELECT varchar_nokey === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_subselect.cc 2010-03-29 14:04:35 +0000 @@ -926,6 +926,11 @@ DBUG_VOID_RETURN; } +int Item_in_subselect::get_identifier() +{ + return engine->get_identifier(); +} + Item_allany_subselect::Item_allany_subselect(Item * left_exp, chooser_compare_func_creator fc, st_select_lex *select_lex, @@ -2271,6 +2276,10 @@ select_lex->master_unit()->item= item_arg; } +int subselect_single_select_engine::get_identifier() +{ + return select_lex->select_number; +} void subselect_single_select_engine::cleanup() { === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_subselect.h 2010-03-29 14:04:35 +0000 @@ -433,7 +433,12 @@ /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } bool is_expensive_processor(uchar *arg); - + + /* + Return the identifier that we could use to identify the subquery for the + user. + */ + int get_identifier(); friend class Item_ref_null_helper; friend class Item_is_not_null_test; friend class Item_in_optimizer; @@ -534,7 +539,7 @@ /* Check if subquery produced any rows during last query execution */ virtual bool no_rows() = 0; virtual enum_engine_type engine_type() { return ABSTRACT_ENGINE; } - + virtual int get_identifier() { DBUG_ASSERT(0); return 0; } protected: void set_row(List<Item> &item_list, Item_cache **row); }; @@ -566,6 +571,7 @@ bool is_executed() const { return executed; } bool no_rows(); virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; } + int get_identifier(); friend class subselect_hash_sj_engine; friend class Item_in_subselect; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-03-20 16:59:30 +0000 +++ b/sql/sql_select.cc 2010-03-29 14:04:35 +0000 @@ -17889,8 +17889,15 @@ else { table_map used_tables=0; - uint last_sjm_table= MAX_TABLES; - for (uint i=0 ; i < join->tables ; i++) + + uchar sjm_nests[MAX_TABLES]; + uint sjm_nests_cur=0; + uint sjm_nests_end= 0; + uint end_table= join->tables; + bool printing_materialize_nest= FALSE; + uint select_id= join->select_lex->select_number; + + for (uint i=0 ; i < end_table ; i++) { JOIN_TAB *tab=join->join_tab+i; TABLE *table=tab->table; @@ -17898,6 +17905,7 @@ char buff[512]; char buff1[512], buff2[512], buff3[512]; char keylen_str_buf[64]; + my_bool key_read; String extra(buff, sizeof(buff),cs); char table_name_buffer[NAME_LEN]; String tmp1(buff1,sizeof(buff1),cs); @@ -17907,7 +17915,6 @@ tmp1.length(0); tmp2.length(0); tmp3.length(0); - quick_type= -1; /* Don't show eliminated tables */ @@ -17919,12 +17926,89 @@ item_list.empty(); /* id */ - item_list.push_back(new Item_uint((uint32) - join->select_lex->select_number)); + item_list.push_back(new Item_uint((uint32)select_id)); /* select_type */ - item_list.push_back(new Item_string(join->select_lex->type, - strlen(join->select_lex->type), - cs)); + const char* stype= printing_materialize_nest? "SUBQUERY" : + join->select_lex->type; + item_list.push_back(new Item_string(stype, strlen(stype), cs)); + + /* + Special processing for SJ-Materialization nests: print the fake table + and delay printing of the SJM nest contents until later. + */ + uint sj_strategy= join->best_positions[i].sj_strategy; + if (sj_is_materialize_strategy(sj_strategy) && + !printing_materialize_nest) + { + /* table */ + int len= my_snprintf(table_name_buffer, + sizeof(table_name_buffer)-1, + "subselect%d", + tab->emb_sj_nest->sj_subq_pred->get_identifier()); + item_list.push_back(new Item_string(table_name_buffer, len, cs)); + /* partitions */ + if (join->thd->lex->describe & DESCRIBE_PARTITIONS) + item_list.push_back(item_null); + /* type */ + uint type= (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? JT_ALL : JT_EQ_REF; + item_list.push_back(new Item_string(join_type_str[type], + strlen(join_type_str[type]), + cs)); + /* possible_keys */ + item_list.push_back(new Item_string("unique_key", + strlen("unique_key"), cs)); + if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN) + { + item_list.push_back(item_null); /* key */ + item_list.push_back(item_null); /* key_len */ + item_list.push_back(item_null); /* ref */ + } + else + { + /* key */ + item_list.push_back(new Item_string("unique_key", strlen("unique_key"), cs)); + /* key_len */ + uint klen= tab->emb_sj_nest->sj_mat_info->table->key_info[0].key_length; + uint buflen= longlong2str(klen, keylen_str_buf, 10) - keylen_str_buf; + item_list.push_back(new Item_string(keylen_str_buf, buflen, cs)); + /* ref */ + item_list.push_back(new Item_string("func", strlen("func"), cs)); + } + /* rows */ + ha_rows rows= (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)? + tab->emb_sj_nest->sj_mat_info->rows : 1; + item_list.push_back(new Item_int(rows)); + /* filtered */ + if (join->thd->lex->describe & DESCRIBE_EXTENDED) + item_list.push_back(new Item_float(1.0, 2)); + + /* Extra */ + if (need_tmp_table) + { + need_tmp_table=0; + extra.append(STRING_WITH_LEN("; Using temporary")); + } + if (need_order) + { + need_order=0; + extra.append(STRING_WITH_LEN("; Using filesort")); + } + /* Skip initial "; "*/ + const char *str= extra.ptr(); + uint32 extra_len= extra.length(); + if (extra_len) + { + str += 2; + extra_len -= 2; + } + item_list.push_back(new Item_string(str, extra_len, cs)); + + /* Register the nest for further processing: */ + sjm_nests[sjm_nests_end++]= i; + i += join->best_positions[i].n_sj_tables-1; + goto loop_end; + } + if (tab->type == JT_ALL && tab->select && tab->select->quick) { quick_type= tab->select->quick->get_type(); @@ -17935,6 +18019,7 @@ else tab->type = JT_RANGE; } + /* table */ if (table->derived_select_number) { @@ -18113,7 +18198,7 @@ } /* Build "Extra" field and add it to item_list. */ - my_bool key_read=table->key_read; + key_read=table->key_read; if ((tab->type == JT_NEXT || tab->type == JT_CONST) && table->covering_keys.is_set(tab->index)) key_read=1; @@ -18269,7 +18354,8 @@ extra.append(STRING_WITH_LEN(")")); } } - uint sj_strategy= join->best_positions[i].sj_strategy; + + /* if (sj_is_materialize_strategy(sj_strategy)) { if (join->best_positions[i].n_sj_tables == 1) @@ -18286,6 +18372,7 @@ { extra.append(STRING_WITH_LEN("; End materialize")); } + */ for (uint part= 0; part < tab->ref.key_parts; part++) { @@ -18309,6 +18396,15 @@ } item_list.push_back(new Item_string(str, len, cs)); } + loop_end: + if (i+1 == end_table && sjm_nests_cur != sjm_nests_end) + { + printing_materialize_nest= TRUE; + i= sjm_nests[sjm_nests_cur++] - 1; + end_table= (i+1) + join->best_positions[i+1].n_sj_tables; + select_id= join->join_tab[i+1].emb_sj_nest->sj_subq_pred->get_identifier(); + } + // For next iteration used_tables|=table->map; if (result->send_data(item_list))

1 0