- developers - lists.mariadb.org

[Maria-developers] We Are Allowed To Add No More Than Two New Log Events :)
by Alexi1952 22 Feb '10

22 Feb '10

Hi Monty, I found out why MySQL/Sun generously allowed to add new log events starting only from number 196. How many log events with numbers >= 196 can be added? Only TWO !!! Indeed, each event must have post header length <= 254 because this length must be saved in the uchar post_header_len[] array (the value 255 is reserved as the "undefined" length value). Now, the post header of the Format description event has the length = number_of_events + 57. Hence max number of possible events in MySQL is 254 - 57 = 197. Funny :) Cheers, Alex

2 1

[Maria-developers] Updated (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 22 Feb '10

22 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Mon, 22 Feb 2010, 17:39)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.17116 2010-02-22 17:39:48.000000000 +0200 +++ /tmp/wklog.68.new.17116 2010-02-22 17:39:48.000000000 +0200 @@ -233,6 +233,7 @@ 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all and v_j1,...,v_jm cannot be null, create for these columns only one index array (and of course do not create any bitmaps for them). +[done] 2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number of rows, where a_i is not null and V(a_i) is the number of distinct @@ -264,6 +265,10 @@ 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. +[This is wrong, because if we don't fill the whole temp table, there may + be some tuple(s) that would match some outer tuple. In such cases, if we + stop filling the temp table, we would miss a TRUE result. Having a partial + match doesn't preclude us from having a complete match]. 8. [timour] Consider that due to materialization, we already have a unique index -=-=(Timour - Tue, 19 Jan 2010, 18:44)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.22569 2010-01-19 18:44:01.000000000 +0200 +++ /tmp/wklog.68.new.22569 2010-01-19 18:44:01.000000000 +0200 @@ -132,11 +132,10 @@ if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE - if (nonull_key) - pq.insert(nonull_key) for (i = 1; i <= n; i++) { + if (vkey[i] != nonull_key) vkey[i].lookup(outer_ref) if (! vkey[i].is_eof()) pq.insert(i) @@ -167,7 +166,7 @@ /* There cannot be a complete match, as we already checked for one. */ assert(matching_keys.elements < n) } - else if (cur_min_key == nonull_key) + else if (vkey[cur_min_key] == nonull_key) { /* The non-NULL key has no corresponding NULL index, so we know for @@ -183,8 +182,10 @@ /* Check if all null_keys contain a NULL at row 'min_row'. The procedure internally checks all keys in a special precomputed order. A prior - procedure determines an optimal order and a mapping - idx_no -> idx_order (encoded as an array). + procedure determines an optimal order and a mapping idx_no -> idx_order + (encoded as an array). + + This procedure makes sure not to match the non-NULL column. */ if (test_null_row(null_keys, min_row)) return TRUE @@ -198,6 +199,14 @@ vkey[cur_min_key].next() if (! vkey[cur_min_key].is_eof()) pq.insert(cur_min_key) + else if (vkey[cur_min_key] == nonull_key) + { + /* + If there can't be more matches for the nonull_key, we know for sure + there is no match, since there is no possible NULL match. + */ + return FALSE + } if (pq.is_empty()) { @@ -216,7 +225,6 @@ } - 3. Directions for improvement ======================================================================== -=-=(Timour - Tue, 19 Jan 2010, 18:29)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.21045 2010-01-19 18:29:12.000000000 +0200 +++ /tmp/wklog.68.new.21045 2010-01-19 18:29:12.000000000 +0200 @@ -132,6 +132,8 @@ if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE + if (nonull_key) + pq.insert(nonull_key) for (i = 1; i <= n; i++) { -=-=(Guest - Tue, 19 Jan 2010, 18:15)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.19825 2010-01-19 18:15:30.000000000 +0200 +++ /tmp/wklog.68.new.19825 2010-01-19 18:15:30.000000000 +0200 @@ -1,8 +1,16 @@ -This a copy of the initial algorithm proposed by Igor: -====================================================== +Contents +======================================================================== -For each left side tuple (v_1,...,v_n) we have to find the following set -of rowids for the temp table containing N rows as the result of +1. Initial idea as proposed by Igor +2. Algorithm for IN execution with partial matching +3. Directions for improvement + + +1. Initial idea as proposed by Igor +======================================================================== + +For each left side tuple (v_1,...,v_n) we have to find the following +set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs @@ -18,38 +26,198 @@ - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap -Taken all above into account I could suggest the following algorithm to -build R: +Taken all above into account I could suggest the following algorithm +to build R: - Using indexes (read about them below) for each column participating in the - intersection, - merge ordered sets rowid{a_i=v_i} in the following manner. + Using indexes (read about them below) for each column participating + in the intersection, merge ordered sets rowid{a_i=v_i} in the + following manner. If a rowid r has been encountered maximum in k sets -rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), + rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is -not in {i1,...,ik}. + not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. -Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i -is null} is either +Here we use the property (1): +any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can -infer that for any r from R -indexes a_i can be uniquely divided into two groups: one contains -indexes a_i where r belongs to -the sets rowid{a_i=v_i}, the other contains indexes a_j such that r -belongs to rowid{a_j is null}. - -Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order -needed for the merge procedure. We could use BTREE indexes for temp -table. But they are rather expensive and -take a lot of memory as the are implemented with RB trees. +infer that for any r from R indexes a_i can be uniquely divided into +two groups: +- one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, +- the other contains indexes a_j such that r belongs to + rowid{a_j is null}. + +Now let's talk how to get elements from rowid{a_i=v_i} in a sorted +order needed for the merge procedure. We could use BTREE indexes for +temp table. But they are rather expensive and take a lot of memory as +the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. +2. Algorithm for IN execution with partial matching +======================================================================== + +2.1 Below is shown the top-level algorithm to execute an IN predicate +with partial matching. This algorithm is essentially the implementation +of Item_subselect:exec(). + +int lookup_with_null_semantics(outer_ref[], mat_subquery) +{ + if (index_lookup(outer_ref, mat_subquery) + return TRUE + else + { + /* + Check if there is a partial match (UNKNOWN) or no match (NULL). + */ + if (this is the first partial match) + { + vkey[] = build array of value keys for each NULL-able column + of mat_subquery. + nkey[] = build a bitmap NULL index for each column of mat_subquery + that contains NULLs + nonull_key = build a key over all non-NULL columns of mat_subquery + } + if (partial_match(outer_ref, vkey[], nkey[], nonull_key) + return UNKNOWN + else + return FALSE + } +} + +2.2 The implementation of partial matching is as follows + +/* + Assumptions: + - It has already been checked if there is a complete match by a + regular index lookup, and the test failed. + - It has already been checked if there is a complete NULL row, + and if there was we wouldn't call this function. Thus we assume + that there is no complete NULL row. + - Not all vidx_i are empty, but some can be empty. If all were empty, + then the only possibility for a match is a complete NULL row, which + we already checked. + + @param outer_ref - the uter (left) IN argument. + @param vidx[] - array of value keys + Ordered sequences of rowids of the corresponding columns a_i, such + that all rowids in idx_i are the ones where column a_i contains some + value or NULL. Each idx_i is derived dynamically, for each different + left argument of an IN predicate. + @param nidx[] - array of NULL keys + Bitmpas, one per each column, where a bit is set if the corresponding + row has a NULL value for the corresponding column. + @nonull_key - the only key over all columns of the materialized subquery + that do not contain NULLs + + @returns + @retval FALSE if there is no match + @retval TRUE if there is a partial match +*/ + +Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key) +{ + /* Set of the keys (columns) that form a partial match. */ + Set matching_keys = {} + /* A subset of all keys that need to be checked for NULL matches. */ + Set null_keys = {} + Int min_key /* Key that contains the current minimum position. */ + Int min_row /* Current row number of min_key. */ + Int cur_min_key, cur_min_row + PriorityQueue pq + + if (nonull_key && ! nonull_key->lookup(outer_ref)) + return FALSE + + for (i = 1; i <= n; i++) + { + vkey[i].lookup(outer_ref) + if (! vkey[i].is_eof()) + pq.insert(i) + } + /* + Not all value keys are empty, thus we don't have only NULL + keys. If we had, the only possible match is a NULL row, and + we cheked there is no such row, therefore the result is known + to be FALSE. + In fact this algorithm makes sense for at least two non-NULL + columns. + */ + assert(pq.elements > 1) + + (min_key, min_row) = pq.pop() + matching_keys.add(min_key) + vkey[min_key].next() + if (! vkey[min_key].is_eof()) + pq.insert(min_key) + + while (TRUE) + { + (cur_min_key, cur_min_row) = pq.pop() + + if (cur_min_row == min_row) + { + matching_keys.add(cur_min_key) + /* There cannot be a complete match, as we already checked for one. */ + assert(matching_keys.elements < n) + } + else if (cur_min_key == nonull_key) + { + /* + The non-NULL key has no corresponding NULL index, so we know for + sure that the row 'min_row' is not a match. + */ + (min_key, min_row) = (cur_min_key, cur_min_row) + matching_keys = {min_key} + } + else + { + assert(cur_min_row > min_row) /* Follows from the use of PQ. */ + null_keys = set_difference(all keys vkey[], matching_keys) + /* + Check if all null_keys contain a NULL at row 'min_row'. The procedure + internally checks all keys in a special precomputed order. A prior + procedure determines an optimal order and a mapping + idx_no -> idx_order (encoded as an array). + */ + if (test_null_row(null_keys, min_row)) + return TRUE + else + { + (min_key, min_row) = (cur_min_key, cur_min_row) + matching_keys = {min_key} + } + } + + vkey[cur_min_key].next() + if (! vkey[cur_min_key].is_eof()) + pq.insert(cur_min_key) + + if (pq.is_empty()) + { + /* Check the last row of the last column in PQ for NULL matches. */ + null_keys = set_difference(all keys vkey[], matching_keys) + if (test_null_row(null_keys, min_row)) + return TRUE + else + return FALSE + } + } + + /* We should never get here. */ + assert(FALSE) + return FALSE +} + + + +3. Directions for improvement +======================================================================== + Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary -=-=(Timour - Sun, 06 Dec 2009, 14:36)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.12919 2009-12-06 14:36:18.000000000 +0200 +++ /tmp/wklog.68.new.12919 2009-12-06 14:36:18.000000000 +0200 @@ -87,3 +87,8 @@ 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. +8. [timour] + Consider that due to materialization, we already have a unique index +on all columns <a_1,..., a_n>. We can use the first key part of this index +over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid +creating the index rowid{a_i=v_i}. -=-=(Timour - Fri, 04 Dec 2009, 14:04)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.16724 2009-12-04 14:04:28.000000000 +0200 +++ /tmp/wklog.68.new.16724 2009-12-04 14:04:28.000000000 +0200 @@ -10,7 +10,8 @@ Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint - (2) For each i: rowid{a_i is null} is the same for each tuple + (2) For each i: rowid{a_i is null} is the same for each tuple, + that is, this set is independent of the left-side tuples. Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Version updated. --- /tmp/wklog.68.old.5257 2009-12-04 11:27:11.000000000 +0200 +++ /tmp/wklog.68.new.5257 2009-12-04 11:27:11.000000000 +0200 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Category updated. --- /tmp/wklog.68.old.5242 2009-12-04 11:27:02.000000000 +0200 +++ /tmp/wklog.68.new.5242 2009-12-04 11:27:02.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Status updated. --- /tmp/wklog.68.old.5242 2009-12-04 11:27:02.000000000 +0200 +++ /tmp/wklog.68.new.5242 2009-12-04 11:27:02.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Timour - Fri, 04 Dec 2009, 11:26)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.5182 2009-12-04 11:26:25.000000000 +0200 +++ /tmp/wklog.68.new.5182 2009-12-04 11:26:25.000000000 +0200 @@ -50,23 +50,39 @@ The array can be created on demand. Other consideration that may be taken into account: + 1. If columns a_j1,...,a_jm do not contain null values in the temporary -table at all, create for them only one index array (and of course do not -create any bitmaps for them). -2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows -where a_i is not null and V(a_i) is the number of distinct values for -a_i excluding nulls. - If d(a_i) is close to 1 then do not create any index array: check +table at all and v_j1,...,v_jm cannot be null, create for these columns +only one index array (and of course do not create any bitmaps for them). + +2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number +of rows, where a_i is not null and V(a_i) is the number of distinct +values for a_i excluding nulls. +If d(a_i) is close to N'(a_i) then do not create any index array: check whether there is a match running through the records that have been -filtered in. Anyway if d(a_i) is close to 1 then a intersection with -rowid{a_i=v_i} would not reduce the number of remaining rowids +filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection + with rowid{a_i=v_i} will not reduce the number of remaining rowids significantly. - If additionally N-N' is small do not create a bitmap for this column -either. -3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a -sorted array of rowids from the set rowid{a_i is null} can be used -instead of a bitmap. +In other words is V(a_i) exceeds some threshold there is no sense to +create an index for a_i. +If additionally N-N'(a_i) is small do not create a bitmap for this +column either. + +3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is +small a sorted array of rowids from the set rowid{a_i is null} can be +used instead of a bitmap. + 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. + +5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be +created only for rows with nulls. + +6. If v1,...,vn never can be a null and number of rows with nulls is +small do not create indexes and do not create bitmaps. + +7. If you get a row with nulls in all columns stop filling the temporary +table and return UNKNOWN for any tuple <v1,...,vn>. + ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=68&nolimit=1 DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. HIGH-LEVEL SPECIFICATION: Contents ======================================================================== 1. Initial idea as proposed by Igor 2. Algorithm for IN execution with partial matching 3. Directions for improvement 1. Initial idea as proposed by Igor ======================================================================== For each left side tuple (v_1,...,v_n) we have to find the following set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs trough all indexes from [1..n] such that v_i is not null. Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint (2) For each i: rowid{a_i is null} is the same for each tuple, that is, this set is independent of the left-side tuples. Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap Taken all above into account I could suggest the following algorithm to build R: Using indexes (read about them below) for each column participating in the intersection, merge ordered sets rowid{a_i=v_i} in the following manner. If a rowid r has been encountered maximum in k sets rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can infer that for any r from R indexes a_i can be uniquely divided into two groups: - one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, - the other contains indexes a_j such that r belongs to rowid{a_j is null}. Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order needed for the merge procedure. We could use BTREE indexes for temp table. But they are rather expensive and take a lot of memory as the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. 2. Algorithm for IN execution with partial matching ======================================================================== 2.1 Below is shown the top-level algorithm to execute an IN predicate with partial matching. This algorithm is essentially the implementation of Item_subselect:exec(). int lookup_with_null_semantics(outer_ref[], mat_subquery) { if (index_lookup(outer_ref, mat_subquery) return TRUE else { /* Check if there is a partial match (UNKNOWN) or no match (NULL). */ if (this is the first partial match) { vkey[] = build array of value keys for each NULL-able column of mat_subquery. nkey[] = build a bitmap NULL index for each column of mat_subquery that contains NULLs nonull_key = build a key over all non-NULL columns of mat_subquery } if (partial_match(outer_ref, vkey[], nkey[], nonull_key) return UNKNOWN else return FALSE } } 2.2 The implementation of partial matching is as follows /* Assumptions: - It has already been checked if there is a complete match by a regular index lookup, and the test failed. - It has already been checked if there is a complete NULL row, and if there was we wouldn't call this function. Thus we assume that there is no complete NULL row. - Not all vidx_i are empty, but some can be empty. If all were empty, then the only possibility for a match is a complete NULL row, which we already checked. @param outer_ref - the uter (left) IN argument. @param vidx[] - array of value keys Ordered sequences of rowids of the corresponding columns a_i, such that all rowids in idx_i are the ones where column a_i contains some value or NULL. Each idx_i is derived dynamically, for each different left argument of an IN predicate. @param nidx[] - array of NULL keys Bitmpas, one per each column, where a bit is set if the corresponding row has a NULL value for the corresponding column. @nonull_key - the only key over all columns of the materialized subquery that do not contain NULLs @returns @retval FALSE if there is no match @retval TRUE if there is a partial match */ Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key) { /* Set of the keys (columns) that form a partial match. */ Set matching_keys = {} /* A subset of all keys that need to be checked for NULL matches. */ Set null_keys = {} Int min_key /* Key that contains the current minimum position. */ Int min_row /* Current row number of min_key. */ Int cur_min_key, cur_min_row PriorityQueue pq if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE for (i = 1; i <= n; i++) { if (vkey[i] != nonull_key) vkey[i].lookup(outer_ref) if (! vkey[i].is_eof()) pq.insert(i) } /* Not all value keys are empty, thus we don't have only NULL keys. If we had, the only possible match is a NULL row, and we cheked there is no such row, therefore the result is known to be FALSE. In fact this algorithm makes sense for at least two non-NULL columns. */ assert(pq.elements > 1) (min_key, min_row) = pq.pop() matching_keys.add(min_key) vkey[min_key].next() if (! vkey[min_key].is_eof()) pq.insert(min_key) while (TRUE) { (cur_min_key, cur_min_row) = pq.pop() if (cur_min_row == min_row) { matching_keys.add(cur_min_key) /* There cannot be a complete match, as we already checked for one. */ assert(matching_keys.elements < n) } else if (vkey[cur_min_key] == nonull_key) { /* The non-NULL key has no corresponding NULL index, so we know for sure that the row 'min_row' is not a match. */ (min_key, min_row) = (cur_min_key, cur_min_row) matching_keys = {min_key} } else { assert(cur_min_row > min_row) /* Follows from the use of PQ. */ null_keys = set_difference(all keys vkey[], matching_keys) /* Check if all null_keys contain a NULL at row 'min_row'. The procedure internally checks all keys in a special precomputed order. A prior procedure determines an optimal order and a mapping idx_no -> idx_order (encoded as an array). This procedure makes sure not to match the non-NULL column. */ if (test_null_row(null_keys, min_row)) return TRUE else { (min_key, min_row) = (cur_min_key, cur_min_row) matching_keys = {min_key} } } vkey[cur_min_key].next() if (! vkey[cur_min_key].is_eof()) pq.insert(cur_min_key) else if (vkey[cur_min_key] == nonull_key) { /* If there can't be more matches for the nonull_key, we know for sure there is no match, since there is no possible NULL match. */ return FALSE } if (pq.is_empty()) { /* Check the last row of the last column in PQ for NULL matches. */ null_keys = set_difference(all keys vkey[], matching_keys) if (test_null_row(null_keys, min_row)) return TRUE else return FALSE } } /* We should never get here. */ assert(FALSE) return FALSE } 3. Directions for improvement ======================================================================== Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all and v_j1,...,v_jm cannot be null, create for these columns only one index array (and of course do not create any bitmaps for them). [done] 2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number of rows, where a_i is not null and V(a_i) is the number of distinct values for a_i excluding nulls. If d(a_i) is close to N'(a_i) then do not create any index array: check whether there is a match running through the records that have been filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection with rowid{a_i=v_i} will not reduce the number of remaining rowids significantly. In other words is V(a_i) exceeds some threshold there is no sense to create an index for a_i. If additionally N-N'(a_i) is small do not create a bitmap for this column either. 3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is small a sorted array of rowids from the set rowid{a_i is null} can be used instead of a bitmap. 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. 5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be created only for rows with nulls. 6. If v1,...,vn never can be a null and number of rows with nulls is small do not create indexes and do not create bitmaps. 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. [This is wrong, because if we don't fill the whole temp table, there may be some tuple(s) that would match some outer tuple. In such cases, if we stop filling the temp table, we would miss a TRUE result. Having a partial match doesn't preclude us from having a complete match]. 8. [timour] Consider that due to materialization, we already have a unique index on all columns <a_1,..., a_n>. We can use the first key part of this index over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid creating the index rowid{a_i=v_i}. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subquery optimization: Efficient NOT IN execution with NULLs (68)
by worklog-noreply＠askmonty.org 22 Feb '10

22 Feb '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Efficient NOT IN execution with NULLs CREATION DATE..: Fri, 27 Nov 2009, 13:22 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 68 (http://askmonty.org/worklog/?tid=68) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Mon, 22 Feb 2010, 17:39)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.17116 2010-02-22 17:39:48.000000000 +0200 +++ /tmp/wklog.68.new.17116 2010-02-22 17:39:48.000000000 +0200 @@ -233,6 +233,7 @@ 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all and v_j1,...,v_jm cannot be null, create for these columns only one index array (and of course do not create any bitmaps for them). +[done] 2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number of rows, where a_i is not null and V(a_i) is the number of distinct @@ -264,6 +265,10 @@ 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. +[This is wrong, because if we don't fill the whole temp table, there may + be some tuple(s) that would match some outer tuple. In such cases, if we + stop filling the temp table, we would miss a TRUE result. Having a partial + match doesn't preclude us from having a complete match]. 8. [timour] Consider that due to materialization, we already have a unique index -=-=(Timour - Tue, 19 Jan 2010, 18:44)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.22569 2010-01-19 18:44:01.000000000 +0200 +++ /tmp/wklog.68.new.22569 2010-01-19 18:44:01.000000000 +0200 @@ -132,11 +132,10 @@ if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE - if (nonull_key) - pq.insert(nonull_key) for (i = 1; i <= n; i++) { + if (vkey[i] != nonull_key) vkey[i].lookup(outer_ref) if (! vkey[i].is_eof()) pq.insert(i) @@ -167,7 +166,7 @@ /* There cannot be a complete match, as we already checked for one. */ assert(matching_keys.elements < n) } - else if (cur_min_key == nonull_key) + else if (vkey[cur_min_key] == nonull_key) { /* The non-NULL key has no corresponding NULL index, so we know for @@ -183,8 +182,10 @@ /* Check if all null_keys contain a NULL at row 'min_row'. The procedure internally checks all keys in a special precomputed order. A prior - procedure determines an optimal order and a mapping - idx_no -> idx_order (encoded as an array). + procedure determines an optimal order and a mapping idx_no -> idx_order + (encoded as an array). + + This procedure makes sure not to match the non-NULL column. */ if (test_null_row(null_keys, min_row)) return TRUE @@ -198,6 +199,14 @@ vkey[cur_min_key].next() if (! vkey[cur_min_key].is_eof()) pq.insert(cur_min_key) + else if (vkey[cur_min_key] == nonull_key) + { + /* + If there can't be more matches for the nonull_key, we know for sure + there is no match, since there is no possible NULL match. + */ + return FALSE + } if (pq.is_empty()) { @@ -216,7 +225,6 @@ } - 3. Directions for improvement ======================================================================== -=-=(Timour - Tue, 19 Jan 2010, 18:29)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.21045 2010-01-19 18:29:12.000000000 +0200 +++ /tmp/wklog.68.new.21045 2010-01-19 18:29:12.000000000 +0200 @@ -132,6 +132,8 @@ if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE + if (nonull_key) + pq.insert(nonull_key) for (i = 1; i <= n; i++) { -=-=(Guest - Tue, 19 Jan 2010, 18:15)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.19825 2010-01-19 18:15:30.000000000 +0200 +++ /tmp/wklog.68.new.19825 2010-01-19 18:15:30.000000000 +0200 @@ -1,8 +1,16 @@ -This a copy of the initial algorithm proposed by Igor: -====================================================== +Contents +======================================================================== -For each left side tuple (v_1,...,v_n) we have to find the following set -of rowids for the temp table containing N rows as the result of +1. Initial idea as proposed by Igor +2. Algorithm for IN execution with partial matching +3. Directions for improvement + + +1. Initial idea as proposed by Igor +======================================================================== + +For each left side tuple (v_1,...,v_n) we have to find the following +set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs @@ -18,38 +26,198 @@ - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap -Taken all above into account I could suggest the following algorithm to -build R: +Taken all above into account I could suggest the following algorithm +to build R: - Using indexes (read about them below) for each column participating in the - intersection, - merge ordered sets rowid{a_i=v_i} in the following manner. + Using indexes (read about them below) for each column participating + in the intersection, merge ordered sets rowid{a_i=v_i} in the + following manner. If a rowid r has been encountered maximum in k sets -rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), + rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is -not in {i1,...,ik}. + not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. -Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i -is null} is either +Here we use the property (1): +any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can -infer that for any r from R -indexes a_i can be uniquely divided into two groups: one contains -indexes a_i where r belongs to -the sets rowid{a_i=v_i}, the other contains indexes a_j such that r -belongs to rowid{a_j is null}. - -Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order -needed for the merge procedure. We could use BTREE indexes for temp -table. But they are rather expensive and -take a lot of memory as the are implemented with RB trees. +infer that for any r from R indexes a_i can be uniquely divided into +two groups: +- one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, +- the other contains indexes a_j such that r belongs to + rowid{a_j is null}. + +Now let's talk how to get elements from rowid{a_i=v_i} in a sorted +order needed for the merge procedure. We could use BTREE indexes for +temp table. But they are rather expensive and take a lot of memory as +the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. +2. Algorithm for IN execution with partial matching +======================================================================== + +2.1 Below is shown the top-level algorithm to execute an IN predicate +with partial matching. This algorithm is essentially the implementation +of Item_subselect:exec(). + +int lookup_with_null_semantics(outer_ref[], mat_subquery) +{ + if (index_lookup(outer_ref, mat_subquery) + return TRUE + else + { + /* + Check if there is a partial match (UNKNOWN) or no match (NULL). + */ + if (this is the first partial match) + { + vkey[] = build array of value keys for each NULL-able column + of mat_subquery. + nkey[] = build a bitmap NULL index for each column of mat_subquery + that contains NULLs + nonull_key = build a key over all non-NULL columns of mat_subquery + } + if (partial_match(outer_ref, vkey[], nkey[], nonull_key) + return UNKNOWN + else + return FALSE + } +} + +2.2 The implementation of partial matching is as follows + +/* + Assumptions: + - It has already been checked if there is a complete match by a + regular index lookup, and the test failed. + - It has already been checked if there is a complete NULL row, + and if there was we wouldn't call this function. Thus we assume + that there is no complete NULL row. + - Not all vidx_i are empty, but some can be empty. If all were empty, + then the only possibility for a match is a complete NULL row, which + we already checked. + + @param outer_ref - the uter (left) IN argument. + @param vidx[] - array of value keys + Ordered sequences of rowids of the corresponding columns a_i, such + that all rowids in idx_i are the ones where column a_i contains some + value or NULL. Each idx_i is derived dynamically, for each different + left argument of an IN predicate. + @param nidx[] - array of NULL keys + Bitmpas, one per each column, where a bit is set if the corresponding + row has a NULL value for the corresponding column. + @nonull_key - the only key over all columns of the materialized subquery + that do not contain NULLs + + @returns + @retval FALSE if there is no match + @retval TRUE if there is a partial match +*/ + +Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key) +{ + /* Set of the keys (columns) that form a partial match. */ + Set matching_keys = {} + /* A subset of all keys that need to be checked for NULL matches. */ + Set null_keys = {} + Int min_key /* Key that contains the current minimum position. */ + Int min_row /* Current row number of min_key. */ + Int cur_min_key, cur_min_row + PriorityQueue pq + + if (nonull_key && ! nonull_key->lookup(outer_ref)) + return FALSE + + for (i = 1; i <= n; i++) + { + vkey[i].lookup(outer_ref) + if (! vkey[i].is_eof()) + pq.insert(i) + } + /* + Not all value keys are empty, thus we don't have only NULL + keys. If we had, the only possible match is a NULL row, and + we cheked there is no such row, therefore the result is known + to be FALSE. + In fact this algorithm makes sense for at least two non-NULL + columns. + */ + assert(pq.elements > 1) + + (min_key, min_row) = pq.pop() + matching_keys.add(min_key) + vkey[min_key].next() + if (! vkey[min_key].is_eof()) + pq.insert(min_key) + + while (TRUE) + { + (cur_min_key, cur_min_row) = pq.pop() + + if (cur_min_row == min_row) + { + matching_keys.add(cur_min_key) + /* There cannot be a complete match, as we already checked for one. */ + assert(matching_keys.elements < n) + } + else if (cur_min_key == nonull_key) + { + /* + The non-NULL key has no corresponding NULL index, so we know for + sure that the row 'min_row' is not a match. + */ + (min_key, min_row) = (cur_min_key, cur_min_row) + matching_keys = {min_key} + } + else + { + assert(cur_min_row > min_row) /* Follows from the use of PQ. */ + null_keys = set_difference(all keys vkey[], matching_keys) + /* + Check if all null_keys contain a NULL at row 'min_row'. The procedure + internally checks all keys in a special precomputed order. A prior + procedure determines an optimal order and a mapping + idx_no -> idx_order (encoded as an array). + */ + if (test_null_row(null_keys, min_row)) + return TRUE + else + { + (min_key, min_row) = (cur_min_key, cur_min_row) + matching_keys = {min_key} + } + } + + vkey[cur_min_key].next() + if (! vkey[cur_min_key].is_eof()) + pq.insert(cur_min_key) + + if (pq.is_empty()) + { + /* Check the last row of the last column in PQ for NULL matches. */ + null_keys = set_difference(all keys vkey[], matching_keys) + if (test_null_row(null_keys, min_row)) + return TRUE + else + return FALSE + } + } + + /* We should never get here. */ + assert(FALSE) + return FALSE +} + + + +3. Directions for improvement +======================================================================== + Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary -=-=(Timour - Sun, 06 Dec 2009, 14:36)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.12919 2009-12-06 14:36:18.000000000 +0200 +++ /tmp/wklog.68.new.12919 2009-12-06 14:36:18.000000000 +0200 @@ -87,3 +87,8 @@ 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. +8. [timour] + Consider that due to materialization, we already have a unique index +on all columns <a_1,..., a_n>. We can use the first key part of this index +over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid +creating the index rowid{a_i=v_i}. -=-=(Timour - Fri, 04 Dec 2009, 14:04)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.16724 2009-12-04 14:04:28.000000000 +0200 +++ /tmp/wklog.68.new.16724 2009-12-04 14:04:28.000000000 +0200 @@ -10,7 +10,8 @@ Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint - (2) For each i: rowid{a_i is null} is the same for each tuple + (2) For each i: rowid{a_i is null} is the same for each tuple, + that is, this set is independent of the left-side tuples. Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Version updated. --- /tmp/wklog.68.old.5257 2009-12-04 11:27:11.000000000 +0200 +++ /tmp/wklog.68.new.5257 2009-12-04 11:27:11.000000000 +0200 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Category updated. --- /tmp/wklog.68.old.5242 2009-12-04 11:27:02.000000000 +0200 +++ /tmp/wklog.68.new.5242 2009-12-04 11:27:02.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Timour - Fri, 04 Dec 2009, 11:27)=-=- Status updated. --- /tmp/wklog.68.old.5242 2009-12-04 11:27:02.000000000 +0200 +++ /tmp/wklog.68.new.5242 2009-12-04 11:27:02.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Timour - Fri, 04 Dec 2009, 11:26)=-=- High-Level Specification modified. --- /tmp/wklog.68.old.5182 2009-12-04 11:26:25.000000000 +0200 +++ /tmp/wklog.68.new.5182 2009-12-04 11:26:25.000000000 +0200 @@ -50,23 +50,39 @@ The array can be created on demand. Other consideration that may be taken into account: + 1. If columns a_j1,...,a_jm do not contain null values in the temporary -table at all, create for them only one index array (and of course do not -create any bitmaps for them). -2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows -where a_i is not null and V(a_i) is the number of distinct values for -a_i excluding nulls. - If d(a_i) is close to 1 then do not create any index array: check +table at all and v_j1,...,v_jm cannot be null, create for these columns +only one index array (and of course do not create any bitmaps for them). + +2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number +of rows, where a_i is not null and V(a_i) is the number of distinct +values for a_i excluding nulls. +If d(a_i) is close to N'(a_i) then do not create any index array: check whether there is a match running through the records that have been -filtered in. Anyway if d(a_i) is close to 1 then a intersection with -rowid{a_i=v_i} would not reduce the number of remaining rowids +filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection + with rowid{a_i=v_i} will not reduce the number of remaining rowids significantly. - If additionally N-N' is small do not create a bitmap for this column -either. -3. If for a column a_i d(a_i) is not close to 1, but N-N' is small a -sorted array of rowids from the set rowid{a_i is null} can be used -instead of a bitmap. +In other words is V(a_i) exceeds some threshold there is no sense to +create an index for a_i. +If additionally N-N'(a_i) is small do not create a bitmap for this +column either. + +3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is +small a sorted array of rowids from the set rowid{a_i is null} can be +used instead of a bitmap. + 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. + +5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be +created only for rows with nulls. + +6. If v1,...,vn never can be a null and number of rows with nulls is +small do not create indexes and do not create bitmaps. + +7. If you get a row with nulls in all columns stop filling the temporary +table and return UNKNOWN for any tuple <v1,...,vn>. + ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=68&nolimit=1 DESCRIPTION: The goal of this task is to implement efficient execution of NOT IN subquery predicates of the form: <oe_1,...,oe_n> NOT IN <non_correlated subquery> when either some oe_i, or some subqury result column contains NULLs. The problem with such predicates is that it is possible to use index lookups only when neither argument of the predicate contains NULLs. If some argument contains a NULL, then due to NULL semantics, it plays the role of a wildcard. If we were to use regular index lookups, then we would get 'no match' for some outer tuple (thus the predicate evaluates to FALSE), while the SQL semantics means 'partial match', and the predicate should evaluate to NULL. This task implements an efficient algorithm to compute such 'parial matches', where a NULL matches any value. HIGH-LEVEL SPECIFICATION: Contents ======================================================================== 1. Initial idea as proposed by Igor 2. Algorithm for IN execution with partial matching 3. Directions for improvement 1. Initial idea as proposed by Igor ======================================================================== For each left side tuple (v_1,...,v_n) we have to find the following set of rowids for the temp table containing N rows as the result of materialization of the subquery: R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs trough all indexes from [1..n] such that v_i is not null. Bear in mind the following specifics of this intersection: (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint (2) For each i: rowid{a_i is null} is the same for each tuple, that is, this set is independent of the left-side tuples. Due to (2) it makes sense to build rowid{a_i is null} only once. A good representation for such sets would be bitmaps: - it requires minimum memory: not more than N*n bits in total - search of an element in a set is extremely cheap Taken all above into account I could suggest the following algorithm to build R: Using indexes (read about them below) for each column participating in the intersection, merge ordered sets rowid{a_i=v_i} in the following manner. If a rowid r has been encountered maximum in k sets rowid{a_i1=v_i1},...,rowid(a_ik=v_ik), then it has to be checked against all rowid{a_i=v_i} such that i is not in {i1,...,ik}. As soon as we fail to find r in one of these sets we discard it. If r has been found in all of them then r belongs to the set R. Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can infer that for any r from R indexes a_i can be uniquely divided into two groups: - one contains indexes a_i where r belongs to the sets rowid{a_i=v_i}, - the other contains indexes a_j such that r belongs to rowid{a_j is null}. Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order needed for the merge procedure. We could use BTREE indexes for temp table. But they are rather expensive and take a lot of memory as the are implemented with RB trees. I would suggest creating for each column from the temporary table just an array of rowids sorted by the value from column a. Index lookup in such an array is cheap. It's also rather cheap to check that the next rowid refers to a row with a different value in column a. The array can be created on demand. 2. Algorithm for IN execution with partial matching ======================================================================== 2.1 Below is shown the top-level algorithm to execute an IN predicate with partial matching. This algorithm is essentially the implementation of Item_subselect:exec(). int lookup_with_null_semantics(outer_ref[], mat_subquery) { if (index_lookup(outer_ref, mat_subquery) return TRUE else { /* Check if there is a partial match (UNKNOWN) or no match (NULL). */ if (this is the first partial match) { vkey[] = build array of value keys for each NULL-able column of mat_subquery. nkey[] = build a bitmap NULL index for each column of mat_subquery that contains NULLs nonull_key = build a key over all non-NULL columns of mat_subquery } if (partial_match(outer_ref, vkey[], nkey[], nonull_key) return UNKNOWN else return FALSE } } 2.2 The implementation of partial matching is as follows /* Assumptions: - It has already been checked if there is a complete match by a regular index lookup, and the test failed. - It has already been checked if there is a complete NULL row, and if there was we wouldn't call this function. Thus we assume that there is no complete NULL row. - Not all vidx_i are empty, but some can be empty. If all were empty, then the only possibility for a match is a complete NULL row, which we already checked. @param outer_ref - the uter (left) IN argument. @param vidx[] - array of value keys Ordered sequences of rowids of the corresponding columns a_i, such that all rowids in idx_i are the ones where column a_i contains some value or NULL. Each idx_i is derived dynamically, for each different left argument of an IN predicate. @param nidx[] - array of NULL keys Bitmpas, one per each column, where a bit is set if the corresponding row has a NULL value for the corresponding column. @nonull_key - the only key over all columns of the materialized subquery that do not contain NULLs @returns @retval FALSE if there is no match @retval TRUE if there is a partial match */ Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key) { /* Set of the keys (columns) that form a partial match. */ Set matching_keys = {} /* A subset of all keys that need to be checked for NULL matches. */ Set null_keys = {} Int min_key /* Key that contains the current minimum position. */ Int min_row /* Current row number of min_key. */ Int cur_min_key, cur_min_row PriorityQueue pq if (nonull_key && ! nonull_key->lookup(outer_ref)) return FALSE for (i = 1; i <= n; i++) { if (vkey[i] != nonull_key) vkey[i].lookup(outer_ref) if (! vkey[i].is_eof()) pq.insert(i) } /* Not all value keys are empty, thus we don't have only NULL keys. If we had, the only possible match is a NULL row, and we cheked there is no such row, therefore the result is known to be FALSE. In fact this algorithm makes sense for at least two non-NULL columns. */ assert(pq.elements > 1) (min_key, min_row) = pq.pop() matching_keys.add(min_key) vkey[min_key].next() if (! vkey[min_key].is_eof()) pq.insert(min_key) while (TRUE) { (cur_min_key, cur_min_row) = pq.pop() if (cur_min_row == min_row) { matching_keys.add(cur_min_key) /* There cannot be a complete match, as we already checked for one. */ assert(matching_keys.elements < n) } else if (vkey[cur_min_key] == nonull_key) { /* The non-NULL key has no corresponding NULL index, so we know for sure that the row 'min_row' is not a match. */ (min_key, min_row) = (cur_min_key, cur_min_row) matching_keys = {min_key} } else { assert(cur_min_row > min_row) /* Follows from the use of PQ. */ null_keys = set_difference(all keys vkey[], matching_keys) /* Check if all null_keys contain a NULL at row 'min_row'. The procedure internally checks all keys in a special precomputed order. A prior procedure determines an optimal order and a mapping idx_no -> idx_order (encoded as an array). This procedure makes sure not to match the non-NULL column. */ if (test_null_row(null_keys, min_row)) return TRUE else { (min_key, min_row) = (cur_min_key, cur_min_row) matching_keys = {min_key} } } vkey[cur_min_key].next() if (! vkey[cur_min_key].is_eof()) pq.insert(cur_min_key) else if (vkey[cur_min_key] == nonull_key) { /* If there can't be more matches for the nonull_key, we know for sure there is no match, since there is no possible NULL match. */ return FALSE } if (pq.is_empty()) { /* Check the last row of the last column in PQ for NULL matches. */ null_keys = set_difference(all keys vkey[], matching_keys) if (test_null_row(null_keys, min_row)) return TRUE else return FALSE } } /* We should never get here. */ assert(FALSE) return FALSE } 3. Directions for improvement ======================================================================== Other consideration that may be taken into account: 1. If columns a_j1,...,a_jm do not contain null values in the temporary table at all and v_j1,...,v_jm cannot be null, create for these columns only one index array (and of course do not create any bitmaps for them). [done] 2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number of rows, where a_i is not null and V(a_i) is the number of distinct values for a_i excluding nulls. If d(a_i) is close to N'(a_i) then do not create any index array: check whether there is a match running through the records that have been filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection with rowid{a_i=v_i} will not reduce the number of remaining rowids significantly. In other words is V(a_i) exceeds some threshold there is no sense to create an index for a_i. If additionally N-N'(a_i) is small do not create a bitmap for this column either. 3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is small a sorted array of rowids from the set rowid{a_i is null} can be used instead of a bitmap. 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not empty. Here i runs through all indexes from [1..n] such that v_i is not null. For a given subset of columns this fact has to be checked only once. It can be easily done with bitmap intersection. 5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be created only for rows with nulls. 6. If v1,...,vn never can be a null and number of rows with nulls is small do not create indexes and do not create bitmaps. 7. If you get a row with nulls in all columns stop filling the temporary table and return UNKNOWN for any tuple <v1,...,vn>. [This is wrong, because if we don't fill the whole temp table, there may be some tuple(s) that would match some outer tuple. In such cases, if we stop filling the temp table, we would miss a TRUE result. Having a partial match doesn't preclude us from having a complete match]. 8. [timour] Consider that due to materialization, we already have a unique index on all columns <a_1,..., a_n>. We can use the first key part of this index over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid creating the index rowid{a_i=v_i}. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2764: MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs in file:///home/tsk/mprog/src/5.3-mwl68-unmerged/
by timour＠askmonty.org 22 Feb '10

22 Feb '10

At file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ ------------------------------------------------------------ revno: 2764 revision-id: timour(a)askmonty.org-20100222151655-ltjv0rlv6z2sdiiu parent: timour(a)askmonty.org-20100222135709-3568ya6z76hkwfzs committer: timour(a)askmonty.org branch nick: 5.3-mwl68-unmerged timestamp: Mon 2010-02-22 17:16:55 +0200 message: MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs This patch mainly adds sorting of all indexes for partial matching according to their NULL selectivity. The patch also fixes a related bug in subselect_rowid_merge_engine::test_null_row() where the wrong matched indexes were skipped. In addition the patch: - adds few ::print() methods, - renames few variables that had similar names but different purpose. === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-22 13:57:09 +0000 +++ b/sql/item_subselect.cc 2010-02-22 15:16:55 +0000 @@ -3424,7 +3424,10 @@ DBUG_ENTER("subselect_hash_sj_engine::set_strategy_using_schema"); if (item_in->is_top_level_item()) + { strategy= COMPLETE_MATCH; + DBUG_VOID_RETURN; + } else { List_iterator<Item> inner_col_it(*item_in->unit->get_unit_column_types()); @@ -3846,6 +3849,7 @@ thd->lex->current_select= materialize_engine->select_lex; if ((res= materialize_join->optimize())) goto err; /* purecov: inspected */ + DBUG_ASSERT(!is_materialized); /* We should materialize only once. */ materialize_join->exec(); if ((res= test(materialize_join->error || thd->is_fatal_error))) goto err; @@ -3952,7 +3956,7 @@ lookup_engine->print(str, query_type); else str->append(STRING_WITH_LEN( - "<the access method for lookups is not yet created>" + "<engine selected at execution time>" )); } @@ -3980,11 +3984,10 @@ } -Ordered_key::Ordered_key(uint key_idx_arg, TABLE *tbl_arg, - Item *search_key_arg, ha_rows null_count_arg, - ha_rows min_null_row_arg, ha_rows max_null_row_arg, - uchar *row_num_to_rowid_arg) - : key_idx(key_idx_arg), tbl(tbl_arg), search_key(search_key_arg), +Ordered_key::Ordered_key(uint keyid_arg, TABLE *tbl_arg, Item *search_key_arg, + ha_rows null_count_arg, ha_rows min_null_row_arg, + ha_rows max_null_row_arg, uchar *row_num_to_rowid_arg) + : keyid(keyid_arg), tbl(tbl_arg), search_key(search_key_arg), row_num_to_rowid(row_num_to_rowid_arg), null_count(null_count_arg) { DBUG_ASSERT(tbl->file->stats.records > null_count); @@ -4190,6 +4193,21 @@ /* + The probability that a certain row does not contain a NULL in some row in + a NULL-indexed column. + @retval 1 if there are no NULLs + @retval 0 if only NULLs +*/ + +double Ordered_key::null_selectivity() +{ + /* We should not be processing empty tables. */ + DBUG_ASSERT(tbl->file->stats.records); + return (1 - (double) null_count / (double) tbl->file->stats.records); +} + + +/* Compare the value(s) of the current key in 'search_key' with the data of the current table record. @@ -4307,6 +4325,34 @@ } +void Ordered_key::print(String *str) +{ + uint i; + str->append("{idx="); + str->qs_append(keyid); + str->append(", ("); + for (i= 0; i < key_column_count - 1; i++) + { + str->append(key_columns[i]->field->field_name); + str->append(", "); + } + str->append(key_columns[i]->field->field_name); + str->append("), "); + + str->append("null_bitmap: (bits="); + str->qs_append(null_key.n_bits); + str->append(", nulls= "); + str->qs_append((double)null_count); + str->append(", min_null= "); + str->qs_append((double)min_null_row); + str->append(", max_null= "); + str->qs_append((double)max_null_row); + str->append("), "); + + str->append('}'); +} + + /* @param non_null_key_parts @param partial_match_key_parts A union of all single-column NULL key parts. @@ -4323,7 +4369,7 @@ rownum_t cur_rownum= 0; select_materialize_with_stats *result_sink= (select_materialize_with_stats *) result; - uint cur_key= 0; + uint cur_keyid= 0; Item_in_subselect *item_in= (Item_in_subselect*) item; int error; @@ -4346,16 +4392,16 @@ /* Create the only non-NULL key if there is any. */ if (non_null_key_parts) { - non_null_key= new Ordered_key(cur_key, tmp_table, item_in->left_expr, + non_null_key= new Ordered_key(cur_keyid, tmp_table, item_in->left_expr, 0, 0, 0, row_num_to_rowid); if (non_null_key->init(non_null_key_parts)) { // TIMOUR: revert to partial matching via scanning return TRUE; } - merge_keys[cur_key]= non_null_key; - merge_keys[cur_key]->first(); - ++cur_key; + merge_keys[cur_keyid]= non_null_key; + merge_keys[cur_keyid]->first(); + ++cur_keyid; } /* @@ -4379,23 +4425,24 @@ continue; if (result_sink->get_null_count_of_col(i) == row_count) - bitmap_set_bit(&null_only_columns, cur_key); + bitmap_set_bit(&null_only_columns, cur_keyid); else { - merge_keys[cur_key]= new Ordered_key(cur_key, tmp_table, - item_in->left_expr->element_index(i), - result_sink->get_null_count_of_col(i), - result_sink->get_min_null_of_col(i), - result_sink->get_max_null_of_col(i), - row_num_to_rowid); - if (merge_keys[cur_key]->init(i)) + merge_keys[cur_keyid]= new Ordered_key( + cur_keyid, tmp_table, + item_in->left_expr->element_index(i), + result_sink->get_null_count_of_col(i), + result_sink->get_min_null_of_col(i), + result_sink->get_max_null_of_col(i), + row_num_to_rowid); + if (merge_keys[cur_keyid]->init(i)) { // TIMOUR: revert to partial matching via scanning return TRUE; } - merge_keys[cur_key]->first(); + merge_keys[cur_keyid]->first(); } - ++cur_key; + ++cur_keyid; } } @@ -4453,12 +4500,14 @@ tmp_table->file->ha_rnd_end(); + /* Sort all the keys by their NULL selectivity. */ + my_qsort(merge_keys, keys_count, sizeof(Ordered_key*), + (qsort_cmp) cmp_keys_by_null_selectivity); + /* Sort the keys in each of the indexes. */ for (uint i= 0; i < keys_count; i++) merge_keys[i]->sort_keys(); - // TIMOUR: sort all the keys by NULL selectivity - if (init_queue(&pq, keys_count, 0, FALSE, subselect_rowid_merge_engine::cmp_keys_by_cur_rownum, NULL)) { @@ -4486,20 +4535,38 @@ } +void subselect_rowid_merge_engine::print(String *str, enum_query_type query_type) +{ + str->append(STRING_WITH_LEN("<rowid_merge>(")); + for (uint i= 0; i < keys_count; i++) + merge_keys[i]->print(str); + str->append(')'); +} + + /* + Quick sort comparison function to compare keys in order of decreasing bitmap + selectivity, so that the most selective keys come first. + + @param k1 first key to compare + @param k2 second key to compare + + @retval 1 if k1 is less selective than k2 + @retval 0 if k1 is equally selective as k2 + @retval -1 if k1 is more selective than k2 */ int -subselect_rowid_merge_engine::cmp_keys_by_null_selectivity(Ordered_key *a, - Ordered_key *b) +subselect_rowid_merge_engine::cmp_keys_by_null_selectivity(Ordered_key **k1, + Ordered_key **k2) { - double a_sel= a->null_selectivity(); - double b_sel= b->null_selectivity(); - if (a_sel == b_sel) - return 0; - if (a_sel > b_sel) + double k1_sel= (*k1)->null_selectivity(); + double k2_sel= (*k2)->null_selectivity(); + if (k1_sel < k2_sel) return 1; - return -1; + if (k1_sel > k2_sel) + return -1; + return 0; } @@ -4527,17 +4594,21 @@ bool subselect_rowid_merge_engine::test_null_row(rownum_t row_num) { + Ordered_key *cur_key; + uint cur_id; for (uint i = 0; i < keys_count; i++) { - if (bitmap_is_set(&matching_keys, i)) + cur_key= merge_keys[i]; + cur_id= cur_key->get_keyid(); + if (bitmap_is_set(&matching_keys, cur_id)) { /* - The key 'i' already matches a value in row 'row_num', thus we - skip it as it can't possibly match a NULL. + The key 'i' (with id 'cur_keyid') already matches a value in row 'row_num', + thus we skip it as it can't possibly match a NULL. */ continue; } - if (!merge_keys[i]->is_null(row_num)) + if (!cur_key->is_null(row_num)) return FALSE; } return TRUE; @@ -4583,7 +4654,7 @@ if (merge_keys[i]->get_search_key(0)->is_null()) { ++count_nulls_in_search_key; - bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_key_idx()); + bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_keyid()); } else if (merge_keys[i]->lookup()) queue_insert(&pq, (uchar *) merge_keys[i]); @@ -4610,7 +4681,7 @@ min_key= (Ordered_key*) queue_remove(&pq, 0); min_row_num= min_key->current(); bitmap_copy(&matching_keys, &null_only_columns); - bitmap_set_bit(&matching_keys, min_key->get_key_idx()); + bitmap_set_bit(&matching_keys, min_key->get_keyid()); bitmap_union(&matching_keys, &matching_outer_cols); if (min_key->next_same()) queue_insert(&pq, (uchar *) min_key); @@ -4633,7 +4704,7 @@ cur_row_num= cur_key->current(); if (cur_row_num == min_row_num) - bitmap_set_bit(&matching_keys, cur_key->get_key_idx()); + bitmap_set_bit(&matching_keys, cur_key->get_keyid()); else { /* Follows from the correct use of priority queue. */ @@ -4645,7 +4716,7 @@ min_key= cur_key; min_row_num= cur_row_num; bitmap_copy(&matching_keys, &null_only_columns); - bitmap_set_bit(&matching_keys, min_key->get_key_idx()); + bitmap_set_bit(&matching_keys, min_key->get_keyid()); bitmap_union(&matching_keys, &matching_outer_cols); } } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-02-19 21:55:57 +0000 +++ b/sql/item_subselect.h 2010-02-22 15:16:55 +0000 @@ -752,7 +752,7 @@ Index of the key in an array of keys. This index allows to construct (sub)sets of keys represented by bitmaps. */ - uint key_idx; + uint keyid; /* The table being indexed. */ TABLE *tbl; /* The columns being indexed. */ @@ -810,7 +810,7 @@ public: static void *operator new(size_t size) throw () { return sql_alloc(size); } - Ordered_key(uint key_idx_arg, TABLE *tbl_arg, + Ordered_key(uint keyid_arg, TABLE *tbl_arg, Item *search_key_arg, ha_rows null_count_arg, ha_rows min_null_row_arg, ha_rows max_null_row_arg, uchar *row_num_to_rowid_arg); @@ -822,7 +822,7 @@ bool init(int col_idx); uint get_column_count() { return key_column_count; } - uint get_key_idx() { return key_idx; } + uint get_keyid() { return keyid; } uint get_field_idx(uint i) { DBUG_ASSERT(i < key_column_count); @@ -841,8 +841,7 @@ } void sort_keys(); - - double null_selectivity() { return (1 - null_count / null_key.n_bits); } + double null_selectivity(); /* Position the current element at the first row that matches the key. @@ -896,6 +895,7 @@ return FALSE; return bitmap_is_set(&null_key, row_num); } + void print(String *str); }; @@ -957,10 +957,10 @@ bool has_covering_null_row; protected: /* - Comparison function to compare keys in order of increasing bitmap + Comparison function to compare keys in order of decreasing bitmap selectivity. */ - static int cmp_keys_by_null_selectivity(Ordered_key *a, Ordered_key *b); + static int cmp_keys_by_null_selectivity(Ordered_key **k1, Ordered_key **k2); /* Comparison function used by the priority queue pq, the 'smaller' key is the one with the smaller current row number. @@ -992,7 +992,7 @@ uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude() {} table_map upper_select_const_tables() { return 0; } - void print(String*, enum_query_type) {} + void print(String*, enum_query_type); bool change_result(Item_subselect*, select_result_interceptor*) { DBUG_ASSERT(FALSE); return false; } bool no_tables() { return false; }

1 0

[Maria-developers] bzr commit into file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ branch (timour:2764)
by timour＠askmonty.org 22 Feb '10

22 Feb '10

#At file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ based on revid:timour@askmonty.org-20100222135709-3568ya6z76hkwfzs 2764 timour(a)askmonty.org 2010-02-22 MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs This patch mainly adds sorting of all indexes for partial matching according to their NULL selectivity. The patch also fixes a related bug in subselect_rowid_merge_engine::test_null_row() where the wrong matched indexes were skipped. In addition the patch: - adds few ::print() methods, - renames few variables that had similar names but different purpose. modified: sql/item_subselect.cc sql/item_subselect.h === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-22 13:57:09 +0000 +++ b/sql/item_subselect.cc 2010-02-22 15:16:55 +0000 @@ -3424,7 +3424,10 @@ void subselect_hash_sj_engine::set_strat DBUG_ENTER("subselect_hash_sj_engine::set_strategy_using_schema"); if (item_in->is_top_level_item()) + { strategy= COMPLETE_MATCH; + DBUG_VOID_RETURN; + } else { List_iterator<Item> inner_col_it(*item_in->unit->get_unit_column_types()); @@ -3846,6 +3849,7 @@ int subselect_hash_sj_engine::exec() thd->lex->current_select= materialize_engine->select_lex; if ((res= materialize_join->optimize())) goto err; /* purecov: inspected */ + DBUG_ASSERT(!is_materialized); /* We should materialize only once. */ materialize_join->exec(); if ((res= test(materialize_join->error || thd->is_fatal_error))) goto err; @@ -3952,7 +3956,7 @@ void subselect_hash_sj_engine::print(Str lookup_engine->print(str, query_type); else str->append(STRING_WITH_LEN( - "<the access method for lookups is not yet created>" + "<engine selected at execution time>" )); } @@ -3980,11 +3984,10 @@ bool subselect_hash_sj_engine::change_re } -Ordered_key::Ordered_key(uint key_idx_arg, TABLE *tbl_arg, - Item *search_key_arg, ha_rows null_count_arg, - ha_rows min_null_row_arg, ha_rows max_null_row_arg, - uchar *row_num_to_rowid_arg) - : key_idx(key_idx_arg), tbl(tbl_arg), search_key(search_key_arg), +Ordered_key::Ordered_key(uint keyid_arg, TABLE *tbl_arg, Item *search_key_arg, + ha_rows null_count_arg, ha_rows min_null_row_arg, + ha_rows max_null_row_arg, uchar *row_num_to_rowid_arg) + : keyid(keyid_arg), tbl(tbl_arg), search_key(search_key_arg), row_num_to_rowid(row_num_to_rowid_arg), null_count(null_count_arg) { DBUG_ASSERT(tbl->file->stats.records > null_count); @@ -4190,6 +4193,21 @@ void Ordered_key::sort_keys() /* + The probability that a certain row does not contain a NULL in some row in + a NULL-indexed column. + @retval 1 if there are no NULLs + @retval 0 if only NULLs +*/ + +double Ordered_key::null_selectivity() +{ + /* We should not be processing empty tables. */ + DBUG_ASSERT(tbl->file->stats.records); + return (1 - (double) null_count / (double) tbl->file->stats.records); +} + + +/* Compare the value(s) of the current key in 'search_key' with the data of the current table record. @@ -4307,6 +4325,34 @@ bool Ordered_key::next_same() } +void Ordered_key::print(String *str) +{ + uint i; + str->append("{idx="); + str->qs_append(keyid); + str->append(", ("); + for (i= 0; i < key_column_count - 1; i++) + { + str->append(key_columns[i]->field->field_name); + str->append(", "); + } + str->append(key_columns[i]->field->field_name); + str->append("), "); + + str->append("null_bitmap: (bits="); + str->qs_append(null_key.n_bits); + str->append(", nulls= "); + str->qs_append((double)null_count); + str->append(", min_null= "); + str->qs_append((double)min_null_row); + str->append(", max_null= "); + str->qs_append((double)max_null_row); + str->append("), "); + + str->append('}'); +} + + /* @param non_null_key_parts @param partial_match_key_parts A union of all single-column NULL key parts. @@ -4323,7 +4369,7 @@ subselect_rowid_merge_engine::init(MY_BI rownum_t cur_rownum= 0; select_materialize_with_stats *result_sink= (select_materialize_with_stats *) result; - uint cur_key= 0; + uint cur_keyid= 0; Item_in_subselect *item_in= (Item_in_subselect*) item; int error; @@ -4346,16 +4392,16 @@ subselect_rowid_merge_engine::init(MY_BI /* Create the only non-NULL key if there is any. */ if (non_null_key_parts) { - non_null_key= new Ordered_key(cur_key, tmp_table, item_in->left_expr, + non_null_key= new Ordered_key(cur_keyid, tmp_table, item_in->left_expr, 0, 0, 0, row_num_to_rowid); if (non_null_key->init(non_null_key_parts)) { // TIMOUR: revert to partial matching via scanning return TRUE; } - merge_keys[cur_key]= non_null_key; - merge_keys[cur_key]->first(); - ++cur_key; + merge_keys[cur_keyid]= non_null_key; + merge_keys[cur_keyid]->first(); + ++cur_keyid; } /* @@ -4379,23 +4425,24 @@ subselect_rowid_merge_engine::init(MY_BI continue; if (result_sink->get_null_count_of_col(i) == row_count) - bitmap_set_bit(&null_only_columns, cur_key); + bitmap_set_bit(&null_only_columns, cur_keyid); else { - merge_keys[cur_key]= new Ordered_key(cur_key, tmp_table, - item_in->left_expr->element_index(i), - result_sink->get_null_count_of_col(i), - result_sink->get_min_null_of_col(i), - result_sink->get_max_null_of_col(i), - row_num_to_rowid); - if (merge_keys[cur_key]->init(i)) + merge_keys[cur_keyid]= new Ordered_key( + cur_keyid, tmp_table, + item_in->left_expr->element_index(i), + result_sink->get_null_count_of_col(i), + result_sink->get_min_null_of_col(i), + result_sink->get_max_null_of_col(i), + row_num_to_rowid); + if (merge_keys[cur_keyid]->init(i)) { // TIMOUR: revert to partial matching via scanning return TRUE; } - merge_keys[cur_key]->first(); + merge_keys[cur_keyid]->first(); } - ++cur_key; + ++cur_keyid; } } @@ -4453,12 +4500,14 @@ subselect_rowid_merge_engine::init(MY_BI tmp_table->file->ha_rnd_end(); + /* Sort all the keys by their NULL selectivity. */ + my_qsort(merge_keys, keys_count, sizeof(Ordered_key*), + (qsort_cmp) cmp_keys_by_null_selectivity); + /* Sort the keys in each of the indexes. */ for (uint i= 0; i < keys_count; i++) merge_keys[i]->sort_keys(); - // TIMOUR: sort all the keys by NULL selectivity - if (init_queue(&pq, keys_count, 0, FALSE, subselect_rowid_merge_engine::cmp_keys_by_cur_rownum, NULL)) { @@ -4486,20 +4535,38 @@ void subselect_rowid_merge_engine::clean } +void subselect_rowid_merge_engine::print(String *str, enum_query_type query_type) +{ + str->append(STRING_WITH_LEN("<rowid_merge>(")); + for (uint i= 0; i < keys_count; i++) + merge_keys[i]->print(str); + str->append(')'); +} + + /* + Quick sort comparison function to compare keys in order of decreasing bitmap + selectivity, so that the most selective keys come first. + + @param k1 first key to compare + @param k2 second key to compare + + @retval 1 if k1 is less selective than k2 + @retval 0 if k1 is equally selective as k2 + @retval -1 if k1 is more selective than k2 */ int -subselect_rowid_merge_engine::cmp_keys_by_null_selectivity(Ordered_key *a, - Ordered_key *b) +subselect_rowid_merge_engine::cmp_keys_by_null_selectivity(Ordered_key **k1, + Ordered_key **k2) { - double a_sel= a->null_selectivity(); - double b_sel= b->null_selectivity(); - if (a_sel == b_sel) - return 0; - if (a_sel > b_sel) + double k1_sel= (*k1)->null_selectivity(); + double k2_sel= (*k2)->null_selectivity(); + if (k1_sel < k2_sel) return 1; - return -1; + if (k1_sel > k2_sel) + return -1; + return 0; } @@ -4527,17 +4594,21 @@ subselect_rowid_merge_engine::cmp_keys_b bool subselect_rowid_merge_engine::test_null_row(rownum_t row_num) { + Ordered_key *cur_key; + uint cur_id; for (uint i = 0; i < keys_count; i++) { - if (bitmap_is_set(&matching_keys, i)) + cur_key= merge_keys[i]; + cur_id= cur_key->get_keyid(); + if (bitmap_is_set(&matching_keys, cur_id)) { /* - The key 'i' already matches a value in row 'row_num', thus we - skip it as it can't possibly match a NULL. + The key 'i' (with id 'cur_keyid') already matches a value in row 'row_num', + thus we skip it as it can't possibly match a NULL. */ continue; } - if (!merge_keys[i]->is_null(row_num)) + if (!cur_key->is_null(row_num)) return FALSE; } return TRUE; @@ -4583,7 +4654,7 @@ bool subselect_rowid_merge_engine::parti if (merge_keys[i]->get_search_key(0)->is_null()) { ++count_nulls_in_search_key; - bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_key_idx()); + bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_keyid()); } else if (merge_keys[i]->lookup()) queue_insert(&pq, (uchar *) merge_keys[i]); @@ -4610,7 +4681,7 @@ bool subselect_rowid_merge_engine::parti min_key= (Ordered_key*) queue_remove(&pq, 0); min_row_num= min_key->current(); bitmap_copy(&matching_keys, &null_only_columns); - bitmap_set_bit(&matching_keys, min_key->get_key_idx()); + bitmap_set_bit(&matching_keys, min_key->get_keyid()); bitmap_union(&matching_keys, &matching_outer_cols); if (min_key->next_same()) queue_insert(&pq, (uchar *) min_key); @@ -4633,7 +4704,7 @@ bool subselect_rowid_merge_engine::parti cur_row_num= cur_key->current(); if (cur_row_num == min_row_num) - bitmap_set_bit(&matching_keys, cur_key->get_key_idx()); + bitmap_set_bit(&matching_keys, cur_key->get_keyid()); else { /* Follows from the correct use of priority queue. */ @@ -4645,7 +4716,7 @@ bool subselect_rowid_merge_engine::parti min_key= cur_key; min_row_num= cur_row_num; bitmap_copy(&matching_keys, &null_only_columns); - bitmap_set_bit(&matching_keys, min_key->get_key_idx()); + bitmap_set_bit(&matching_keys, min_key->get_keyid()); bitmap_union(&matching_keys, &matching_outer_cols); } } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-02-19 21:55:57 +0000 +++ b/sql/item_subselect.h 2010-02-22 15:16:55 +0000 @@ -752,7 +752,7 @@ protected: Index of the key in an array of keys. This index allows to construct (sub)sets of keys represented by bitmaps. */ - uint key_idx; + uint keyid; /* The table being indexed. */ TABLE *tbl; /* The columns being indexed. */ @@ -810,7 +810,7 @@ protected: public: static void *operator new(size_t size) throw () { return sql_alloc(size); } - Ordered_key(uint key_idx_arg, TABLE *tbl_arg, + Ordered_key(uint keyid_arg, TABLE *tbl_arg, Item *search_key_arg, ha_rows null_count_arg, ha_rows min_null_row_arg, ha_rows max_null_row_arg, uchar *row_num_to_rowid_arg); @@ -822,7 +822,7 @@ public: bool init(int col_idx); uint get_column_count() { return key_column_count; } - uint get_key_idx() { return key_idx; } + uint get_keyid() { return keyid; } uint get_field_idx(uint i) { DBUG_ASSERT(i < key_column_count); @@ -841,8 +841,7 @@ public: } void sort_keys(); - - double null_selectivity() { return (1 - null_count / null_key.n_bits); } + double null_selectivity(); /* Position the current element at the first row that matches the key. @@ -896,6 +895,7 @@ public: return FALSE; return bitmap_is_set(&null_key, row_num); } + void print(String *str); }; @@ -957,10 +957,10 @@ protected: bool has_covering_null_row; protected: /* - Comparison function to compare keys in order of increasing bitmap + Comparison function to compare keys in order of decreasing bitmap selectivity. */ - static int cmp_keys_by_null_selectivity(Ordered_key *a, Ordered_key *b); + static int cmp_keys_by_null_selectivity(Ordered_key **k1, Ordered_key **k2); /* Comparison function used by the priority queue pq, the 'smaller' key is the one with the smaller current row number. @@ -992,7 +992,7 @@ public: uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } void exclude() {} table_map upper_select_const_tables() { return 0; } - void print(String*, enum_query_type) {} + void print(String*, enum_query_type); bool change_result(Item_subselect*, select_result_interceptor*) { DBUG_ASSERT(FALSE); return false; } bool no_tables() { return false; }

1 0

[Maria-developers] Rev 2763: Automerge with 5.3-subqueries in file:///home/tsk/mprog/src/5.3-mwl68-unmerged/
by timour＠askmonty.org 22 Feb '10

22 Feb '10

At file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ ------------------------------------------------------------ revno: 2763 [merge] revision-id: timour(a)askmonty.org-20100222135709-3568ya6z76hkwfzs parent: timour(a)askmonty.org-20100219215557-g1uohrur8bopfv7n parent: psergey(a)askmonty.org-20100221075312-fc08qgn72dnbudd5 committer: timour(a)askmonty.org branch nick: 5.3-mwl68-unmerged timestamp: Mon 2010-02-22 15:57:09 +0200 message: Automerge with 5.3-subqueries modified: mysql-test/r/subselect_no_mat.result subselect_no_mat.res-20100117143924-hut18sl9k2c7qdj8-1 mysql-test/r/subselect_no_opts.result subselect_no_opts.re-20100117143925-pabg7o8iyokjlu93-1 mysql-test/r/subselect_no_semijoin.result subselect_no_semijoi-20100117143925-9yfygtcm7fwsuq2p-1 mysql-test/r/subselect_sj.result subselect_sj.result-20100117143926-nrop4ku355g3kv8b-1 mysql-test/r/subselect_sj_jcl6.result subselect_sj_jcl6.re-20100117143928-7vzk51yaf29cdavp-1 mysql-test/t/subselect_no_mat.test subselect_no_mat.tes-20100117143930-gdl0f5gsvv77pvtm-1 mysql-test/t/subselect_no_opts.test subselect_no_opts.te-20100117143930-hqzf7r8bjr0gu6td-1 mysql-test/t/subselect_no_semijoin.test subselect_no_semijoi-20100117143930-9yvcpokp2ayxhkju-1 mysql-test/t/subselect_sj.test subselect_sj.test-20100117143931-qp396ufpe3k0scre-1 sql/item.cc sp1f-item.cc-19700101030959-u7hxqopwpfly4kf5ctlyk2dvrq4l3dhn sql/item.h sp1f-item.h-19700101030959-rrkb43htudd62batmoteashkebcwykpa sql/item_subselect.cc sp1f-item_subselect.cc-20020512204640-qep43aqhsfrwkqmrobni6czc3fqj36oo sql/opt_table_elimination.cc opt_table_eliminatio-20090625095316-7ka9w3zr7n5114iv-1 === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -1360,9 +1360,9 @@ 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index -1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index +1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.61 Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`)) insert into t1 values (3,31); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-21 07:53:12 +0000 @@ -202,39 +202,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:53:12 +0000 @@ -206,39 +206,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/subselect_no_mat.test' --- a/mysql-test/t/subselect_no_mat.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_mat.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_opts.test' --- a/mysql-test/t/subselect_no_opts.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_opts.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off,semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_semijoin.test' --- a/mysql-test/t/subselect_no_semijoin.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_semijoin.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-11 23:59:58 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-21 07:53:12 +0000 @@ -96,30 +96,39 @@ --echo BUG#37120 optimizer_switch allowable values not according to specification --echo +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch=default; === modified file 'sql/item.cc' --- a/sql/item.cc 2010-02-11 23:59:58 +0000 +++ b/sql/item.cc 2010-02-21 06:32:23 +0000 @@ -1959,7 +1959,7 @@ bool Item_field::enumerate_field_refs_processor(uchar *arg) { Field_enumerator *fe= (Field_enumerator*)arg; - fe->visit_field(field); + fe->visit_field(this); return FALSE; } @@ -5779,6 +5779,35 @@ set_properties(); } +/* + A Field_enumerator-compatible class that invokes mark_as_dependent() for + each field that is a reference to some ancestor of current_select. +*/ +class Dependency_marker: public Field_enumerator +{ +public: + THD *thd; + st_select_lex *current_select; + virtual void visit_field(Item_field *item) + { + // Find which select the field is in. This is achieved by walking up + // the select tree and looking for the table of interest. + st_select_lex *sel; + for (sel= current_select; sel; sel= sel->outer_select()) + { + TABLE_LIST *tbl; + for (tbl= sel->leaf_tables; tbl; tbl= tbl->next_leaf) + { + if (tbl->table == item->field->table) + { + if (sel != current_select) + mark_as_dependent(thd, sel, current_select, item, item); + return; + } + } + } + } +}; /** Resolve the name of a reference to a column reference. @@ -6038,6 +6067,20 @@ last_checked_context->select_lex->nest_level); } } + else + { + ; + /* + It could be that we're referring to something that's in ancestor selects. + We must make an appropriate mark_as_dependent() call for each such + outside reference. + */ + Dependency_marker dep_marker; + dep_marker.current_select= current_sel; + dep_marker.thd= thd; + (*ref)->walk(&Item::enumerate_field_refs_processor, FALSE, + (uchar*)&dep_marker); + } DBUG_ASSERT(*ref); /* === modified file 'sql/item.h' --- a/sql/item.h 2010-02-17 10:05:27 +0000 +++ b/sql/item.h 2010-02-21 06:32:23 +0000 @@ -1134,7 +1134,7 @@ class Field_enumerator { public: - virtual void visit_field(Field *field)= 0; + virtual void visit_field(Item_field *field)= 0; virtual ~Field_enumerator() {}; /* purecov: inspected */ }; @@ -2378,7 +2378,12 @@ return ref ? (*ref)->real_item() : this; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg) - { return (*ref)->walk(processor, walk_subquery, arg); } + { + if (ref && *ref) + return (*ref)->walk(processor, walk_subquery, arg); + else + return FALSE; + } bool enumerate_field_refs_processor(uchar *arg) { return (*ref)->enumerate_field_refs_processor(arg); } virtual void print(String *str, enum_query_type query_type); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-19 21:55:57 +0000 +++ b/sql/item_subselect.cc 2010-02-22 13:57:09 +0000 @@ -168,6 +168,11 @@ { done_first_fix_fields= TRUE; inside_first_fix_fields= TRUE; + upper_refs.empty(); + /* + psergey-todo: remove _first_fix_fields calls, we need changes on every + execution + */ } eliminated= FALSE; @@ -182,6 +187,7 @@ changed= 1; inside_first_fix_fields= FALSE; + if (!res) { // all transformation is done (used by prepared statements) @@ -212,12 +218,14 @@ if (!(*ref)->fixed) ret= (*ref)->fix_fields(thd, ref); thd->where= save_where; + done_first_fix_fields= FALSE; return ret; } // Is it one field subselect? if (engine->cols() > max_columns) { my_error(ER_OPERAND_COLUMNS, MYF(0), 1); + done_first_fix_fields= FALSE; return TRUE; } fix_length_and_dec(); @@ -234,6 +242,7 @@ fixed= 1; err: + done_first_fix_fields= FALSE; thd->where= save_where; return res; } @@ -276,6 +285,7 @@ return FALSE; } + /* Adjust attributes after our parent select has been merged into grandparent @@ -304,18 +314,19 @@ parent_select= new_parent; } + class Field_fixer: public Field_enumerator { public: table_map used_tables; /* Collect used_tables here */ st_select_lex *new_parent; /* Select we're in */ - virtual void visit_field(Field *field) + virtual void visit_field(Item_field *item) { //for (TABLE_LIST *tbl= new_parent->leaf_tables; tbl; tbl= tbl->next_local) //{ // if (tbl->table == field->table) // { - used_tables|= field->table->map; + used_tables|= item->field->table->map; // return; // } //} === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2010-01-17 14:51:10 +0000 +++ b/sql/opt_table_elimination.cc 2010-02-21 06:32:23 +0000 @@ -922,8 +922,9 @@ Field_dependency_recorder(Dep_analysis_context *ctx_arg): ctx(ctx_arg) {} - void visit_field(Field *field) + void visit_field(Item_field *item) { + Field *field= item->field; Dep_value_table *tbl_dep; if ((tbl_dep= ctx->table_deps[field->table->tablenr])) {

1 0

[Maria-developers] bzr commit into file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ branch (timour:2763)
by timour＠askmonty.org 22 Feb '10

22 Feb '10

#At file:///home/tsk/mprog/src/5.3-mwl68-unmerged/ based on revid:timour@askmonty.org-20100219215557-g1uohrur8bopfv7n 2763 timour(a)askmonty.org 2010-02-22 [merge] Automerge with 5.3-subqueries modified: mysql-test/r/subselect_no_mat.result mysql-test/r/subselect_no_opts.result mysql-test/r/subselect_no_semijoin.result mysql-test/r/subselect_sj.result mysql-test/r/subselect_sj_jcl6.result mysql-test/t/subselect_no_mat.test mysql-test/t/subselect_no_opts.test mysql-test/t/subselect_no_semijoin.test mysql-test/t/subselect_sj.test sql/item.cc sql/item.h sql/item_subselect.cc sql/opt_table_elimination.cc === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -1360,9 +1360,9 @@ a 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index -1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index +1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.61 Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`)) insert into t1 values (3,31); @@ -4811,4 +4811,4 @@ End of 5.1 tests. set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ End of 5.1 tests. set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ End of 5.1 tests. set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-21 07:53:12 +0000 @@ -202,39 +202,39 @@ BUG#37120 optimizer_switch allowable val select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:53:12 +0000 @@ -206,39 +206,39 @@ BUG#37120 optimizer_switch allowable val select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/subselect_no_mat.test' --- a/mysql-test/t/subselect_no_mat.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_mat.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_opts.test' --- a/mysql-test/t/subselect_no_opts.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_opts.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off,semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_semijoin.test' --- a/mysql-test/t/subselect_no_semijoin.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_semijoin.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-11 23:59:58 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-21 07:53:12 +0000 @@ -96,30 +96,39 @@ explain extended select * from t1 where --echo BUG#37120 optimizer_switch allowable values not according to specification --echo +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch=default; === modified file 'sql/item.cc' --- a/sql/item.cc 2010-02-11 23:59:58 +0000 +++ b/sql/item.cc 2010-02-21 06:32:23 +0000 @@ -1959,7 +1959,7 @@ void Item_field::reset_field(Field *f) bool Item_field::enumerate_field_refs_processor(uchar *arg) { Field_enumerator *fe= (Field_enumerator*)arg; - fe->visit_field(field); + fe->visit_field(this); return FALSE; } @@ -5779,6 +5779,35 @@ Item_ref::Item_ref(Name_resolution_conte set_properties(); } +/* + A Field_enumerator-compatible class that invokes mark_as_dependent() for + each field that is a reference to some ancestor of current_select. +*/ +class Dependency_marker: public Field_enumerator +{ +public: + THD *thd; + st_select_lex *current_select; + virtual void visit_field(Item_field *item) + { + // Find which select the field is in. This is achieved by walking up + // the select tree and looking for the table of interest. + st_select_lex *sel; + for (sel= current_select; sel; sel= sel->outer_select()) + { + TABLE_LIST *tbl; + for (tbl= sel->leaf_tables; tbl; tbl= tbl->next_leaf) + { + if (tbl->table == item->field->table) + { + if (sel != current_select) + mark_as_dependent(thd, sel, current_select, item, item); + return; + } + } + } + } +}; /** Resolve the name of a reference to a column reference. @@ -6038,6 +6067,20 @@ bool Item_ref::fix_fields(THD *thd, Item last_checked_context->select_lex->nest_level); } } + else + { + ; + /* + It could be that we're referring to something that's in ancestor selects. + We must make an appropriate mark_as_dependent() call for each such + outside reference. + */ + Dependency_marker dep_marker; + dep_marker.current_select= current_sel; + dep_marker.thd= thd; + (*ref)->walk(&Item::enumerate_field_refs_processor, FALSE, + (uchar*)&dep_marker); + } DBUG_ASSERT(*ref); /* === modified file 'sql/item.h' --- a/sql/item.h 2010-02-17 10:05:27 +0000 +++ b/sql/item.h 2010-02-21 06:32:23 +0000 @@ -1134,7 +1134,7 @@ public: class Field_enumerator { public: - virtual void visit_field(Field *field)= 0; + virtual void visit_field(Item_field *field)= 0; virtual ~Field_enumerator() {}; /* purecov: inspected */ }; @@ -2378,7 +2378,12 @@ public: return ref ? (*ref)->real_item() : this; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg) - { return (*ref)->walk(processor, walk_subquery, arg); } + { + if (ref && *ref) + return (*ref)->walk(processor, walk_subquery, arg); + else + return FALSE; + } bool enumerate_field_refs_processor(uchar *arg) { return (*ref)->enumerate_field_refs_processor(arg); } virtual void print(String *str, enum_query_type query_type); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-19 21:55:57 +0000 +++ b/sql/item_subselect.cc 2010-02-22 13:57:09 +0000 @@ -168,6 +168,11 @@ bool Item_subselect::fix_fields(THD *thd { done_first_fix_fields= TRUE; inside_first_fix_fields= TRUE; + upper_refs.empty(); + /* + psergey-todo: remove _first_fix_fields calls, we need changes on every + execution + */ } eliminated= FALSE; @@ -182,6 +187,7 @@ bool Item_subselect::fix_fields(THD *thd changed= 1; inside_first_fix_fields= FALSE; + if (!res) { // all transformation is done (used by prepared statements) @@ -212,12 +218,14 @@ bool Item_subselect::fix_fields(THD *thd if (!(*ref)->fixed) ret= (*ref)->fix_fields(thd, ref); thd->where= save_where; + done_first_fix_fields= FALSE; return ret; } // Is it one field subselect? if (engine->cols() > max_columns) { my_error(ER_OPERAND_COLUMNS, MYF(0), 1); + done_first_fix_fields= FALSE; return TRUE; } fix_length_and_dec(); @@ -234,6 +242,7 @@ bool Item_subselect::fix_fields(THD *thd fixed= 1; err: + done_first_fix_fields= FALSE; thd->where= save_where; return res; } @@ -276,6 +285,7 @@ bool Item_subselect::mark_as_dependent(T return FALSE; } + /* Adjust attributes after our parent select has been merged into grandparent @@ -304,18 +314,19 @@ void Item_subselect::fix_after_pullout(s parent_select= new_parent; } + class Field_fixer: public Field_enumerator { public: table_map used_tables; /* Collect used_tables here */ st_select_lex *new_parent; /* Select we're in */ - virtual void visit_field(Field *field) + virtual void visit_field(Item_field *item) { //for (TABLE_LIST *tbl= new_parent->leaf_tables; tbl; tbl= tbl->next_local) //{ // if (tbl->table == field->table) // { - used_tables|= field->table->map; + used_tables|= item->field->table->map; // return; // } //} === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2010-01-17 14:51:10 +0000 +++ b/sql/opt_table_elimination.cc 2010-02-21 06:32:23 +0000 @@ -922,8 +922,9 @@ public: Field_dependency_recorder(Dep_analysis_context *ctx_arg): ctx(ctx_arg) {} - void visit_field(Field *field) + void visit_field(Item_field *item) { + Field *field= item->field; Dep_value_table *tbl_dep; if ((tbl_dep= ctx->table_deps[field->table->tablenr])) {

1 0

[Maria-developers] Rev 2767: Fix buildbot failure: take into account that there is no optimizer_switch flag in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 20 Feb '10

20 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2767 revision-id: psergey(a)askmonty.org-20100221075312-fc08qgn72dnbudd5 parent: psergey(a)askmonty.org-20100221073354-d05otp2nklvja9pc committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Sun 2010-02-21 09:53:12 +0200 message: Fix buildbot failure: take into account that there is no optimizer_switch flag for table elimination in debug builds. (part 2) === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-21 07:53:12 +0000 @@ -202,7 +202,7 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:53:12 +0000 @@ -206,7 +206,7 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-21 07:33:54 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-21 07:53:12 +0000 @@ -96,6 +96,7 @@ --echo BUG#37120 optimizer_switch allowable values not according to specification --echo +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off';

1 0

[Maria-developers] Rev 2766: Fix buildbot failure: take into account that there is no optimizer_switch flag in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 20 Feb '10

20 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2766 revision-id: psergey(a)askmonty.org-20100221073354-d05otp2nklvja9pc parent: psergey(a)askmonty.org-20100221063223-h0f7u2low7rtjixc committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Sun 2010-02-21 09:33:54 +0200 message: Fix buildbot failure: take into account that there is no optimizer_switch flag for table elimination in debug builds. === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-02-17 22:54:59 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-21 07:33:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; select (select 2); @@ -4811,4 +4811,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,table_elimination=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-21 07:33:54 +0000 @@ -206,35 +206,35 @@ set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:33:54 +0000 @@ -210,35 +210,35 @@ set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,table_elimination=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/subselect_no_mat.test' --- a/mysql-test/t/subselect_no_mat.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_mat.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_opts.test' --- a/mysql-test/t/subselect_no_opts.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_opts.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='materialization=off,semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_no_semijoin.test' --- a/mysql-test/t/subselect_no_semijoin.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_no_semijoin.test 2010-02-21 07:33:54 +0000 @@ -1,11 +1,13 @@ # # Run subselect.test without semi-join optimization (test materialize) # +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; set optimizer_switch='semijoin=off'; --source t/subselect.test set optimizer_switch=default; +--replace_regex /,table_elimination=on// show variables like 'optimizer_switch'; === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-11 23:59:58 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-21 07:33:54 +0000 @@ -99,27 +99,35 @@ select @@optimizer_switch; set optimizer_switch='default,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,semijoin=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,semijoin=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch='default,materialization=off,loosescan=off'; +--replace_regex /,table_elimination=on// select @@optimizer_switch; set optimizer_switch=default;

1 0

[Maria-developers] Rev 2764: * Better self-recursion protection in Item_subselect::fix_fields. in file:///home/psergey/dev/maria-5.3-subqueries-r7/
by Sergey Petrunya 20 Feb '10

20 Feb '10

At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2764 revision-id: psergey(a)askmonty.org-20100221033618-83dgm2h9ingzmhcc parent: psergey(a)askmonty.org-20100220082329-9esvom4n6mpgeqvk committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Sun 2010-02-21 05:36:18 +0200 message: * Better self-recursion protection in Item_subselect::fix_fields. Don't go into branch that calls upper_refs.empty() more than once per PREPARE or EXECUTE * Avoid crashing when processing references to outside from subquery's HAVING (will explain in more details in email) === modified file 'sql/item.h' --- a/sql/item.h 2010-02-17 10:05:27 +0000 +++ b/sql/item.h 2010-02-21 03:36:18 +0000 @@ -2378,7 +2378,12 @@ return ref ? (*ref)->real_item() : this; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg) - { return (*ref)->walk(processor, walk_subquery, arg); } + { + if (ref && *ref) + return (*ref)->walk(processor, walk_subquery, arg); + else + return FALSE; + } bool enumerate_field_refs_processor(uchar *arg) { return (*ref)->enumerate_field_refs_processor(arg); } virtual void print(String *str, enum_query_type query_type); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-20 08:23:29 +0000 +++ b/sql/item_subselect.cc 2010-02-21 03:36:18 +0000 @@ -186,7 +186,6 @@ changed= 1; inside_first_fix_fields= FALSE; - done_first_fix_fields= FALSE; if (!res) { @@ -218,12 +217,14 @@ if (!(*ref)->fixed) ret= (*ref)->fix_fields(thd, ref); thd->where= save_where; + done_first_fix_fields= FALSE; return ret; } // Is it one field subselect? if (engine->cols() > max_columns) { my_error(ER_OPERAND_COLUMNS, MYF(0), 1); + done_first_fix_fields= FALSE; return TRUE; } fix_length_and_dec(); @@ -240,6 +241,7 @@ fixed= 1; err: + done_first_fix_fields= FALSE; thd->where= save_where; return res; } @@ -282,6 +284,7 @@ return FALSE; } + /* Adjust attributes after our parent select has been merged into grandparent @@ -310,6 +313,7 @@ parent_select= new_parent; } + class Field_fixer: public Field_enumerator { public:

1 1