- developers - lists.mariadb.org

[Maria-developers] Updated (by Timour): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 12 Mar '10

12 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-Sprint TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 12 Mar 2010, 09:17)=-=- Status updated. --- /tmp/wklog.89.old.13018 2010-03-12 09:17:25.000000000 +0000 +++ /tmp/wklog.89.new.13018 2010-03-12 09:17:25.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Category updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Status updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 12 Mar '10

12 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-Sprint TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 12 Mar 2010, 09:17)=-=- Status updated. --- /tmp/wklog.89.old.13018 2010-03-12 09:17:25.000000000 +0000 +++ /tmp/wklog.89.new.13018 2010-03-12 09:17:25.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Category updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Status updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 12 Mar '10

12 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-Sprint TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 12 Mar 2010, 09:17)=-=- Status updated. --- /tmp/wklog.89.old.13018 2010-03-12 09:17:25.000000000 +0000 +++ /tmp/wklog.89.new.13018 2010-03-12 09:17:25.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Category updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Status updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Timour): Subqueries: cost-based choice between Materialization and IN->EXISTS transformation (89)
by worklog-noreply＠askmonty.org 12 Mar '10

12 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: cost-based choice between Materialization and IN->EXISTS transformation CREATION DATE..: Sun, 28 Feb 2010, 13:39 SUPERVISOR.....: Monty IMPLEMENTOR....: Timour COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-Sprint TASK ID........: 89 (http://askmonty.org/worklog/?tid=89) VERSION........: Server-5.3 STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Timour - Fri, 12 Mar 2010, 09:17)=-=- Status updated. --- /tmp/wklog.89.old.13018 2010-03-12 09:17:25.000000000 +0000 +++ /tmp/wklog.89.new.13018 2010-03-12 09:17:25.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Category updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Igor - Wed, 10 Mar 2010, 21:48)=-=- Status updated. --- /tmp/wklog.89.old.778 2010-03-10 21:48:08.000000000 +0000 +++ /tmp/wklog.89.new.778 2010-03-10 21:48:08.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 16:34)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24497 2010-02-28 16:34:05.000000000 +0000 +++ /tmp/wklog.89.new.24497 2010-02-28 16:34:05.000000000 +0000 @@ -36,8 +36,8 @@ So, we'll need to compute both exists_select_cost and materialization_cost. -Difficulty with computing the two costs ---------------------------------------- +Difficulty with the need to run select optimization two times +------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. @@ -46,4 +46,10 @@ 3. Then we find that cost #1 is less and want to execute the materialization strategy. +The problem is that once one injects "oe=ie", it can trigger some optimization +steps that are not possible to undo. +- Example1: outer->inner join conversion +- non-Example: according to Igor, "oe=ie" won't participate in equality propagation. +- ... what else ? + -=-=(Psergey - Sun, 28 Feb 2010, 16:08)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24098 2010-02-28 16:08:56.000000000 +0000 +++ /tmp/wklog.89.new.24098 2010-02-28 16:08:56.000000000 +0000 @@ -36,3 +36,14 @@ So, we'll need to compute both exists_select_cost and materialization_cost. +Difficulty with computing the two costs +--------------------------------------- +The problem is in this scenario: +1. We compute materialization_cost by running optimization for the original + subquery select. +2. We compute exists_select_cost by running optimization for the subquery's + select with "oe=ie" injected into WHERE +3. Then we find that cost #1 is less and want to execute the materialization + strategy. + + -=-=(Psergey - Sun, 28 Feb 2010, 15:57)=-=- High-Level Specification modified. --- /tmp/wklog.89.old.24045 2010-02-28 15:57:49.000000000 +0000 +++ /tmp/wklog.89.new.24045 2010-02-28 15:57:49.000000000 +0000 @@ -1 +1,38 @@ +Why need two optimizations +-------------------------- +Consider a query with subquery: + + SELECT + oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) + FROM outer_tbl + WHERE outer_cond + +If we use Materialization strategy, the costs will be + + cost of accessing outer_tbl + + materialization_cost + + #records(outer_tbl w/o outer_cond) * lookup_cost + +where + + materialization_cost= + cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) + +On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into + + SELECT + EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + FROM outer_tbl + WHERE outer_cond + +and the costs will be + + cost of accessing outer_tbl + + #records(outer_tbl w/o outer_cond) * exists_select_cost + +where + exists_select_cost= + cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) + +So, we'll need to compute both exists_select_cost and materialization_cost. -=-=(Psergey - Sun, 28 Feb 2010, 15:07)=-=- Dependency created: 91 now depends on 89 DESCRIPTION: For uncorrelated IN subqueries that can't be converted to semi-joins it is necessary to make a cost-based choice between IN->EXISTS and Materialization strategies. Both strategies handle two cases: 1. A simple case w/o NULLs handling 2. Handling NULLs. This WL is about making cost-based decision for #1. HIGH-LEVEL SPECIFICATION: Why need two optimizations -------------------------- Consider a query with subquery: SELECT oe IN (SELECT ie FROM inner_tbl WHERE inner_cond) FROM outer_tbl WHERE outer_cond If we use Materialization strategy, the costs will be cost of accessing outer_tbl + materialization_cost + #records(outer_tbl w/o outer_cond) * lookup_cost where materialization_cost= cost of executing the (SELECT ie FROM inner_tbl WHERE inner_cond) On the other hand, for IN->EXISTS strategy, the subquery will be rewritten into SELECT EXISTS (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) FROM outer_tbl WHERE outer_cond and the costs will be cost of accessing outer_tbl + #records(outer_tbl w/o outer_cond) * exists_select_cost where exists_select_cost= cost of executing the (SELECT 1 FROM inner_tbl WHERE inner_cond AND oe=ie) So, we'll need to compute both exists_select_cost and materialization_cost. Difficulty with the need to run select optimization two times ------------------------------------------------------------- The problem is in this scenario: 1. We compute materialization_cost by running optimization for the original subquery select. 2. We compute exists_select_cost by running optimization for the subquery's select with "oe=ie" injected into WHERE 3. Then we find that cost #1 is less and want to execute the materialization strategy. The problem is that once one injects "oe=ie", it can trigger some optimization steps that are not possible to undo. - Example1: outer->inner join conversion - non-Example: according to Igor, "oe=ie" won't participate in equality propagation. - ... what else ? 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 12 Mar '10

12 Mar '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.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 68 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 68 -=-=(Psergey - Sun, 28 Feb 2010, 14:08)=-=- Dependency created: 94 now depends on 68 -=-=(Guest - Sat, 27 Feb 2010, 10:11)=-=- Status updated. No change. -=-=(Guest - Sat, 27 Feb 2010, 10:11)=-=- Status updated. --- /tmp/wklog.68.old.24229 2010-02-27 10:11:57.000000000 +0000 +++ /tmp/wklog.68.new.24229 2010-02-27 10:11:57.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(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}. ------------------------------------------------------------ -=-=(View All Progress Notes, 16 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 12 Mar '10

12 Mar '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.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 68 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 68 -=-=(Psergey - Sun, 28 Feb 2010, 14:08)=-=- Dependency created: 94 now depends on 68 -=-=(Guest - Sat, 27 Feb 2010, 10:11)=-=- Status updated. No change. -=-=(Guest - Sat, 27 Feb 2010, 10:11)=-=- Status updated. --- /tmp/wklog.68.old.24229 2010-02-27 10:11:57.000000000 +0000 +++ /tmp/wklog.68.new.24229 2010-02-27 10:11:57.000000000 +0000 @@ -1 +1 @@ -Assigned +In-Progress -=-=(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}. ------------------------------------------------------------ -=-=(View All Progress Notes, 16 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 2767: MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs in file:///home/tsk/mprog/src/5.3-mwl68/
by timour＠askmonty.org 11 Mar '10

11 Mar '10

At file:///home/tsk/mprog/src/5.3-mwl68/ ------------------------------------------------------------ revno: 2767 revision-id: timour(a)askmonty.org-20100311214331-kw8ng8aiy6h60vai parent: timour(a)askmonty.org-20100309103615-dzmm6xt7ye5xfs25 committer: timour(a)askmonty.org branch nick: 5.3-mwl68 timestamp: Thu 2010-03-11 23:43:31 +0200 message: MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs This patch does three things: - It adds the possibility to force the execution of top-level [NOT] IN subquery predicates via the IN=>EXISTS transformation. This is done by setting both optimizer switches partial_match_rowid_merge and partial_match_table_scan to "off". - It adjusts all test cases where the complete optimizer_switch is selected because now we have two more switches. - For those test cases where the plan changes because of the new available strategies, we switch off both partial match strategies in order to force the "old" IN=>EXISTS strategy. This is done because most of these test cases specifically test bugs in this strategy. === modified file 'mysql-test/include/mix1.inc' --- a/mysql-test/include/mix1.inc 2009-09-15 06:08:54 +0000 +++ b/mysql-test/include/mix1.inc 2010-03-11 21:43:31 +0000 @@ -1177,8 +1177,11 @@ create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; explain select b from t1 where a not in (select b from t1,t2 group by a) group by a; +set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; --echo End of 5.0 tests === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2010-03-11 21:43:31 +0000 @@ -1419,19 +1419,19 @@ # 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,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 +index_merge=off,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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -1458,21 +1458,21 @@ set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on # # Check index_merge's @@optimizer_switch flags # 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1582,5 +1582,5 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on drop table t0, t1; === modified file 'mysql-test/r/innodb_mysql.result' --- a/mysql-test/r/innodb_mysql.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/innodb_mysql.result 2010-03-11 21:43:31 +0000 @@ -1425,12 +1425,15 @@ # create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; explain select b from t1 where a not in (select b from t1,t2 group by a) group by a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 +set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; End of 5.0 tests CREATE TABLE `t2` ( === modified file 'mysql-test/r/myisam_mrr.result' --- a/mysql-test/r/myisam_mrr.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/myisam_mrr.result 2010-03-11 21:43:31 +0000 @@ -394,7 +394,7 @@ # - engine_condition_pushdown does not affect ICP 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, key(a)); === modified file 'mysql-test/r/ps.result' --- a/mysql-test/r/ps.result 2009-05-27 15:19:44 +0000 +++ b/mysql-test/r/ps.result 2010-03-11 21:43:31 +0000 @@ -149,6 +149,8 @@ c32 set('monday', 'tuesday', 'wednesday') ) engine = MYISAM ; create table t2 like t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; @@ -177,6 +179,7 @@ 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; drop tables t1,t2; +set @@optimizer_switch=@save_optimizer_switch; set @arg00=1; prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ; execute stmt1 ; === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect.result 2010-03-11 21:43:31 +0000 @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4803,4 +4805,5 @@ 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; End of 5.1 tests. === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-02-17 10:05:27 +0000 +++ b/mysql-test/r/subselect3.result 2010-03-11 21:43:31 +0000 @@ -63,12 +63,15 @@ select ' ^ This must show 11' Z; Z ^ This must show 11 +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; create table t1 (a int, oref int, key(a)); insert into t1 values @@ -692,6 +695,8 @@ 2 3 h 3 4 i DROP TABLE t1, t2; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); INSERT INTO t1 VALUES (1), (NULL), (4); @@ -759,6 +764,7 @@ 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2 (placeholder CHAR(11)); @@ -960,7 +966,7 @@ # Baseline: SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 17 +Handler_read_rnd_next 18 INSERT INTO t1 VALUES (NULL, NULL); FLUSH STATUS; @@ -977,7 +983,7 @@ # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 18 +Handler_read_rnd_next 19 DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-03-11 21:43:31 +0000 @@ -67,12 +67,15 @@ select ' ^ This must show 11' Z; Z ^ This must show 11 +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; create table t1 (a int, oref int, key(a)); insert into t1 values @@ -696,6 +699,8 @@ 2 3 h 3 4 i DROP TABLE t1, t2; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); INSERT INTO t1 VALUES (1), (NULL), (4); @@ -763,6 +768,7 @@ 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2 (placeholder CHAR(11)); @@ -964,7 +970,7 @@ # Baseline: SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 17 +Handler_read_rnd_next 18 INSERT INTO t1 VALUES (NULL, NULL); FLUSH STATUS; @@ -981,7 +987,7 @@ # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 18 +Handler_read_rnd_next 19 DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-11 21:43:31 +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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-11 21:43:31 +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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/ps.test' --- a/mysql-test/t/ps.test 2009-05-27 15:19:44 +0000 +++ b/mysql-test/t/ps.test 2010-03-11 21:43:31 +0000 @@ -163,6 +163,9 @@ ) engine = MYISAM ; create table t2 like t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; @@ -171,6 +174,8 @@ deallocate prepare stmt1; drop tables t1,t2; +set @@optimizer_switch=@save_optimizer_switch; + # # parameters from variables (for field creation) # === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-01-17 20:52:20 +0000 +++ b/mysql-test/t/subselect.test 2010-03-11 21:43:31 +0000 @@ -11,6 +11,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; --enable_warnings +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + select (select 2); explain extended select (select 2); SELECT (SELECT 1) UNION SELECT (SELECT 2); @@ -4061,4 +4064,6 @@ (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; + --echo End of 5.1 tests. === modified file 'mysql-test/t/subselect3.test' --- a/mysql-test/t/subselect3.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect3.test 2010-03-11 21:43:31 +0000 @@ -59,9 +59,13 @@ show status like 'Handler_read_rnd_next'; select ' ^ This must show 11' Z; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + # This must show trigcond: explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; # @@ -529,6 +533,9 @@ DROP TABLE t1, t2; +# The next three test cases must be executed with the IN=>EXISTS strategy +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; # # Bug #27870: crash of an equijoin query with WHERE condition containing @@ -588,6 +595,8 @@ DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; + # # Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer: # Assertion failed, unexpected error message: === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-03-09 10:36:15 +0000 +++ b/sql/opt_subselect.cc 2010-03-11 21:43:31 +0000 @@ -187,7 +187,11 @@ does not call setup_subquery_materialization(). We could make SELECT ... FROM DUAL call that function but that doesn't seem to be the case that is worth handling. - 4. Subquery is non-correlated + 4. Either the subquery predicate is a top-level predicate, or at + least one partial match strategy is enabled. If no partial match + strategy is enabled, then materialization cannot be used for + non-top-level queries because it cannot handle NULLs correctly. + 5. Subquery is non-correlated TODO: This is an overly restrictive condition. It can be extended to: (Subquery is non-correlated || @@ -195,13 +199,13 @@ (Subquery is correlated to the immediate outer query && Subquery !contains {GROUP BY, ORDER BY [LIMIT], aggregate functions}) && subquery predicate is not under "NOT IN")) - 5. No execution method was already chosen (by a prepared statement). + 6. No execution method was already chosen (by a prepared statement). (*) The subquery must be part of a SELECT statement. The current condition also excludes multi-table update statements. - We have to determine whether we will perform subquery materialization - before calling the IN=>EXISTS transformation, so that we know whether to + Determine whether we will perform subquery materialization before + calling the IN=>EXISTS transformation, so that we know whether to perform the whole transformation or only that part of it which wraps Item_in_subselect in an Item_in_optimizer. */ @@ -211,11 +215,14 @@ select_lex->master_unit()->first_select()->leaf_tables && // 3 thd->lex->sql_command == SQLCOM_SELECT && // * select_lex->outer_select()->leaf_tables && // 3A - subquery_types_allow_materialization(in_subs)) + subquery_types_allow_materialization(in_subs) && + // psergey-todo: duplicated_subselect_card_check: where it's done? + (in_subs->is_top_level_item() || + optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || + optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) &&//4 + !in_subs->is_correlated && // 5 + in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6 { - // psergey-todo: duplicated_subselect_card_check: where it's done? - if (!in_subs->is_correlated && // 4 - in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 5 in_subs->exec_method= Item_in_subselect::MATERIALIZATION; }

1 0

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

11 Mar '10

#At file:///home/tsk/mprog/src/5.3-mwl68/ based on revid:timour@askmonty.org-20100309103615-dzmm6xt7ye5xfs25 2767 timour(a)askmonty.org 2010-03-11 MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs This patch does three things: - It adds the possibility to force the execution of top-level [NOT] IN subquery predicates via the IN=>EXISTS transformation. This is done by setting both optimizer switches partial_match_rowid_merge and partial_match_table_scan to "off". - It adjusts all test cases where the complete optimizer_switch is selected because now we have two more switches. - For those test cases where the plan changes because of the new available strategies, we switch off both partial match strategies in order to force the "old" IN=>EXISTS strategy. This is done because most of these test cases specifically test bugs in this strategy. @ sql/opt_subselect.cc Adds the possibility to force the execution of top-level [NOT] IN subquery predicates via the IN=>EXISTS transformation. This is done by setting both optimizer switches partial_match_rowid_merge and partial_match_table_scan to "off". modified: mysql-test/include/mix1.inc mysql-test/r/index_merge_myisam.result mysql-test/r/innodb_mysql.result mysql-test/r/myisam_mrr.result mysql-test/r/ps.result mysql-test/r/subselect.result mysql-test/r/subselect3.result mysql-test/r/subselect3_jcl6.result 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/ps.test mysql-test/t/subselect.test mysql-test/t/subselect3.test sql/opt_subselect.cc === modified file 'mysql-test/include/mix1.inc' --- a/mysql-test/include/mix1.inc 2009-09-15 06:08:54 +0000 +++ b/mysql-test/include/mix1.inc 2010-03-11 21:43:31 +0000 @@ -1177,8 +1177,11 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; explain select b from t1 where a not in (select b from t1,t2 group by a) group by a; +set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; --echo End of 5.0 tests === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2010-03-11 21:43:31 +0000 @@ -1419,19 +1419,19 @@ drop table t1; # 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,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 +index_merge=off,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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -1458,21 +1458,21 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on # # Check index_merge's @@optimizer_switch flags # 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1582,5 +1582,5 @@ id select_type table type possible_keys 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on drop table t0, t1; === modified file 'mysql-test/r/innodb_mysql.result' --- a/mysql-test/r/innodb_mysql.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/innodb_mysql.result 2010-03-11 21:43:31 +0000 @@ -1425,12 +1425,15 @@ DROP TABLE t1; # create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; explain select b from t1 where a not in (select b from t1,t2 group by a) group by a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 +set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; End of 5.0 tests CREATE TABLE `t2` ( === modified file 'mysql-test/r/myisam_mrr.result' --- a/mysql-test/r/myisam_mrr.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/myisam_mrr.result 2010-03-11 21:43:31 +0000 @@ -394,7 +394,7 @@ drop table t0, t1; # - engine_condition_pushdown does not affect ICP 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, key(a)); === modified file 'mysql-test/r/ps.result' --- a/mysql-test/r/ps.result 2009-05-27 15:19:44 +0000 +++ b/mysql-test/r/ps.result 2010-03-11 21:43:31 +0000 @@ -149,6 +149,8 @@ c29 longblob, c30 longtext, c31 enum('on c32 set('monday', 'tuesday', 'wednesday') ) engine = MYISAM ; create table t2 like t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; @@ -177,6 +179,7 @@ id select_type table type possible_keys 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; drop tables t1,t2; +set @@optimizer_switch=@save_optimizer_switch; set @arg00=1; prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ; execute stmt1 ; === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-02-17 21:59:41 +0000 +++ b/mysql-test/r/subselect.result 2010-03-11 21:43:31 +0000 @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4803,4 +4805,5 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; End of 5.1 tests. === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-02-17 10:05:27 +0000 +++ b/mysql-test/r/subselect3.result 2010-03-11 21:43:31 +0000 @@ -63,12 +63,15 @@ Handler_read_rnd_next 11 select ' ^ This must show 11' Z; Z ^ This must show 11 +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; create table t1 (a int, oref int, key(a)); insert into t1 values @@ -692,6 +695,8 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); INSERT INTO t1 VALUES (1), (NULL), (4); @@ -759,6 +764,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2 (placeholder CHAR(11)); @@ -960,7 +966,7 @@ i1 i2 # Baseline: SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 17 +Handler_read_rnd_next 18 INSERT INTO t1 VALUES (NULL, NULL); FLUSH STATUS; @@ -977,7 +983,7 @@ i1 i2 # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 18 +Handler_read_rnd_next 19 DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-03-11 21:43:31 +0000 @@ -67,12 +67,15 @@ Handler_read_rnd_next 11 select ' ^ This must show 11' Z; Z ^ This must show 11 +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; create table t1 (a int, oref int, key(a)); insert into t1 values @@ -696,6 +699,8 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); INSERT INTO t1 VALUES (1), (NULL), (4); @@ -763,6 +768,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2 (placeholder CHAR(11)); @@ -964,7 +970,7 @@ i1 i2 # Baseline: SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 17 +Handler_read_rnd_next 18 INSERT INTO t1 VALUES (NULL, NULL); FLUSH STATUS; @@ -981,7 +987,7 @@ i1 i2 # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 18 +Handler_read_rnd_next 19 DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-02-21 07:33:54 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-03-11 21:43:31 +0000 @@ -1,8 +1,10 @@ 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; select (select 2); (select 2) 2 @@ -4807,8 +4809,9 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-11 21:43:31 +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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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-03-07 15:41:45 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-11 21:43:31 +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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on 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 +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,partial_match_rowid_merge=on,partial_match_table_scan=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/ps.test' --- a/mysql-test/t/ps.test 2009-05-27 15:19:44 +0000 +++ b/mysql-test/t/ps.test 2010-03-11 21:43:31 +0000 @@ -163,6 +163,9 @@ create table t1 ) engine = MYISAM ; create table t2 like t1; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; @@ -171,6 +174,8 @@ explain SELECT (SELECT SUM(c1 + c12 + 0. deallocate prepare stmt1; drop tables t1,t2; +set @@optimizer_switch=@save_optimizer_switch; + # # parameters from variables (for field creation) # === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-01-17 20:52:20 +0000 +++ b/mysql-test/t/subselect.test 2010-03-11 21:43:31 +0000 @@ -11,6 +11,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; --enable_warnings +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + select (select 2); explain extended select (select 2); SELECT (SELECT 1) UNION SELECT (SELECT 2); @@ -4061,4 +4064,6 @@ SELECT 1 FROM t1 GROUP BY (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); DROP TABLE t1; +set @@optimizer_switch=@save_optimizer_switch; + --echo End of 5.1 tests. === modified file 'mysql-test/t/subselect3.test' --- a/mysql-test/t/subselect3.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect3.test 2010-03-11 21:43:31 +0000 @@ -59,9 +59,13 @@ select a in (select max(ie) from t1 wher show status like 'Handler_read_rnd_next'; select ' ^ This must show 11' Z; +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + # This must show trigcond: explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; # @@ -529,6 +533,9 @@ SELECT a, MAX(b), DROP TABLE t1, t2; +# The next three test cases must be executed with the IN=>EXISTS strategy +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; # # Bug #27870: crash of an equijoin query with WHERE condition containing @@ -588,6 +595,8 @@ EXPLAIN SELECT a FROM t1 WHERE a NOT IN DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; + # # Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer: # Assertion failed, unexpected error message: === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-03-09 10:36:15 +0000 +++ b/sql/opt_subselect.cc 2010-03-11 21:43:31 +0000 @@ -187,7 +187,11 @@ int check_and_do_in_subquery_rewrites(JO does not call setup_subquery_materialization(). We could make SELECT ... FROM DUAL call that function but that doesn't seem to be the case that is worth handling. - 4. Subquery is non-correlated + 4. Either the subquery predicate is a top-level predicate, or at + least one partial match strategy is enabled. If no partial match + strategy is enabled, then materialization cannot be used for + non-top-level queries because it cannot handle NULLs correctly. + 5. Subquery is non-correlated TODO: This is an overly restrictive condition. It can be extended to: (Subquery is non-correlated || @@ -195,13 +199,13 @@ int check_and_do_in_subquery_rewrites(JO (Subquery is correlated to the immediate outer query && Subquery !contains {GROUP BY, ORDER BY [LIMIT], aggregate functions}) && subquery predicate is not under "NOT IN")) - 5. No execution method was already chosen (by a prepared statement). + 6. No execution method was already chosen (by a prepared statement). (*) The subquery must be part of a SELECT statement. The current condition also excludes multi-table update statements. - We have to determine whether we will perform subquery materialization - before calling the IN=>EXISTS transformation, so that we know whether to + Determine whether we will perform subquery materialization before + calling the IN=>EXISTS transformation, so that we know whether to perform the whole transformation or only that part of it which wraps Item_in_subselect in an Item_in_optimizer. */ @@ -211,11 +215,14 @@ int check_and_do_in_subquery_rewrites(JO select_lex->master_unit()->first_select()->leaf_tables && // 3 thd->lex->sql_command == SQLCOM_SELECT && // * select_lex->outer_select()->leaf_tables && // 3A - subquery_types_allow_materialization(in_subs)) + subquery_types_allow_materialization(in_subs) && + // psergey-todo: duplicated_subselect_card_check: where it's done? + (in_subs->is_top_level_item() || + optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || + optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) &&//4 + !in_subs->is_correlated && // 5 + in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6 { - // psergey-todo: duplicated_subselect_card_check: where it's done? - if (!in_subs->is_correlated && // 4 - in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 5 in_subs->exec_method= Item_in_subselect::MATERIALIZATION; }

1 0

Re: [Maria-developers] options for CREATE TABLE (MWL#43)
by Oleksandr Byelkin 11 Mar '10

11 Mar '10

Hi! Some you request contradicts with Mony's ones so I think it should be discussed somehow. 11 марта 2010, в 12:30, Sergei Golubchik написал(а): > Hi, Sanja! > > Here's the review, below: > > Summary: > > 1. please, store options together with the objects they describe, not > separately. I tried to do so in my very first implementation and IMHO it was my mistake. The same related to way of storing elements - it should be the same for parsing and reading. During parsing we one one structures and classes, for storing in TABLE/TABLE_SHARE others. Moving data between them (taking into account different memroot where they are allocated) was quite tricky. I can make it again if you both think that it is really important. > 2. Unknown option should be an error by default. OK. The only problem is that it is contradict to Monty requirements. Our initial decision was issue error if option was added explicitly. The only problem is that it is very difficult to implement - we write options to .frm first then read them and pass to engine. I have no idea how to pass this information via/over frm. > 3. use something my_getopt-like as we discussed, don't force every > engine to parse its options I can add such function for users to use, but it will be thier choice use it or do not, is it OK? > 4. make options immutable to avoid copying them in ::clone I do not know way to do it if they should be allocated in different mem_roots. > 5. don't check for changed options in alter table with your > check_if_incompatible_data. let the engine do that. This and 8 require big changes engine and ALTER TEBLE. Monty's requirement was do not touch current code. I would be glad if you discuss it and make some non contradicting requirement. > 6. parser: use ident, not IDENT_sys OK > 7. parser: make the equal sign optional I have some doubts that it is doable DATA DIRECTORY TEST VALUE ... Does it mean: DATA = DIRECTORY TEST = VALUE ... or DATA DIRECTORY = TEST VALUE ... ? - error (ALTER TABLE uses create_table_options_space_separated list of options) Other problem is should we store old options in new way, old way, both. (I think in this case both). > 8. few existing options, like row_format, insert_method, checksum, > delay_key_write, key_block_size, min_rows/max_rows, avg_row_length, > tablespace, connection, pack_keys could be moved into storage > engines > out of the parser. See above. > 9. make sure your code works (and tested) with table options specified > per partition/subpartition OK. > 10. misc details, like using 'changed' or unnecessary complex encoding > of options in the frm file, see below. > >> === added file 'mysql-test/r/create_options.result' >> --- mysql-test/r/create_options.result 1970-01-01 00:00:00 +0000 >> +++ mysql-test/r/create_options.result 2010-03-04 20:46:55 +0000 >> @@ -0,0 +1,197 @@ >> +drop table if exists t1; >> +create table t1 (a int fkey1=v1, key akey (a) kkey1=v1) tkey1=1v1 >> TKEY1=NULL tkey1=1v2 tkey2=2v1 tkey3=3v1; >> +Warnings: >> +Warning 1650 Unused option 'tkey1'='1v2' >> +Warning 1650 Unused option 'tkey2'='2v1' >> +Warning 1650 Unused option 'tkey3'='3v1' >> +Warning 1651 Unused option 'fkey1'='v1' of field 'a' >> +Warning 1652 Unused option 'kkey1'='v1' of key 'akey' > > 1. Better "unknown" or "unsupported" e.g. > > Unknown option 'tkey1' > Unsupported option 'fkey1' specified for field 'a' > Invalid option 'kkey1' used for key 'akey' > > no, "invalid" is bad here, scratch that ok > > 2. why there's no warning for TKEY1=NULL ? Because it means remove option. > >> +drop table t1; >> +create table t1 (a int fkey1=v1, key akey (a) kkey1=v1) tkey1=1v1 >> tkey1=1v2 TKEY1=NULL tkey1=1v1 tkey1=1v2 tkey2=2v1 tkey3=3v1; > > I don't understand how this is different from the first test > (and many of the tests bellow), > could you please add short one-line comments to the .test file keys are in different order. > explaining what you test in each statement ? OK > > also, a thought about "warning vs errors": > making warnings for typos and unknown options is one of the most > disliked features in MySQL - judging from the number of bugreports > (bug reports about USE HASH/BTREE, mind you - only a couple of places > where MySQL is promiscuous like that, guess what will happen when your > patch will take it to a whole new level!). > > moving engines, and so on, I know - but most users don't care. > > STRICT mode is too strict here, I think, it adds too much strictness > everywhere. What about adding a special mode that's only "strict" in > create > table (and alter table - user specified part) ? That should be ON by > default > (or, rather, a negative mode should be OFF by default). > > In other words - I want the patch to be optimized (performance, and > user > experience) for the common case, not to boundary cases. And the common > case, I believe, is the one when a user does not change engines all > the > time. We support the boundary case, yes, but optimize for the common > one. You remember that I also was for errors, but MOnty still want warnings. Also there is problem in implementation of the way we agreed on (see abopve about ALTER TABLE). >> +Warnings: >> +Warning 1650 Unused option 'tkey1'='1v2' >> +Warning 1650 Unused option 'tkey2'='2v1' >> +Warning 1650 Unused option 'tkey3'='3v1' >> +Warning 1651 Unused option 'fkey1'='v1' of field 'a' >> +Warning 1652 Unused option 'kkey1'='v1' of key 'akey' >> +drop table t1; >> +create table t1 (a int fkey1=v1, key akey (a) kkey1=v1) tkey1=1v1 >> tkey1=1v2 TKEY1='NULL' tkey2=2v1 tkey3=3v1; > ... >> === added file 'mysql-test/t/create_options_example.test' >> --- mysql-test/t/create_options_example.test 1970-01-01 00:00:00 >> +0000 >> +++ mysql-test/t/create_options_example.test 2010-03-04 20:46:55 >> +0000 >> @@ -0,0 +1,16 @@ >> +--source include/have_example_plugin.inc >> + >> +--disable_warnings >> +drop table if exists t1; >> +--enable_warnings >> + >> +#All vaues with warnings > > this should go into plugin.test or exampledb.test Why not separate test? >> +create table t1 (a int ttt=xxx E=1, key akey (a) kkk=xxx ) E=1 >> ttt=xxx ttt=yyy TTT=DEFAULT mmm=CCC zzz=MMM; >> + >> +drop table t1; >> + >> +# E=1 accepted by engine >> +create table t1 (a int ttt=xxx E=1) ENGINE=EXAMPLE E=1 ttt=xxx >> ttt=yyy TTT=DEFAULT mmm=CCC zzz=MMM; >> + >> +drop table t1; >> + >> === modified file 'sql/Makefile.am' >> --- sql/Makefile.am 2010-03-03 14:44:14 +0000 >> +++ sql/Makefile.am 2010-03-04 20:46:55 +0000 >> @@ -124,7 +124,7 @@ mysqld_SOURCES = sql_lex.cc sql_handler. >> sql_plugin.cc sql_binlog.cc \ >> sql_builtin.cc sql_tablespace.cc >> partition_info.cc \ >> sql_servers.cc event_parse_data.cc \ >> - opt_table_elimination.cc >> + opt_table_elimination.cc >> sql_create_options.cc > > please make sure that 'make distcheck' works after your changes OK >> >> nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c >> my_time.c my_user.c >> >> === modified file 'storage/example/ha_example.cc' >> --- storage/example/ha_example.cc 2010-03-03 14:44:14 +0000 >> +++ storage/example/ha_example.cc 2010-03-04 20:46:55 +0000 >> @@ -836,11 +836,43 @@ ha_rows ha_example::records_in_range(uin >> int ha_example::create(const char *name, TABLE *table_arg, >> HA_CREATE_INFO *create_info) >> { >> + CREATE_OPTION *opt; >> DBUG_ENTER("ha_example::create"); >> /* >> This is not implemented but we want someone to be able to see >> that it >> works. >> */ >> + /* Example of checking parameters for table*/ >> + if (!create_info->create_table_options) >> + DBUG_RETURN(0); >> + for (opt= create_info->create_table_options->table_opt.first; >> + opt; >> + opt= opt->next) >> + { >> + /* check for legal options and its legal values */ >> + if (opt->key.length == 1 && >> + (opt->key.str[0] == 'e' || opt->key.str[0] == 'E') && >> + opt->val.length == 1 && >> + opt->val.str[0] == '1') >> + opt->used= 1; /* tell MariaDB that we used the only legal >> parameter */ >> + } >> + /* Example of checking parameters for fields*/ >> + for (Field **field= table_arg->s->field; *field; field++) >> + { >> + if ((*field)->create_options.first) >> + { >> + for (opt= (*field)->create_options.first; opt; opt= opt->next) >> + { >> + /* check for legal options and its legal values */ >> + if (opt->key.length == 1 && >> + (opt->key.str[0] == 'e' || opt->key.str[0] == 'E') && >> + opt->val.length == 1 && >> + opt->val.str[0] == '1') >> + opt->used= 1; /* tell MariaDB that we used the only >> legal parameter */ >> + } >> + } >> + } > > No, that's way too complex and too much code. > *every* engine will need to do that, which means - it should be done > in the > server for all engines. Why you didn't use my_getopt as we originally > discussed ? OK (see above) >> + >> DBUG_RETURN(0); >> } >> >> === added file 'sql/sql_create_options.h' >> --- sql/sql_create_options.h 1970-01-01 00:00:00 +0000 >> +++ sql/sql_create_options.h 2010-03-04 20:46:55 +0000 >> @@ -0,0 +1,102 @@ >> + >> +#ifndef _SQL_CREATE_OPTIONS_H >> +#define _SQL_CREATE_OPTIONS_H >> + >> + >> +/* types of cretate options records on disk, also it is length of >> extra data */ > > 1. typo: create > 2. I know what does "length of extra data" mean, but the comment does > not help to understand it. I just forgot to change comment after changing parameter (monty wanted 2 bytes for key number also just tu be able to increase number of keys) >> +typedef enum enum_create_options_type { >> + CREATE_OPTION_TABLE= 0, >> + CREATE_OPTION_KEY= 1, >> + CREATE_OPTION_FIELD= 2 >> +} CREATE_OPTION_TYPES; >> + >> +typedef struct st_create_option { >> + /* pointer to the next option or NULL */ >> + struct st_create_option *next; >> + /* pointer to Field or KEY or NULL */ >> + void *owner; > > 1. better to use union { Field *, KEY *} > 2. even better - use 'const char* name' as you don't need anything > else > from your fields/keys here > 3. even better remove this 'owner' at all, you don't need it - see > below, > if you iterate the list of fields and keys you always know > what field/key the option belongs to. OK >> + /* key and value of the option (\0 terminated)*/ >> + LEX_STRING key, val; >> + /* used to issue warnings about unused options */ >> + my_bool used; >> +} CREATE_OPTION; >> + >> +struct st_table_options; >> + >> + >> +class st_create_option_list { > > why did you need to create your own list implementation instead of > using > either one that MySQL already has ? > (hint: LIST, I_List, List, or even dynamic array) > > and - > why do you need any list at all, if you store options in Fields and > KEYs > and simply can use the existing lists of fields and keys ? Historically. But yes now it would be better to use LIST (if it allow to insert item at the end)... >> +public: >> + /** >> + pointer on the first list element >> + */ >> + CREATE_OPTION *first; >> + /** >> + pointer on last list '.next' or beginning of the list in case >> of empty list >> + >> + @note: >> + If it is NULL then it is just sign of array of list end >> + */ >> +private: >> + CREATE_OPTION **last; >> +public: >> + void empty() {first= NULL; last= &first;} >> + st_create_option_list() {empty();} >> + st_create_option_list(const st_create_option_list &o) >> + { >> + if ((first= o.first)) >> + last= o.last; >> + else >> + last= &first; >> + } >> + my_bool last_opt() { return last == NULL; } >> + friend my_bool create_option_add(st_create_option_list *options, >> + MEM_ROOT *root, >> + const LEX_STRING *str_key, >> + const LEX_STRING *str_val, >> + my_bool *changed); >> + friend st_create_option_list >> *create_create_options_array(MEM_ROOT *root, >> + uint n); >> + friend my_bool create_options_read(const uchar *buff, uint length, >> + MEM_ROOT *root, >> + st_table_options *opt); >> + friend my_bool create_options_clone(MEM_ROOT *root, >> + st_create_option_list *opts); >> +}; >> +typedef class st_create_option_list CREATE_OPTION_LIST; >> + >> + >> +typedef struct st_table_options { >> + CREATE_OPTION_LIST table_opt; /* table options list */ >> + CREATE_OPTION_LIST *field_opt; /* fields options array */ >> + CREATE_OPTION_LIST *key_opt; /* keys options array */ >> +} TABLE_OPTIONS; >> + >> +CREATE_OPTION_LIST *create_create_options_array(MEM_ROOT *root, >> uint n); >> +TABLE_OPTIONS *create_create_options(MEM_ROOT *root, uint fields, >> uint keys); >> + >> +my_bool create_options_read(const uchar *buff, uint length, >> MEM_ROOT *root, >> + TABLE_OPTIONS *opt); >> + >> +my_bool create_option_add(CREATE_OPTION_LIST *options, MEM_ROOT >> *root, >> + const LEX_STRING *k, const LEX_STRING *v, >> + my_bool *chanes); >> + >> +ulong create_options_length(TABLE_OPTIONS *opt); >> + >> +void create_options_store(uchar *buff, TABLE_OPTIONS *opt); >> + >> +void create_options_check_unused(THD *thd, >> + TABLE_OPTIONS *options); >> + >> +struct st_table_share; >> +void create_options_binding(struct st_table_share *share); >> + >> +my_bool create_options_clone(MEM_ROOT *root, CREATE_OPTION_LIST >> *opt); >> + >> +CREATE_OPTION_LIST *create_table_list_merge(CREATE_OPTION_LIST >> *source, >> + CREATE_OPTION_LIST >> *changes, >> + MEM_ROOT *root, >> + my_bool *changed); >> +my_bool is_equal_create_options(CREATE_OPTION *opt1, CREATE_OPTION >> *opt2); >> + >> +#endif >> === modified file 'sql/table.h' >> --- sql/table.h 2010-02-12 08:47:31 +0000 >> +++ sql/table.h 2010-03-04 20:46:55 +0000 >> @@ -340,6 +340,7 @@ typedef struct st_table_share >> #ifdef NOT_YET >> struct st_table *open_tables; /* link to open tables */ >> #endif >> + TABLE_OPTIONS *create_table_options; /* text options for table */ > > do you need TABLE_OPTIONS - I mean, table, field, and key options - > here ? TABLE_SHARE has an array of KEYs and KEYs store options > internally (in KEY::create_options). And exactly the same > applies to Fields. I described it in the beginning. >> >> /* The following is copied to each TABLE on OPEN */ >> Field **field; >> === modified file 'sql/structs.h' >> --- sql/structs.h 2010-02-01 06:14:12 +0000 >> +++ sql/structs.h 2010-03-04 20:46:55 +0000 >> @@ -101,6 +101,8 @@ typedef struct st_key { >> int bdb_return_if_eq; >> } handler; >> struct st_table *table; >> + /** reference to the list of options or NULL */ >> + CREATE_OPTION_LIST create_options; > > eh, strictly speaking 'create_options' is not a pointer and it > cannot be NULL. > And it is not a reference in the C++ sense either. > > you could've simply said "list of options" Not fixed comment, sorry. >> } KEY; >> >> >> === modified file 'sql/handler.h' >> --- sql/handler.h 2010-02-01 06:14:12 +0000 >> +++ sql/handler.h 2010-03-04 20:46:55 +0000 >> @@ -919,6 +919,12 @@ typedef struct st_ha_create_information >> LEX_STRING connect_string; >> const char *password, *tablespace; >> LEX_STRING comment; >> + TABLE_OPTIONS create_table_options_orig; >> + /** >> + Originally create_table_options points on above field, but >> during ALTER >> + TABLE of the options it points on new built parameters >> + */ >> + TABLE_OPTIONS *create_table_options; > > after reading the patch I still don't understand why do you need > create_table_options_orig For avoiding allocating it for normal table, it will be changed in ALTER TABLE process. >> const char *data_file_name, *index_file_name; >> const char *alias; >> ulonglong max_rows,min_rows; >> === modified file 'sql/sql_class.cc' >> --- sql/sql_class.cc 2010-02-01 06:14:12 +0000 >> +++ sql/sql_class.cc 2010-03-04 20:46:55 +0000 >> @@ -109,6 +109,8 @@ Key::Key(const Key &rhs, MEM_ROOT *mem_r >> generated(rhs.generated) >> { >> list_copy_and_replace_each_value(columns, mem_root); >> + create_options= rhs.create_options; >> + create_options_clone(mem_root, &create_options); > > in create_options_clone() you don't need to clone everything, > this constructor only copies elements that can change during > execution, > for example field and key names don't change and don't need to be > copied. And options don't change either, only their "used" property > is. > but it would be best if you would get rid of it and make options > completely > immutable. There was problems like pointer on freed memory which gone after this I suspect different mem_roots. >> } >> >> /** >> === modified file 'sql/field.h' >> --- sql/field.h 2010-02-01 06:14:12 +0000 >> +++ sql/field.h 2010-03-04 20:46:55 +0000 >> @@ -137,6 +137,8 @@ class Field >> struct st_table *table; // Pointer for table >> struct st_table *orig_table; // Pointer to original table >> const char **table_name, *field_name; >> + /** reference to the list of options or NULL */ > > this is neither a reference nor it can be NULL old comment >> + CREATE_OPTION_LIST create_options; >> LEX_STRING comment; >> /* Field is part of the following keys */ >> key_map key_start, part_of_key, part_of_key_not_clustered; >> === modified file 'sql/field.cc' >> --- sql/field.cc 2010-02-01 06:14:12 +0000 >> +++ sql/field.cc 2010-03-04 20:46:55 +0000 >> @@ -10220,6 +10225,7 @@ Create_field::Create_field(Field *old_fi >> decimals= old_field->decimals(); >> vcol_info= old_field->vcol_info; >> stored_in_db= old_field->stored_in_db; >> + create_options= old_field->create_options; > > explain in a comment please why you don't need to copy the data > here, and can simply assign pointers Because copy constructor makes correct list assignment, is it correct comment? > >> >> /* Fix if the original table had 4 byte pointer blobs */ >> if (flags & BLOB_FLAG) >> === modified file 'sql/sql_show.cc' >> --- sql/sql_show.cc 2010-02-01 06:14:12 +0000 >> +++ sql/sql_show.cc 2010-03-04 20:46:55 +0000 >> @@ -1356,6 +1376,8 @@ int store_create_info(THD *thd, TABLE_LI >> packet->append(STRING_WITH_LEN(" COMMENT ")); >> append_unescaped(packet, field->comment.str, field- >> >comment.length); >> } >> + if (field->create_options.first) > > you don't need an if() here and below, append_create_options() > can handle the case of create_options.first == 0 OK (but i will need change list implementation in any case) > >> + append_create_options(thd, packet, field- >> >create_options.first); >> } >> >> key_info= table->key_info; >> @@ -1586,6 +1610,11 @@ int store_create_info(THD *thd, TABLE_LI >> packet->append(STRING_WITH_LEN(" CONNECTION=")); >> append_unescaped(packet, share->connect_string.str, share- >> >connect_string.length); >> } >> + /* create_table_options can be NULL for temporary tables */ >> + if (share->create_table_options && > > why TABLE_SHARE::create_table_options is a pointer to something > allocared > on TABLE_SHARE::mem_root ? In Field and KEY it's simply > a structure - part of the Field/KEY class, why not the same here ? Most time it is pointer to create_table_options_orig. It is not the same here because of ALTER TABLE and the way how it plays with TABLE_SHARE. > >> + share->create_table_options->table_opt.first) >> + append_create_options(thd, packet, >> + share->create_table_options- >> >table_opt.first); >> append_directory(thd, packet, "DATA", >> create_info.data_file_name); >> append_directory(thd, packet, "INDEX", >> create_info.index_file_name); >> } >> === modified file 'sql/sql_table.cc' >> --- sql/sql_table.cc 2010-02-12 08:47:31 +0000 >> +++ sql/sql_table.cc 2010-03-04 20:46:55 +0000 >> @@ -5789,6 +5791,15 @@ compare_tables(TABLE *table, >> DBUG_RETURN(0); >> } >> >> + if (!is_equal_create_options(tmp_new_field- >> >create_options.first, >> + field->create_options.first)) >> + { > > I am not sure this should be checked on MySQL level, we don't know the > semantics of options. I'd say this check belong to > handler::check_if_incompatible_data() and should be implemented in the > storage engine internally. Monty even requested me to recreate .frm even if case of KEY was chenged (which is clear do not chengr semantic) - i.e. any change == rewriting .frm. So your requests contradict here it should be discussed (I do not see sens nor harm in such rewriting policy) >> + DBUG_PRINT("info", ("Options difference in field '%s'", >> + new_field->field_name)); >> + *need_copy_table= ALTER_TABLE_DATA_CHANGED; >> + DBUG_RETURN(0); >> + } >> + >> /* Don't pack rows in old tables if the user has requested >> this. */ >> if (create_info->row_type == ROW_TYPE_DYNAMIC || >> (tmp_new_field->flags & BLOB_FLAG) || >> @@ -6112,6 +6125,41 @@ mysql_prepare_alter_table(THD *thd, TABL >> } >> restore_record(table, s->default_values); // Empty record for >> DEFAULT >> >> + if (create_info->create_table_options_orig.table_opt.first) >> + { >> + CREATE_OPTION_LIST *res; >> + my_bool changed= FALSE; >> + if (!table->s->create_table_options && >> + !(table->s->create_table_options= >> + create_create_options(&table->s->mem_root, >> + table->s->fields, table->s->keys))) >> + goto err; >> + >> + if (!(res= >> + create_table_list_merge(&table->s->create_table_options- >> >table_opt, >> + &create_info-> >> + >> create_table_options_orig.table_opt, >> + thd->mem_root, >> + &changed))) >> + goto err; >> + DBUG_ASSERT(res->first); >> + create_info->create_table_options_orig.table_opt= *res; >> + >> + if (changed) >> + alter_info->change_level= ALTER_TABLE_DATA_CHANGED; >> + else >> + { >> + alter_info->flags&= ~ALTER_CREATE_OPT; >> + DBUG_PRINT("info", ("Table options was not changed")); >> + } >> + } >> + else >> + if (table->s->create_table_options) >> + create_info->create_table_options_orig.table_opt= >> + table->s->create_table_options->table_opt; > > why don't you set ALTER_TABLE_DATA_CHANGED here ? it used as flag from parser only. > >> + else >> + create_info->create_table_options_orig.table_opt.empty(); >> + >> /* >> First collect all fields from table which isn't in drop_list >> */ >> === modified file 'sql/sql_yacc.yy' >> --- sql/sql_yacc.yy 2010-02-01 06:14:12 +0000 >> +++ sql/sql_yacc.yy 2010-03-04 20:46:55 +0000 >> @@ -4714,6 +4718,16 @@ create_table_option: >> Lex->create_info.used_fields|= >> HA_CREATE_USED_TRANSACTIONAL; >> Lex->create_info.transactional= $3; >> } >> + | IDENT_sys equal plugin_option_value > > 1. why IDENT_sys and not ident ? OK > 2. perhaps we should make the equal sign optional ? > first - that's backward compatible, > second - that would allow us to simplify the code quite a bit, > moving existing table and index options onto a new framework Answered above. >> + { >> + LEX *lex= Lex; >> + create_option_add(&(lex-> >> + create_info. >> + >> create_table_options_orig.table_opt), >> + YYTHD->mem_root, &$1, &$3, >> + NULL); >> + lex->alter_info.flags|= ALTER_CREATE_OPT; >> + } >> ; >> >> default_charset: >> @@ -13827,6 +13867,32 @@ uninstall: >> } >> ; >> >> +/ >> ************************************************************************** >> + >> + Create options >> + >> + >> **************************************************************************/ >> + >> +plugin_option_value: >> + DEFAULT >> + { >> + $$.str= NULL; /* We are going to remove the option */ >> + $$.length= 0; >> + } >> + | NULL_SYM > > I don't like this trick. > If you don't support NULLs, dont't allow users to specify them how it can be stored as parameter value? Such semantic prevent users of thinking that assigning NULL will make it really NULL not "NULL". >> + { >> + $$.str= NULL; /* We are going to remove the option */ >> + $$.length= 0; >> + } >> + | IDENT_sys { $$ = $1; } >> + | TEXT_STRING_sys { $$ = $1; } >> + | DECIMAL_NUM { $$ = $1; } >> + | FLOAT_NUM { $$ = $1; } >> + | NUM { $$ = $1; } >> + | LONG_NUM { $$ = $1; } >> + | HEX_NUM { $$ = $1; } > > looks like you forgot a semicolon here OK >> + >> + >> /** >> @} (end of group Parser) >> */ >> >> === added file 'sql/sql_create_options.cc' >> --- sql/sql_create_options.cc 1970-01-01 00:00:00 +0000 >> +++ sql/sql_create_options.cc 2010-03-04 20:46:55 +0000 >> @@ -0,0 +1,646 @@ >> + >> +#include "mysql_priv.h" >> + >> +/* Additional length of index for CREATE_OPTION_XXX types */ > > the comment is confusing. I could understand from the code what > create_options_len[] is for, but the comment did not help in the least "Length of additional data stored for every CREATE_OPTION_XXX types " Is it OK? > >> +static uint create_options_len[3]= {0, 2, 2}; >> + >> + >> +/** >> + Adds new option to this list >> + >> + @param options pointer to the list >> + @param root memroot to allocate option >> + @param str_key key >> + @param str_val value >> + @param changed pointer to variable to report changed data >> + >> + @retval TRUE error >> + @retval FALSE OK >> +*/ >> + >> +my_bool create_option_add(CREATE_OPTION_LIST *options, MEM_ROOT >> *root, >> + const LEX_STRING *str_key, >> + const LEX_STRING *str_val, >> + my_bool *changed) >> +{ >> + CREATE_OPTION *cur_option, **option; >> + char *key, *val; >> + my_bool not_used; >> + my_bool copy= FALSE; >> + my_bool replace= FALSE; >> + DBUG_ENTER("create_option_add"); >> + DBUG_PRINT("enter", ("key: '%s' value: '%s'", >> + str_key->str, str_val->str)); >> + if (changed) >> + copy= TRUE; >> + else >> + changed= &not_used; >> + >> + DBUG_ASSERT(options->first || >> + (!options->first && options->last == &options- >> >first)); >> + *changed= FALSE; > > Hmm, strange. From the way you use 'changed' I thought it should > accumulate > the results - I mean, it's one variable that is passed into > create_option_add() for all options. Apparently at the end it should > be > true if *any* of the options has changed. > > But then, why do you set it to false inside create_option_add() ? It was special case for call from ALTER TABLE and from parser. Only ALTER TABLE was interested in changes and so required copying parameters. >> + >> + /* try to find the option first */ >> + for (option= &(options->first); >> + *option && my_strcasecmp(system_charset_info, >> + str_key->str, (*option)->key.str); >> + option= &((*option)->next)) ; >> + if (str_val->str) >> + { >> + /* add / replace */ >> + if (*option) >> + { >> + /* replace */ >> + cur_option= *option; >> + if (!(*changed) && >> + (cur_option->val.length != str_val->length || >> + memcmp(cur_option->val.str, str_val->str, str_val- >> >length))) >> + { >> + *changed= TRUE; >> + } >> + replace= TRUE; >> + } >> + else >> + { >> + /* add */ >> + if (!(cur_option= (CREATE_OPTION *)alloc_root(root, >> + >> sizeof(CREATE_OPTION)))) >> + DBUG_RETURN(TRUE); >> + bzero(cur_option, sizeof(CREATE_OPTION)); >> + *(options->last)= cur_option; >> + options->last= &(cur_option->next); >> + *changed= TRUE; >> + } >> + if (changed || replace) >> + { >> + /* >> + In case of replace we use new key in case it differ only >> in case >> + like 'key' and 'KEY' >> + */ >> + if (!multi_alloc_root(root, &key, str_key->length + 1, >> + &val, str_val->length + 1, NULL)) >> + DBUG_RETURN(TRUE); >> + cur_option->key.str= >> + (char *)memcpy(key, str_key->str, >> + (cur_option->key.length= str_key->length)); >> + key[str_key->length]= '\0'; >> + cur_option->val.str= >> + (char *)memcpy(val, str_val->str, >> + (cur_option->val.length= str_val->length)); >> + val[str_val->length]= '\0'; >> + cur_option->used= FALSE; >> + cur_option->owner= NULL; >> + } >> + DBUG_ASSERT(options->first || >> + (!options->first && options->last == &options- >> >first)); >> + } >> + else >> + { >> + /* remove */ >> + if (*option) >> + { >> + if (options->last == &((*option)->next)) >> + options->last= option; /* we deleted last option */ >> + *option= (*option)->next; >> + *changed= TRUE; >> + DBUG_ASSERT(options->first || >> + (!options->first && options->last == &options- >> >first)); >> + } >> + } >> + DBUG_RETURN(FALSE); >> +} >> + >> + >> +/** >> + Creates empty fields/keys array for table create options structure >> + >> + @param root memroot where to allocate memory for this >> structure >> + @param n number of fields/keys >> + >> + @return pointer to array or NULL in case of error. >> +*/ >> + >> +CREATE_OPTION_LIST *create_create_options_array(MEM_ROOT *root, >> uint n) > > "create_create" is not a good name :( I did not found better but open for suggestion. > >> +{ >> + uint i; >> + DBUG_ENTER("create_create_options_array"); >> + DBUG_PRINT("enter", ("Number: %u", n)); >> + >> + CREATE_OPTION_LIST *res= >> + (CREATE_OPTION_LIST *) alloc_root(root, >> + sizeof(CREATE_OPTION_LIST) * (n >> + 1)); >> + bzero(res, sizeof(CREATE_OPTION_LIST) * (n + 1)); >> + if (!res) >> + DBUG_RETURN(NULL); >> + for (i= 0; i < n; i++) >> + res[i].last= &res[i].first; >> + /* We do not do above for res[n]. It is sign of array end */ >> + DBUG_RETURN(res); >> +} >> + >> + >> +/** >> + Reads options from this buffer >> + >> + @param buffer the buffer to read from >> + @param mem_root memroot for allocating >> + @param opt parametes to write to >> + >> + @retval TRUE Error >> + @retval FALSE OK >> +*/ >> + >> +my_bool create_options_read(const uchar *buff, uint length, >> MEM_ROOT *root, >> + TABLE_OPTIONS *opt) >> +{ >> + const uchar *buff_end= buff + length; >> + DBUG_ENTER("create_options_read"); >> + while (buff < buff_end) >> + { >> + CREATE_OPTION *option; >> + CREATE_OPTION_TYPES type; >> + uint index= 0; >> + >> + if (!(option= (CREATE_OPTION *) alloc_root(root, >> sizeof(CREATE_OPTION)))) >> + DBUG_RETURN(TRUE); >> + >> + DBUG_ASSERT(buff + 4 <= buff_end); >> + option->val.length= uint2korr(buff); >> + option->key.length= buff[2]; >> + option->next= NULL; >> + type= (CREATE_OPTION_TYPES)buff[3]; >> + buff+= 4; >> + switch (type) { >> + case CREATE_OPTION_FIELD: > > interesting encoding. so basically you support the case when field, > key, and table options are all written interleaved: > > <table option><key 1 option><field 5 option><table option><field 3 > option><key 4 option>... > > why the heck do you want to support it ? Could you propose other encoding taking into account that some fields, keys and tables do not have parameters and some has several ones? >> + index= uint2korr(buff); >> + buff+= 2; >> + *(opt->field_opt[index].last)= option; >> + opt->field_opt[index].last= &option->next; >> + break; >> + case CREATE_OPTION_KEY: >> + index= uint2korr(buff); >> + buff+= 2; >> + *(opt->key_opt[index].last)= option; >> + opt->key_opt[index].last= &option->next; >> + break; >> + case CREATE_OPTION_TABLE: >> + /* table */ >> + *(opt->table_opt.last)= option; >> + opt->table_opt.last= &option->next; >> + break; >> + default: >> + DBUG_ASSERT(0); >> + } >> + if (!(option->key.str= strmake_root(root, (const char*)buff, >> + option->key.length))) >> + DBUG_RETURN(TRUE); >> + buff+= option->key.length; >> + if (!(option->val.str= strmake_root(root, (const char*)buff, >> + option->val.length))) >> + DBUG_RETURN(TRUE); >> + buff+= option->val.length; >> + option->used= FALSE; >> + option->owner= NULL; >> + DBUG_PRINT("info", ("type: %u index: %u key: '%s' value: >> '%s'", >> + (uint) type, (uint) index, >> + option->key.str, option->val.str)); >> + } >> + DBUG_RETURN(FALSE); >> +} >> + >> +/** >> + Calculates length of saved image of the option lists >> + >> + @param opt list of options >> + @param extra_length type of the record > > eh, extra_length is not really a "type of the record", is it ? it was, but you are right it should be fixed. >> + >> + @return length >> +*/ >> + >> +static ulong create_options_list_length(CREATE_OPTION_LIST *opts, >> int extra_length) >> +{ >> + CREATE_OPTION *opt; >> + ulong res= 0; >> + DBUG_ENTER("create_options_list_length"); >> + for (opt= opts->first; opt != NULL; opt= opt->next) >> + { >> + DBUG_PRINT("info", ("key: '%s' value: '%s'", >> + (opt->key.str ? opt->key.str : "<NULL>"), >> + (opt->val.str ? opt->val.str : "<NULL>"))); >> + DBUG_ASSERT(opt->key.length); >> + /* >> + length of disk for every record: >> + 2 bytes - value length >> + 1 byte - key length >> + 1 byte - record type >> + 0/2 bytes - none/key number/field number >> + */ >> + res+= 2 + 1 + 1 + extra_length + opt->key.length + opt- >> >val.length; >> + } >> + DBUG_RETURN(res); >> +} >> + >> +/** >> + Calculates length of saved image of the all options of the table >> + >> + @param opts table of options >> + >> + @return length >> +*/ >> + >> +ulong create_options_length(TABLE_OPTIONS *opt) >> +{ >> + CREATE_OPTION_LIST *opts; >> + ulong res; >> + DBUG_ENTER("create_options_length"); >> + >> + res= >> + (opt->table_opt.first ? >> + create_options_list_length(&opt->table_opt, >> + >> create_options_len[CREATE_OPTION_TABLE]): >> + 0); >> + if (opt->field_opt) >> + { >> + for (opts= opt->field_opt; !opts->last_opt(); opts++) > > why wouldn't you simply iterate over an array of the fixed length - > you know how many fields and keys are there. And you wouldn't need > this "invalid list" array element at the end. To avoid knowing too much about other structures and classes. > even better - as I wrote above, keep options together with fields/ > keys only > and don't maintain a separate array of them. I explained what problems it brings if you think that it is vitally important I will make it. >> + res+= >> + create_options_list_length(opts, >> + >> create_options_len[CREATE_OPTION_FIELD]); >> + } >> + if (opt->key_opt) >> + { >> + for (opts= opt->key_opt; !opts->last_opt(); opts++) >> + res+= >> + create_options_list_length(opts, >> + >> create_options_len[CREATE_OPTION_KEY]); >> + } >> + DBUG_RETURN(res); >> +} > > > Regards, > Sergei

1 0

[Maria-developers] Rev 2734: Maria WL#61 in file:///Users/bell/maria/bzr/work-maria-5.2-engine/
by sanja＠askmonty.org 11 Mar '10

11 Mar '10

At file:///Users/bell/maria/bzr/work-maria-5.2-engine/ ------------------------------------------------------------ revno: 2734 revision-id: sanja(a)askmonty.org-20100311150203-mg6478pobnln5x22 parent: psergey(a)askmonty.org-20091202142609-18bp41q8mejxl47t committer: sanja(a)askmonty.org branch nick: work-maria-5.2-engine timestamp: Thu 2010-03-11 17:02:03 +0200 message: Maria WL#61 Interface for maria extensions. Alternative plugin interface with additional info (maturity and string version). === modified file 'CMakeLists.txt' --- a/CMakeLists.txt 2009-10-03 19:24:13 +0000 +++ b/CMakeLists.txt 2010-03-11 15:02:03 +0000 @@ -250,7 +250,7 @@ ENDIF(WITH_${ENGINE}_STORAGE_ENGINE AND MYSQL_PLUGIN_STATIC) IF (ENGINE_BUILD_TYPE STREQUAL "STATIC") - SET (mysql_plugin_defs "${mysql_plugin_defs},builtin_${PLUGIN_NAME}_plugin") + SET (maria_plugin_defs "${maria_plugin_defs},builtin_maria_${PLUGIN_NAME}_plugin") SET (MYSQLD_STATIC_ENGINE_LIBS ${MYSQLD_STATIC_ENGINE_LIBS} ${PLUGIN_NAME}) SET (STORAGE_ENGINE_DEFS "${STORAGE_ENGINE_DEFS} -DWITH_${ENGINE}_STORAGE_ENGINE") SET (WITH_${ENGINE}_STORAGE_ENGINE TRUE) @@ -268,7 +268,7 @@ # Special handling for partition(not really pluggable) IF(NOT WITHOUT_PARTITION_STORAGE_ENGINE) SET (STORAGE_ENGINE_DEFS "${STORAGE_ENGINE_DEFS} -DWITH_PARTITION_STORAGE_ENGINE") - SET (mysql_plugin_defs "${mysql_plugin_defs},builtin_partition_plugin") + SET (maria_plugin_defs "${maria_plugin_defs},builtin_maria_partition_plugin") ENDIF(NOT WITHOUT_PARTITION_STORAGE_ENGINE) # Special handling for tmp tables with the maria engine === modified file 'config/ac-macros/plugins.m4' --- a/config/ac-macros/plugins.m4 2009-04-25 10:05:32 +0000 +++ b/config/ac-macros/plugins.m4 2010-03-11 15:02:03 +0000 @@ -460,7 +460,7 @@ ]) ]) ]) - mysql_plugin_defs="$mysql_plugin_defs, [builtin_]$2[_plugin]" + maria_plugin_defs="$maria_plugin_defs, [builtin_maria_]$2[_plugin]" [with_plugin_]$2=yes AC_MSG_RESULT([yes]) m4_ifdef([$11],[ === modified file 'configure.in' --- a/configure.in 2009-11-12 04:31:28 +0000 +++ b/configure.in 2010-03-11 15:02:03 +0000 @@ -2841,7 +2841,7 @@ AC_SUBST(mysql_plugin_dirs) AC_SUBST(mysql_plugin_libs) -AC_SUBST(mysql_plugin_defs) +AC_SUBST(maria_plugin_defs) # Now that sql_client_dirs and sql_server_dirs are stable, determine the union. === modified file 'include/mysql/plugin.h' --- a/include/mysql/plugin.h 2009-09-07 20:50:10 +0000 +++ b/include/mysql/plugin.h 2010-03-11 15:02:03 +0000 @@ -65,7 +65,10 @@ Plugin API. Common for all plugin types. */ +/* MySQL plugin interface version */ #define MYSQL_PLUGIN_INTERFACE_VERSION 0x0100 +/* MariaDB plugin interface version */ +#define MARIA_PLUGIN_INTERFACE_VERSION 0x0100 /* The allowable types of plugins @@ -86,6 +89,21 @@ #define PLUGIN_LICENSE_GPL_STRING "GPL" #define PLUGIN_LICENSE_BSD_STRING "BSD" +/* definitions of code maturity for plugins */ +#define PLUGIN_MATURITY_UNKNOWN 0 +#define PLUGIN_MATURITY_TEST 1 +#define PLUGIN_MATURITY_ALPHA 2 +#define PLUGIN_MATURITY_BETA 3 +#define PLUGIN_MATURITY_GAMMA 4 +#define PLUGIN_MATURITY_RELEASE 5 + +#define PLUGIN_MATURITY_UNKNOWN_STR "Unknown" +#define PLUGIN_MATURITY_TEST_STR "Test" +#define PLUGIN_MATURITY_ALPHA_STR "Alpha" +#define PLUGIN_MATURITY_BETA_STR "Beta" +#define PLUGIN_MATURITY_GAMMA_STR "Gamma" +#define PLUGIN_MATURITY_RELEASE_STR "Release" + /* Macros for beginning and ending plugin declarations. Between mysql_declare_plugin and mysql_declare_plugin_end there should @@ -94,15 +112,29 @@ #ifndef MYSQL_DYNAMIC_PLUGIN + #define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \ int VERSION= MYSQL_PLUGIN_INTERFACE_VERSION; \ int PSIZE= sizeof(struct st_mysql_plugin); \ struct st_mysql_plugin DECLS[]= { + +#define __MARIA_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \ +int VERSION= MARIA_PLUGIN_INTERFACE_VERSION; \ +int PSIZE= sizeof(struct st_maria_plugin); \ +struct st_maria_plugin DECLS[]= { + #else + #define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \ MYSQL_PLUGIN_EXPORT int _mysql_plugin_interface_version_= MYSQL_PLUGIN_INTERFACE_VERSION; \ MYSQL_PLUGIN_EXPORT int _mysql_sizeof_struct_st_plugin_= sizeof(struct st_mysql_plugin); \ MYSQL_PLUGIN_EXPORT struct st_mysql_plugin _mysql_plugin_declarations_[]= { + +#define __MARIA_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \ +MYSQL_PLUGIN_EXPORT int _maria_plugin_interface_version_= MARIA_PLUGIN_INTERFACE_VERSION; \ +MYSQL_PLUGIN_EXPORT int _maria_sizeof_struct_st_plugin_= sizeof(struct st_maria_plugin); \ +MYSQL_PLUGIN_EXPORT struct st_maria_plugin _maria_plugin_declarations_[]= { + #endif #define mysql_declare_plugin(NAME) \ @@ -111,7 +143,14 @@ builtin_ ## NAME ## _sizeof_struct_st_plugin, \ builtin_ ## NAME ## _plugin) +#define maria_declare_plugin(NAME) \ +__MARIA_DECLARE_PLUGIN(NAME, \ + builtin_maria_ ## NAME ## _plugin_interface_version, \ + builtin_maria_ ## NAME ## _sizeof_struct_st_plugin, \ + builtin_maria_ ## NAME ## _plugin) + #define mysql_declare_plugin_end ,{0,0,0,0,0,0,0,0,0,0,0,0}} +#define maria_declare_plugin_end ,{0,0,0,0,0,0,0,0,0,0,0,0,0,0}} /* declarations for SHOW STATUS support in plugins @@ -407,6 +446,31 @@ void * __reserved1; /* reserved for dependency checking */ }; +/* + MariaDB extension for plugins declaration structure. + + It also copy current MySQL plugin fields to have more independency + in plugins extension +*/ + +struct st_maria_plugin +{ + int type; /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + void *info; /* pointer to type-specific plugin descriptor */ + const char *name; /* plugin name */ + const char *author; /* plugin author (for SHOW PLUGINS) */ + const char *descr; /* general descriptive text (for SHOW PLUGINS ) */ + int license; /* the plugin license (PLUGIN_LICENSE_XXX) */ + int (*init)(void *); /* the function to invoke when plugin is loaded */ + int (*deinit)(void *);/* the function to invoke when plugin is unloaded */ + unsigned int version; /* plugin version (for SHOW PLUGINS) */ + struct st_mysql_show_var *status_vars; + struct st_mysql_sys_var **system_vars; + const char *version_info; /* plugin version string */ + int maturity; /* HA_PLUGIN_MATURITY_XXX */ + void * __reserved1; /* reserved for dependency checking */ +}; + /************************************************************************* API for Full-text parser plugin. (MYSQL_FTPARSER_PLUGIN) */ === modified file 'include/mysql/plugin.h.pp' --- a/include/mysql/plugin.h.pp 2008-10-10 15:28:41 +0000 +++ b/include/mysql/plugin.h.pp 2010-03-11 15:02:03 +0000 @@ -46,6 +46,23 @@ struct st_mysql_sys_var **system_vars; void * __reserved1; }; +struct st_maria_plugin +{ + int type; + void *info; + const char *name; + const char *author; + const char *descr; + int license; + int (*init)(void *); + int (*deinit)(void *); + unsigned int version; + struct st_mysql_show_var *status_vars; + struct st_mysql_sys_var **system_vars; + const char *version_info; + int maturity; + void * __reserved1; +}; enum enum_ftparser_mode { MYSQL_FTPARSER_SIMPLE_MODE= 0, === modified file 'mysql-test/r/information_schema.result' --- a/mysql-test/r/information_schema.result 2009-10-19 17:14:48 +0000 +++ b/mysql-test/r/information_schema.result 2010-03-11 15:02:03 +0000 @@ -1175,7 +1175,7 @@ group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num varchar(27) information_schema.COLUMNS 1 -varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 +varchar(7) information_schema.PLUGINS,information_schema.ROUTINES,information_schema.VIEWS 3 varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING 6 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; === modified file 'plugin/daemon_example/daemon_example.cc' --- a/plugin/daemon_example/daemon_example.cc 2007-06-27 14:49:12 +0000 +++ b/plugin/daemon_example/daemon_example.cc 2010-03-11 15:02:03 +0000 @@ -200,3 +200,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(daemon_example) +{ + MYSQL_DAEMON_PLUGIN, + &daemon_example_plugin, + "daemon_example", + "Brian Aker", + "Daemon example, creates a heartbeat beat file in mysql-heartbeat.log", + PLUGIN_LICENSE_GPL, + daemon_example_plugin_init, /* Plugin Init */ + daemon_example_plugin_deinit, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_TEST, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'plugin/fulltext/plugin_example.c' --- a/plugin/fulltext/plugin_example.c 2007-04-26 19:26:04 +0000 +++ b/plugin/fulltext/plugin_example.c 2010-03-11 15:02:03 +0000 @@ -270,4 +270,22 @@ NULL } mysql_declare_plugin_end; +maria_declare_plugin(ftexample) +{ + MYSQL_FTPARSER_PLUGIN, /* type */ + &simple_parser_descriptor, /* descriptor */ + "simple_parser", /* name */ + "MySQL AB", /* author */ + "Simple Full-Text Parser", /* description */ + PLUGIN_LICENSE_GPL, + simple_parser_plugin_init, /* init function (when loaded) */ + simple_parser_plugin_deinit,/* deinit function (when unloaded) */ + 0x0001, /* version */ + simple_status, /* status variables */ + simple_system_variables, /* system variables */ + "0.01", /* string version */ + PLUGIN_MATURITY_TEST, /* maturity */ + NULL +} +maria_declare_plugin_end; === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2009-09-07 20:50:10 +0000 +++ b/sql/ha_ndbcluster.cc 2010-03-11 15:02:03 +0000 @@ -10561,5 +10561,23 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(ndbcluster) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &ndbcluster_storage_engine, + ndbcluster_hton_name, + "MySQL AB", + "Clustered, fault-tolerant tables", + PLUGIN_LICENSE_GPL, + ndbcluster_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + ndb_status_variables_export,/* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_BETA, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; #endif === modified file 'sql/ha_partition.cc' --- a/sql/ha_partition.cc 2009-11-12 04:31:28 +0000 +++ b/sql/ha_partition.cc 2010-03-11 15:02:03 +0000 @@ -6510,5 +6510,23 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(partition) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &partition_storage_engine, + "partition", + "Mikael Ronstrom, MySQL AB", + "Partition Storage Engine Helper", + PLUGIN_LICENSE_GPL, + partition_initialize, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100, /* 1.0 */ + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; #endif === modified file 'sql/log.cc' --- a/sql/log.cc 2009-11-12 04:31:28 +0000 +++ b/sql/log.cc 2010-03-11 15:02:03 +0000 @@ -5795,3 +5795,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(binlog) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &binlog_storage_engine, + "binlog", + "MySQL AB", + "This is a pseudo storage engine to represent the binlog in a transaction", + PLUGIN_LICENSE_GPL, + binlog_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'sql/sql_builtin.cc.in' --- a/sql/sql_builtin.cc.in 2006-12-31 01:29:11 +0000 +++ b/sql/sql_builtin.cc.in 2010-03-11 15:02:03 +0000 @@ -15,13 +15,12 @@ #include <mysql/plugin.h> -typedef struct st_mysql_plugin builtin_plugin[]; - -extern builtin_plugin - builtin_binlog_plugin@mysql_plugin_defs@; - -struct st_mysql_plugin *mysqld_builtins[]= +typedef struct st_maria_plugin builtin_maria_plugin[]; + +extern builtin_maria_plugin + builtin_maria_binlog_plugin@maria_plugin_defs@; + +struct st_maria_plugin *mariadb_builtins[]= { - builtin_binlog_plugin@mysql_plugin_defs@,(struct st_mysql_plugin *)0 + builtin_maria_binlog_plugin@maria_plugin_defs@,(struct st_maria_plugin *)0 }; - === modified file 'sql/sql_plugin.cc' --- a/sql/sql_plugin.cc 2009-11-12 04:31:28 +0000 +++ b/sql/sql_plugin.cc 2010-03-11 15:02:03 +0000 @@ -27,7 +27,7 @@ #define plugin_int_to_ref(A) &(A) #endif -extern struct st_mysql_plugin *mysqld_builtins[]; +extern struct st_maria_plugin *mariadb_builtins[]; /** @note The order of the enumeration is critical. @@ -82,6 +82,14 @@ "_mysql_sizeof_struct_st_plugin_"; static const char *plugin_declarations_sym= "_mysql_plugin_declarations_"; static int min_plugin_interface_version= MYSQL_PLUGIN_INTERFACE_VERSION & ~0xFF; +static const char *maria_plugin_interface_version_sym= + "_maria_plugin_interface_version_"; +static const char *maria_sizeof_st_plugin_sym= + "_maria_sizeof_struct_st_plugin_"; +static const char *maria_plugin_declarations_sym= + "_maria_plugin_declarations_"; +static int min_maria_plugin_interface_version= + MARIA_PLUGIN_INTERFACE_VERSION & ~0xFF; #endif /* Note that 'int version' must be the first field of every plugin @@ -205,7 +213,7 @@ const char *list); static int test_plugin_options(MEM_ROOT *, struct st_plugin_int *, int *, char **); -static bool register_builtin(struct st_mysql_plugin *, struct st_plugin_int *, +static bool register_builtin(struct st_maria_plugin *, struct st_plugin_int *, struct st_plugin_int **); static void unlock_variables(THD *thd, struct system_variables *vars); static void cleanup_variables(THD *thd, struct system_variables *vars); @@ -341,11 +349,261 @@ dlclose(p->handle); #endif my_free(p->dl.str, MYF(MY_ALLOW_ZERO_PTR)); - if (p->version != MYSQL_PLUGIN_INTERFACE_VERSION) + if (p->mariaversion != MARIA_PLUGIN_INTERFACE_VERSION) my_free((uchar*)p->plugins, MYF(MY_ALLOW_ZERO_PTR)); } +/** + Reads data from mysql plugin interface + + @param plugin_dl Structure where the data should be put + @param sym Reverence on version info + @param dlpath Path to the module + @param report What errors should be reported + + @retval FALSE OK + @retval TRUE ERROR +*/ + +static my_bool read_mysql_plugin_info(struct st_plugin_dl *plugin_dl, + void *sym, char *dlpath, + int report) +{ + DBUG_ENTER("read_maria_plugin_info"); + /* Determine interface version */ + if (!sym) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_interface_version_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_interface_version_sym); + DBUG_RETURN(TRUE); + } + plugin_dl->mariaversion= 0; + plugin_dl->mysqlversion= *(int *)sym; + /* Versioning */ + if (plugin_dl->mysqlversion < min_plugin_interface_version || + (plugin_dl->mysqlversion >> 8) > (MYSQL_PLUGIN_INTERFACE_VERSION >> 8)) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_OPEN_LIBRARY, MYF(0), dlpath, 0, + "plugin interface version mismatch"); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_OPEN_LIBRARY), dlpath, 0, + "plugin interface version mismatch"); + DBUG_RETURN(TRUE); + } + /* Find plugin declarations */ + if (!(sym= dlsym(plugin_dl->handle, plugin_declarations_sym))) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_declarations_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_declarations_sym); + DBUG_RETURN(TRUE); + } + + /* convert mysql declaration to maria one */ + { + int i; + uint sizeof_st_plugin; + struct st_mysql_plugin *old; + struct st_maria_plugin *cur; + char *ptr= (char *)sym; + + if ((sym= dlsym(plugin_dl->handle, sizeof_st_plugin_sym))) + sizeof_st_plugin= *(int *)sym; + else + { +#ifdef ERROR_ON_NO_SIZEOF_PLUGIN_SYMBOL + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), sizeof_st_plugin_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), sizeof_st_plugin_sym); + DBUG_RETURN(TRUE); +#else + /* + When the following assert starts failing, we'll have to switch + to the upper branch of the #ifdef + */ + DBUG_ASSERT(min_plugin_interface_version == 0); + sizeof_st_plugin= (int)offsetof(struct st_mysql_plugin, version); +#endif + } + + for (i= 0; + ((struct st_mysql_plugin *)(ptr+i*sizeof_st_plugin))->info; + i++) + /* no op */; + + cur= (struct st_maria_plugin*) + my_malloc(i * sizeof(struct st_maria_plugin), + MYF(MY_ZEROFILL|MY_WME)); + if (!cur) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_OUTOFMEMORY, MYF(0), plugin_dl->dl.length); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_OUTOFMEMORY), plugin_dl->dl.length); + DBUG_RETURN(TRUE); + } + /* + All st_plugin fields not initialized in the plugin explicitly, are + set to 0. It matches C standard behaviour for struct initializers that + have less values than the struct definition. + */ + for (i=0; + (old=(struct st_mysql_plugin *)(ptr+i*sizeof_st_plugin))->info; + i++) + { + + cur->type= old->type; + cur->info= old->info; + cur->name= old->name; + cur->author= old->author; + cur->descr= old->descr; + cur->license= old->license; + cur->init= old->init; + cur->deinit= old->deinit; + cur->version= old->version; + cur->status_vars= old->status_vars; + cur->system_vars= old->system_vars; + /* + Something like this should be added to process + new mysql plugin versions: + if (plugin_dl->mysqlversion > 0x0100) + { + cur->newfield= CONSTANT_MEANS_UNKNOWN; + } + else + { + cur->newfield= old->newfield; + } + */ + /* Maria only fields */ + cur->version_info= "Unknown"; + cur->maturity= PLUGIN_MATURITY_UNKNOWN; + } + + plugin_dl->plugins= (struct st_maria_plugin *)cur; + } + + DBUG_RETURN(FALSE); +} + + +/** + Reads data from maria plugin interface + + @param plugin_dl Structure where the data should be put + @param sym Reverence on version info + @param dlpath Path to the module + @param report what errors should be reported + + @retval FALSE OK + @retval TRUE ERROR +*/ + +static my_bool read_maria_plugin_info(struct st_plugin_dl *plugin_dl, + void *sym, char *dlpath, + int report) +{ + DBUG_ENTER("read_maria_plugin_info"); + + /* Determine interface version */ + if (!(sym)) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_interface_version_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_interface_version_sym); + DBUG_RETURN(TRUE); + } + plugin_dl->mariaversion= *(int *)sym; + plugin_dl->mysqlversion= 0; + /* Versioning */ + if (plugin_dl->mariaversion < min_maria_plugin_interface_version || + (plugin_dl->mariaversion >> 8) > (MARIA_PLUGIN_INTERFACE_VERSION >> 8)) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_OPEN_LIBRARY, MYF(0), dlpath, 0, + "plugin interface version mismatch"); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_OPEN_LIBRARY), dlpath, 0, + "plugin interface version mismatch"); + DBUG_RETURN(TRUE); + } + /* Find plugin declarations */ + if (!(sym= dlsym(plugin_dl->handle, maria_plugin_declarations_sym))) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_declarations_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_declarations_sym); + DBUG_RETURN(TRUE); + } + if (plugin_dl->mariaversion != MARIA_PLUGIN_INTERFACE_VERSION) + { + int i; + uint sizeof_st_plugin; + struct st_maria_plugin *old, *cur; + char *ptr= (char *)sym; + + if ((sym= dlsym(plugin_dl->handle, maria_sizeof_st_plugin_sym))) + sizeof_st_plugin= *(int *)sym; + else + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), sizeof_st_plugin_sym); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), sizeof_st_plugin_sym); + DBUG_RETURN(TRUE); + } + + for (i= 0; + ((struct st_maria_plugin *)(ptr+i*sizeof_st_plugin))->info; + i++) + /* no op */; + + cur= (struct st_maria_plugin*) + my_malloc(i * sizeof(struct st_maria_plugin), + MYF(MY_ZEROFILL|MY_WME)); + if (!cur) + { + free_plugin_mem(plugin_dl); + if (report & REPORT_TO_USER) + my_error(ER_OUTOFMEMORY, MYF(0), plugin_dl->dl.length); + if (report & REPORT_TO_LOG) + sql_print_error(ER(ER_OUTOFMEMORY), plugin_dl->dl.length); + DBUG_RETURN(TRUE); + } + /* + All st_plugin fields not initialized in the plugin explicitly, are + set to 0. It matches C standard behaviour for struct initializers that + have less values than the struct definition. + */ + for (i=0; + (old=(struct st_maria_plugin *)(ptr+i*sizeof_st_plugin))->info; + i++) + memcpy(cur+i, old, min(sizeof(cur[i]), sizeof_st_plugin)); + + sym= cur; + } + plugin_dl->plugins= (struct st_maria_plugin *)sym; + + DBUG_RETURN(FALSE); +} + static st_plugin_dl *plugin_dl_add(const LEX_STRING *dl, int report) { #ifdef HAVE_DLOPEN @@ -399,98 +657,22 @@ sql_print_error(ER(ER_CANT_OPEN_LIBRARY), dlpath, errno, errmsg); DBUG_RETURN(0); } - /* Determine interface version */ - if (!(sym= dlsym(plugin_dl.handle, plugin_interface_version_sym))) - { - free_plugin_mem(&plugin_dl); - if (report & REPORT_TO_USER) - my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_interface_version_sym); - if (report & REPORT_TO_LOG) - sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_interface_version_sym); - DBUG_RETURN(0); - } - plugin_dl.version= *(int *)sym; - /* Versioning */ - if (plugin_dl.version < min_plugin_interface_version || - (plugin_dl.version >> 8) > (MYSQL_PLUGIN_INTERFACE_VERSION >> 8)) - { - free_plugin_mem(&plugin_dl); - if (report & REPORT_TO_USER) - my_error(ER_CANT_OPEN_LIBRARY, MYF(0), dlpath, 0, - "plugin interface version mismatch"); - if (report & REPORT_TO_LOG) - sql_print_error(ER(ER_CANT_OPEN_LIBRARY), dlpath, 0, - "plugin interface version mismatch"); - DBUG_RETURN(0); - } - /* Find plugin declarations */ - if (!(sym= dlsym(plugin_dl.handle, plugin_declarations_sym))) - { - free_plugin_mem(&plugin_dl); - if (report & REPORT_TO_USER) - my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), plugin_declarations_sym); - if (report & REPORT_TO_LOG) - sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), plugin_declarations_sym); - DBUG_RETURN(0); - } - - if (plugin_dl.version != MYSQL_PLUGIN_INTERFACE_VERSION) - { - int i; - uint sizeof_st_plugin; - struct st_mysql_plugin *old, *cur; - char *ptr= (char *)sym; - - if ((sym= dlsym(plugin_dl.handle, sizeof_st_plugin_sym))) - sizeof_st_plugin= *(int *)sym; - else - { -#ifdef ERROR_ON_NO_SIZEOF_PLUGIN_SYMBOL - free_plugin_mem(&plugin_dl); - if (report & REPORT_TO_USER) - my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), sizeof_st_plugin_sym); - if (report & REPORT_TO_LOG) - sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), sizeof_st_plugin_sym); - DBUG_RETURN(0); -#else - /* - When the following assert starts failing, we'll have to switch - to the upper branch of the #ifdef - */ - DBUG_ASSERT(min_plugin_interface_version == 0); - sizeof_st_plugin= (int)offsetof(struct st_mysql_plugin, version); -#endif - } - - for (i= 0; - ((struct st_mysql_plugin *)(ptr+i*sizeof_st_plugin))->info; - i++) - /* no op */; - - cur= (struct st_mysql_plugin*) - my_malloc(i*sizeof(struct st_mysql_plugin), MYF(MY_ZEROFILL|MY_WME)); - if (!cur) - { - free_plugin_mem(&plugin_dl); - if (report & REPORT_TO_USER) - my_error(ER_OUTOFMEMORY, MYF(0), plugin_dl.dl.length); - if (report & REPORT_TO_LOG) - sql_print_error(ER(ER_OUTOFMEMORY), plugin_dl.dl.length); - DBUG_RETURN(0); - } - /* - All st_plugin fields not initialized in the plugin explicitly, are - set to 0. It matches C standard behaviour for struct initializers that - have less values than the struct definition. - */ - for (i=0; - (old=(struct st_mysql_plugin *)(ptr+i*sizeof_st_plugin))->info; - i++) - memcpy(cur+i, old, min(sizeof(cur[i]), sizeof_st_plugin)); - - sym= cur; - } - plugin_dl.plugins= (struct st_mysql_plugin *)sym; + + /* Checks which plugin interface present and reads info */ + if (!(sym= dlsym(plugin_dl.handle, maria_plugin_interface_version_sym))) + { + if (read_mysql_plugin_info(&plugin_dl, + dlsym(plugin_dl.handle, + plugin_interface_version_sym), + dlpath, + report)) + DBUG_RETURN(0); + } + else + { + if (read_maria_plugin_info(&plugin_dl, sym, dlpath, report)) + DBUG_RETURN(0); + } /* Duplicate and convert dll name */ plugin_dl.dl.length= dl->length * files_charset_info->mbmaxlen + 1; @@ -718,7 +900,7 @@ int *argc, char **argv, int report) { struct st_plugin_int tmp; - struct st_mysql_plugin *plugin; + struct st_maria_plugin *plugin; DBUG_ENTER("plugin_add"); if (plugin_find_internal(name, MYSQL_ANY_PLUGIN)) { @@ -1120,8 +1302,8 @@ { uint i; bool is_myisam; - struct st_mysql_plugin **builtins; - struct st_mysql_plugin *plugin; + struct st_maria_plugin **builtins; + struct st_maria_plugin *plugin; struct st_plugin_int tmp, *plugin_ptr, **reap; MEM_ROOT tmp_root; bool reaped_mandatory_plugin= FALSE; @@ -1160,7 +1342,7 @@ /* First we register builtin plugins */ - for (builtins= mysqld_builtins; *builtins; builtins++) + for (builtins= mariadb_builtins; *builtins; builtins++) { for (plugin= *builtins; plugin->info; plugin++) { @@ -1290,7 +1472,7 @@ } -static bool register_builtin(struct st_mysql_plugin *plugin, +static bool register_builtin(struct st_maria_plugin *plugin, struct st_plugin_int *tmp, struct st_plugin_int **ptr) { @@ -1326,7 +1508,7 @@ RETURN false - plugin registered successfully */ -bool plugin_register_builtin(THD *thd, struct st_mysql_plugin *plugin) +bool plugin_register_builtin(THD *thd, struct st_maria_plugin *plugin) { struct st_plugin_int tmp, *ptr; bool result= true; @@ -1455,7 +1637,7 @@ char buffer[FN_REFLEN]; LEX_STRING name= {buffer, 0}, dl= {NULL, 0}, *str= &name; struct st_plugin_dl *plugin_dl; - struct st_mysql_plugin *plugin; + struct st_maria_plugin *plugin; char *p= buffer; DBUG_ENTER("plugin_load_list"); while (list) === modified file 'sql/sql_plugin.h' --- a/sql/sql_plugin.h 2009-05-14 12:03:33 +0000 +++ b/sql/sql_plugin.h 2010-03-11 15:02:03 +0000 @@ -62,8 +62,9 @@ { LEX_STRING dl; void *handle; - struct st_mysql_plugin *plugins; - int version; + struct st_maria_plugin *plugins; + int mysqlversion; + int mariaversion; uint ref_count; /* number of plugins loaded from the library */ }; @@ -72,7 +73,7 @@ struct st_plugin_int { LEX_STRING name; - struct st_mysql_plugin *plugin; + struct st_maria_plugin *plugin; struct st_plugin_dl *plugin_dl; uint state; uint ref_count; /* number of threads using the plugin */ === modified file 'sql/sql_show.cc' --- a/sql/sql_show.cc 2009-11-12 04:31:28 +0000 +++ b/sql/sql_show.cc 2010-03-11 15:02:03 +0000 @@ -94,11 +94,19 @@ return my_snprintf(buf, buf_length, "%d.%d", version>>8,version&0xff); } +static const LEX_STRING maturity_name[]={ + { C_STRING_WITH_LEN(PLUGIN_MATURITY_UNKNOWN_STR) }, + { C_STRING_WITH_LEN(PLUGIN_MATURITY_TEST_STR) }, + { C_STRING_WITH_LEN(PLUGIN_MATURITY_ALPHA_STR) }, + { C_STRING_WITH_LEN(PLUGIN_MATURITY_BETA_STR) }, + { C_STRING_WITH_LEN(PLUGIN_MATURITY_GAMMA_STR) }, + { C_STRING_WITH_LEN(PLUGIN_MATURITY_RELEASE_STR) }}; + static my_bool show_plugins(THD *thd, plugin_ref plugin, void *arg) { TABLE *table= (TABLE*) arg; - struct st_mysql_plugin *plug= plugin_decl(plugin); + struct st_maria_plugin *plug= plugin_decl(plugin); struct st_plugin_dl *plugin_dl= plugin_dlib(plugin); CHARSET_INFO *cs= system_charset_info; char version_buf[20]; @@ -143,7 +151,9 @@ table->field[5]->set_notnull(); table->field[6]->store(version_buf, make_version_string(version_buf, sizeof(version_buf), - plugin_dl->version), + (plugin_dl->mariaversion ? + plugin_dl->mariaversion : + plugin_dl->mysqlversion)), cs); table->field[6]->set_notnull(); } @@ -186,6 +196,26 @@ } table->field[9]->set_notnull(); + if ((uint) plug->maturity <= PLUGIN_MATURITY_RELEASE) + table->field[10]->store(maturity_name[plug->maturity].str, + maturity_name[plug->maturity].length, + cs); + else + { + DBUG_ASSERT(0); + table->field[10]->store("Unknown", 7, cs); + } + table->field[10]->set_notnull(); + + if (plug->version_info) + { + table->field[11]->store(plug->version_info, + strlen(plug->version_info), cs); + table->field[11]->set_notnull(); + } + else + table->field[11]->set_null(); + return schema_table_store_record(thd, table); } @@ -4293,7 +4323,7 @@ if (plugin_state(plugin) != PLUGIN_IS_READY) { - struct st_mysql_plugin *plug= plugin_decl(plugin); + struct st_maria_plugin *plug= plugin_decl(plugin); if (!(wild && wild[0] && wild_case_compare(scs, plug->name,wild))) { @@ -6990,6 +7020,8 @@ {"PLUGIN_AUTHOR", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 1, 0, SKIP_OPEN_TABLE}, {"PLUGIN_DESCRIPTION", 65535, MYSQL_TYPE_STRING, 0, 1, 0, SKIP_OPEN_TABLE}, {"PLUGIN_LICENSE", 80, MYSQL_TYPE_STRING, 0, 1, "License", SKIP_OPEN_TABLE}, + {"PLUGIN_MATURITY", 7, MYSQL_TYPE_STRING, 0, 1, 0, SKIP_OPEN_TABLE}, + {"PLUGIN_AUTH_VERSION", 80, MYSQL_TYPE_STRING, 0, 1, 0, SKIP_OPEN_TABLE}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} }; === modified file 'storage/archive/ha_archive.cc' --- a/storage/archive/ha_archive.cc 2009-09-07 20:50:10 +0000 +++ b/storage/archive/ha_archive.cc 2010-03-11 15:02:03 +0000 @@ -1642,4 +1642,22 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(archive) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &archive_storage_engine, + "ARCHIVE", + "Brian Aker, MySQL AB", + "Archive storage engine", + PLUGIN_LICENSE_GPL, + archive_db_init, /* Plugin Init */ + archive_db_done, /* Plugin Deinit */ + 0x0300 /* 3.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/blackhole/ha_blackhole.cc' --- a/storage/blackhole/ha_blackhole.cc 2008-11-10 20:21:49 +0000 +++ b/storage/blackhole/ha_blackhole.cc 2010-03-11 15:02:03 +0000 @@ -369,3 +369,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(blackhole) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &blackhole_storage_engine, + "BLACKHOLE", + "MySQL AB", + "/dev/null storage engine (anything you write to it disappears)", + PLUGIN_LICENSE_GPL, + blackhole_init, /* Plugin Init */ + blackhole_fini, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/csv/ha_tina.cc' --- a/storage/csv/ha_tina.cc 2009-04-25 10:05:32 +0000 +++ b/storage/csv/ha_tina.cc 2010-03-11 15:02:03 +0000 @@ -1636,4 +1636,21 @@ NULL /* config options */ } mysql_declare_plugin_end; - +maria_declare_plugin(csv) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &csv_storage_engine, + "CSV", + "Brian Aker, MySQL AB", + "CSV storage engine", + PLUGIN_LICENSE_GPL, + tina_init_func, /* Plugin Init */ + tina_done_func, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/example/ha_example.cc' --- a/storage/example/ha_example.cc 2008-02-24 13:12:17 +0000 +++ b/storage/example/ha_example.cc 2010-03-11 15:02:03 +0000 @@ -906,3 +906,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(example) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &example_storage_engine, + "EXAMPLE", + "Brian Aker, MySQL AB", + "Example storage engine", + PLUGIN_LICENSE_GPL, + example_init_func, /* Plugin Init */ + example_done_func, /* Plugin Deinit */ + 0x0001 /* 0.1 */, + NULL, /* status variables */ + example_system_variables, /* system variables */ + "0.1", /* string version */ + PLUGIN_MATURITY_TEST, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/federated/ha_federated.cc' --- a/storage/federated/ha_federated.cc 2009-09-07 20:50:10 +0000 +++ b/storage/federated/ha_federated.cc 2010-03-11 15:02:03 +0000 @@ -3379,3 +3379,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(federated) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &federated_storage_engine, + "FEDERATED", + "Patrick Galbraith and Brian Aker, MySQL AB", + "Federated MySQL storage engine", + PLUGIN_LICENSE_GPL, + federated_db_init, /* Plugin Init */ + federated_done, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_BETA, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/federatedx/ha_federatedx.cc' --- a/storage/federatedx/ha_federatedx.cc 2009-11-03 11:08:09 +0000 +++ b/storage/federatedx/ha_federatedx.cc 2010-03-11 15:02:03 +0000 @@ -3485,9 +3485,27 @@ PLUGIN_LICENSE_GPL, federatedx_db_init, /* Plugin Init */ federatedx_done, /* Plugin Deinit */ - 0x0100 /* 1.0 */, + 0x0200 /* 2.0 */, NULL, /* status variables */ NULL, /* system variables */ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(federated) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &federatedx_storage_engine, + "FEDERATED", + "Patrick Galbraith", + "FederatedX pluggable storage engine", + PLUGIN_LICENSE_GPL, + federatedx_db_init, /* Plugin Init */ + federatedx_done, /* Plugin Deinit */ + 0x0200 /* 2.0 */, + NULL, /* status variables */ + NULL, /* system variables */ + "2.0", /* string version */ + PLUGIN_MATURITY_BETA, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/heap/ha_heap.cc' --- a/storage/heap/ha_heap.cc 2009-09-07 20:50:10 +0000 +++ b/storage/heap/ha_heap.cc 2010-03-11 15:02:03 +0000 @@ -767,3 +767,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(heap) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &heap_storage_engine, + "MEMORY", + "MySQL AB", + "Hash based, stored in memory, useful for temporary tables", + PLUGIN_LICENSE_GPL, + heap_init, + NULL, + 0x0100, /* 1.0 */ + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/ibmdb2i/ha_ibmdb2i.cc' --- a/storage/ibmdb2i/ha_ibmdb2i.cc 2009-07-08 09:10:01 +0000 +++ b/storage/ibmdb2i/ha_ibmdb2i.cc 2010-03-11 15:02:03 +0000 @@ -3357,3 +3357,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(ibmdb2i) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &ibmdb2i_storage_engine, + "IBMDB2I", + "The IBM development team in Rochester, Minnesota", + "IBM DB2 for i Storage Engine", + PLUGIN_LICENSE_GPL, + ibmdb2i_init_func, /* Plugin Init */ + ibmdb2i_done_func, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + NULL, /* status variables */ + ibmdb2i_system_variables, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_UNKNOWN, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/innobase/handler/ha_innodb.cc' --- a/storage/innobase/handler/ha_innodb.cc 2009-10-16 22:57:48 +0000 +++ b/storage/innobase/handler/ha_innodb.cc 2010-03-11 15:02:03 +0000 @@ -8684,6 +8684,24 @@ NULL /* reserved */ } mysql_declare_plugin_end; +maria_declare_plugin(innobase) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &innobase_storage_engine, + innobase_hton_name, + "Innobase OY", + "Supports transactions, row-level locking, and foreign keys", + PLUGIN_LICENSE_GPL, + innobase_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100 /* 1.0 */, + innodb_status_variables_export,/* status variables */ + innobase_system_variables, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* reserved */ +} +maria_declare_plugin_end; /** @brief Initialize the default value of innodb_commit_concurrency. === modified file 'storage/innodb_plugin/handler/i_s.cc' --- a/storage/innodb_plugin/handler/i_s.cc 2009-08-14 15:18:52 +0000 +++ b/storage/innodb_plugin/handler/i_s.cc 2010-03-11 15:02:03 +0000 @@ -455,6 +455,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_trx_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_TRX"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB transactions"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_trx_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table INFORMATION_SCHEMA.innodb_locks */ static ST_FIELD_INFO innodb_locks_fields_info[] = { @@ -730,6 +787,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_locks_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_LOCKS"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB conflicting locks"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_locks_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table INFORMATION_SCHEMA.innodb_lock_waits */ static ST_FIELD_INFO innodb_lock_waits_fields_info[] = { @@ -913,6 +1027,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_lock_waits_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_LOCK_WAITS"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, "Innobase Oy"), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB which lock is blocking which"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_lock_waits_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /*******************************************************************//** Common function to fill any of the dynamic tables: INFORMATION_SCHEMA.innodb_trx @@ -1245,6 +1416,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_mysql_plugin i_s_innodb_cmp_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMP"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compression"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmp_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_cmp_reset = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -1295,6 +1523,64 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmp_reset_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMP_RESET"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compression;" + " reset cumulated counts"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmp_reset_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table information_schema.innodb_cmpmem. */ static ST_FIELD_INFO i_s_cmpmem_fields_info[] = { @@ -1511,6 +1797,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmpmem_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMPMEM"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compressed buffer pool"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmpmem_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_cmpmem_reset = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -1561,6 +1904,64 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmpmem_reset_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMPMEM_RESET"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compressed buffer pool;" + " reset cumulated counts"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmpmem_reset_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /*******************************************************************//** Unbind a dynamic INFORMATION_SCHEMA table. @return 0 on success */ === modified file 'storage/maria/ha_maria.cc' --- a/storage/maria/ha_maria.cc 2009-10-26 11:35:42 +0000 +++ b/storage/maria/ha_maria.cc 2010-03-11 15:02:03 +0000 @@ -3346,9 +3346,27 @@ PLUGIN_LICENSE_GPL, ha_maria_init, /* Plugin Init */ NULL, /* Plugin Deinit */ - 0x0100, /* 1.0 */ + 0x0105, /* 1.5 */ status_variables, /* status variables */ system_variables, /* system variables */ NULL } mysql_declare_plugin_end; +maria_declare_plugin(maria) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &maria_storage_engine, + "MARIA", + "MySQL AB", + "Crash-safe tables with MyISAM heritage", + PLUGIN_LICENSE_GPL, + ha_maria_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0105, /* 1.5 */ + status_variables, /* status variables */ + system_variables, /* system variables */ + "1.5", /* string version */ + PLUGIN_MATURITY_GAMMA, /* maturity */ + NULL +} +maria_declare_plugin_end; === modified file 'storage/myisam/ha_myisam.cc' --- a/storage/myisam/ha_myisam.cc 2009-10-17 19:12:28 +0000 +++ b/storage/myisam/ha_myisam.cc 2010-03-11 15:02:03 +0000 @@ -2183,6 +2183,24 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(myisam) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &myisam_storage_engine, + "MyISAM", + "MySQL AB", + "Default engine as of MySQL 3.23 with great performance", + PLUGIN_LICENSE_GPL, + myisam_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100, /* 1.0 */ + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; #ifdef HAVE_QUERY_CACHE === modified file 'storage/myisammrg/ha_myisammrg.cc' --- a/storage/myisammrg/ha_myisammrg.cc 2009-10-15 21:38:29 +0000 +++ b/storage/myisammrg/ha_myisammrg.cc 2010-03-11 15:02:03 +0000 @@ -1289,3 +1289,21 @@ NULL /* config options */ } mysql_declare_plugin_end; +maria_declare_plugin(myisammrg) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &myisammrg_storage_engine, + "MRG_MYISAM", + "MySQL AB", + "Collection of identical MyISAM tables", + PLUGIN_LICENSE_GPL, + myisammrg_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + 0x0100, /* 1.0 */ + NULL, /* status variables */ + NULL, /* system variables */ + "1.0", /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; === modified file 'storage/pbxt/src/ha_pbxt.cc' --- a/storage/pbxt/src/ha_pbxt.cc 2009-09-03 06:15:03 +0000 +++ b/storage/pbxt/src/ha_pbxt.cc 2010-03-11 15:02:03 +0000 @@ -5507,6 +5507,42 @@ drizzle_declare_plugin_end; #else mysql_declare_plugin_end; +#ifdef MARIADB_BASE_VERSION +maria_declare_plugin(pbxt) +{ /* PBXT */ + MYSQL_STORAGE_ENGINE_PLUGIN, + &pbxt_storage_engine, + "PBXT", + "Paul McCullagh, PrimeBase Technologies GmbH", + "High performance, multi-versioning transactional engine", + PLUGIN_LICENSE_GPL, + pbxt_init, /* Plugin Init */ + pbxt_end, /* Plugin Deinit */ + 0x0001 /* 0.1 */, + NULL, /* status variables */ + pbxt_system_variables, /* system variables */ + "1.0.09g RC3", /* string version */ + PLUGIN_MATURITY_GAMMA, /* maturity */ + NULL /* config options */ +}, +{ /* PBXT_STATISTICS */ + MYSQL_INFORMATION_SCHEMA_PLUGIN, + &pbxt_statitics, + "PBXT_STATISTICS", + "Paul McCullagh, PrimeBase Technologies GmbH", + "PBXT internal system statitics", + PLUGIN_LICENSE_GPL, + pbxt_init_statitics, /* plugin init */ + pbxt_exit_statitics, /* plugin deinit */ + 0x0005, + NULL, /* status variables */ + NULL, /* system variables */ + "1.0.09g RC3", /* string version */ + PLUGIN_MATURITY_GAMMA, /* maturity */ + NULL /* config options */ +} +maria_declare_plugin_end; +#endif #endif #if defined(XT_WIN) && defined(XT_COREDUMP) === modified file 'storage/xtradb/handler/ha_innodb.cc' --- a/storage/xtradb/handler/ha_innodb.cc 2009-10-16 22:57:48 +0000 +++ b/storage/xtradb/handler/ha_innodb.cc 2010-03-11 15:02:03 +0000 @@ -10540,6 +10540,39 @@ i_s_innodb_index_stats, i_s_innodb_patches mysql_declare_plugin_end; +maria_declare_plugin(innobase) +{ /* InnoDB */ + MYSQL_STORAGE_ENGINE_PLUGIN, + &innobase_storage_engine, + innobase_hton_name, + "Innobase Oy", + "Supports transactions, row-level locking, and foreign keys", + PLUGIN_LICENSE_GPL, + innobase_init, /* Plugin Init */ + NULL, /* Plugin Deinit */ + INNODB_VERSION_SHORT, + innodb_status_variables_export,/* status variables */ + innobase_system_variables, /* system variables */ + INNODB_VERSION_STR, /* string version */ + PLUGIN_MATURITY_RELEASE, /* maturity */ + NULL /* reserved */ +}, +i_s_innodb_rseg_maria, +i_s_innodb_buffer_pool_pages_maria, +i_s_innodb_buffer_pool_pages_index_maria, +i_s_innodb_buffer_pool_pages_blob_maria, +i_s_innodb_trx_maria, +i_s_innodb_locks_maria, +i_s_innodb_lock_waits_maria, +i_s_innodb_cmp_maria, +i_s_innodb_cmp_reset_maria, +i_s_innodb_cmpmem_maria, +i_s_innodb_cmpmem_reset_maria, +i_s_innodb_table_stats_maria, +i_s_innodb_index_stats_maria, +i_s_innodb_patches_maria +maria_declare_plugin_end; + /** @brief Initialize the default value of innodb_commit_concurrency. === modified file 'storage/xtradb/handler/i_s.cc' --- a/storage/xtradb/handler/i_s.cc 2009-09-15 10:46:35 +0000 +++ b/storage/xtradb/handler/i_s.cc 2010-03-11 15:02:03 +0000 @@ -390,6 +390,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_patches_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "XTRADB_ENHANCEMENTS"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, "Percona"), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Enhancements applied to InnoDB plugin"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_patches_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + static ST_FIELD_INFO i_s_innodb_buffer_pool_pages_fields_info[] = { @@ -1037,6 +1094,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_buffer_pool_pages_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_BUFFER_POOL_PAGES"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB buffer pool pages"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_innodb_buffer_pool_pages_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, 0x0100 /* 1.0 */), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_buffer_pool_pages_index = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -1086,6 +1200,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_buffer_pool_pages_index_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_BUFFER_POOL_PAGES_INDEX"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB buffer pool index pages"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_innodb_buffer_pool_pages_index_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, 0x0100 /* 1.0 */), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_buffer_pool_pages_blob = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -1135,6 +1306,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_buffer_pool_pages_blob_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_BUFFER_POOL_PAGES_BLOB"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB buffer pool blob pages"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_innodb_buffer_pool_pages_blob_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, 0x0100 /* 1.0 */), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table INFORMATION_SCHEMA.innodb_trx */ static ST_FIELD_INFO innodb_trx_fields_info[] = @@ -1370,6 +1598,64 @@ STRUCT_FLD(__reserved1, NULL) }; + +UNIV_INTERN struct st_maria_plugin i_s_innodb_trx_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_TRX"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB transactions"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_trx_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table INFORMATION_SCHEMA.innodb_locks */ static ST_FIELD_INFO innodb_locks_fields_info[] = { @@ -1645,6 +1931,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_locks_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_LOCKS"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB conflicting locks"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_locks_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /* Fields of the dynamic table INFORMATION_SCHEMA.innodb_lock_waits */ static ST_FIELD_INFO innodb_lock_waits_fields_info[] = { @@ -1828,6 +2171,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_lock_waits_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_LOCK_WAITS"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, "Innobase Oy"), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB which lock is blocking which"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, innodb_lock_waits_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /*********************************************************************** Common function to fill any of the dynamic tables: INFORMATION_SCHEMA.innodb_trx @@ -2160,6 +2560,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmp_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMP"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compression"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmp_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_cmp_reset = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -2210,6 +2667,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmp_reset_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMP_RESET"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compression;" + " reset cumulated counts"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmp_reset_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; /* Fields of the dynamic table information_schema.innodb_cmpmem. */ static ST_FIELD_INFO i_s_cmpmem_fields_info[] = { @@ -2428,6 +2942,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmpmem_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMPMEM"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compressed buffer pool"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmpmem_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_cmpmem_reset = { /* the plugin type (a MYSQL_XXX_PLUGIN value) */ @@ -2478,6 +3049,64 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_cmpmem_reset_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_CMPMEM_RESET"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "Statistics for the InnoDB compressed buffer pool;" + " reset cumulated counts"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_cmpmem_reset_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, INNODB_VERSION_SHORT), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /*********************************************************************** Unbind a dynamic INFORMATION_SCHEMA table. */ static @@ -2657,6 +3286,63 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_rseg_maria = +{ + /* the plugin type (a MYSQL_XXX_PLUGIN value) */ + /* int */ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + + /* pointer to type-specific plugin descriptor */ + /* void* */ + STRUCT_FLD(info, &i_s_info), + + /* plugin name */ + /* const char* */ + STRUCT_FLD(name, "INNODB_RSEG"), + + /* plugin author (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(author, plugin_author), + + /* general descriptive text (for SHOW PLUGINS) */ + /* const char* */ + STRUCT_FLD(descr, "InnoDB rollback segment information"), + + /* the plugin license (PLUGIN_LICENSE_XXX) */ + /* int */ + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + + /* the function to invoke when plugin is loaded */ + /* int (*)(void*); */ + STRUCT_FLD(init, i_s_innodb_rseg_init), + + /* the function to invoke when plugin is unloaded */ + /* int (*)(void*); */ + STRUCT_FLD(deinit, i_s_common_deinit), + + /* plugin version (for SHOW PLUGINS) */ + /* unsigned int */ + STRUCT_FLD(version, 0x0100 /* 1.0 */), + + /* struct st_mysql_show_var* */ + STRUCT_FLD(status_vars, NULL), + + /* struct st_mysql_sys_var** */ + STRUCT_FLD(system_vars, NULL), + + /* string version */ + /* const char * */ + STRUCT_FLD(version_info, "1.0"), + + /* Maturity */ + /* int */ + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + + /* reserved for dependency checking */ + /* void* */ + STRUCT_FLD(__reserved1, NULL) +}; + /*********************************************************************** */ static ST_FIELD_INFO i_s_innodb_table_stats_info[] = @@ -2937,6 +3623,24 @@ STRUCT_FLD(__reserved1, NULL) }; +UNIV_INTERN struct st_maria_plugin i_s_innodb_table_stats_maria = +{ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + STRUCT_FLD(info, &i_s_info), + STRUCT_FLD(name, "INNODB_TABLE_STATS"), + STRUCT_FLD(author, plugin_author), + STRUCT_FLD(descr, "InnoDB table statistics in memory"), + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + STRUCT_FLD(init, i_s_innodb_table_stats_init), + STRUCT_FLD(deinit, i_s_common_deinit), + STRUCT_FLD(version, 0x0100 /* 1.0 */), + STRUCT_FLD(status_vars, NULL), + STRUCT_FLD(system_vars, NULL), + STRUCT_FLD(version_info, "1.0"), + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + STRUCT_FLD(__reserved1, NULL) +}; + UNIV_INTERN struct st_mysql_plugin i_s_innodb_index_stats = { STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), @@ -2952,3 +3656,21 @@ STRUCT_FLD(system_vars, NULL), STRUCT_FLD(__reserved1, NULL) }; + +UNIV_INTERN struct st_maria_plugin i_s_innodb_index_stats_maria = +{ + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN), + STRUCT_FLD(info, &i_s_info), + STRUCT_FLD(name, "INNODB_INDEX_STATS"), + STRUCT_FLD(author, plugin_author), + STRUCT_FLD(descr, "InnoDB index statistics in memory"), + STRUCT_FLD(license, PLUGIN_LICENSE_GPL), + STRUCT_FLD(init, i_s_innodb_index_stats_init), + STRUCT_FLD(deinit, i_s_common_deinit), + STRUCT_FLD(version, 0x0100 /* 1.0 */), + STRUCT_FLD(status_vars, NULL), + STRUCT_FLD(system_vars, NULL), + STRUCT_FLD(version_info, "1.0"), + STRUCT_FLD(maturity, PLUGIN_MATURITY_RELEASE), + STRUCT_FLD(__reserved1, NULL) +}; === modified file 'storage/xtradb/handler/i_s.h' --- a/storage/xtradb/handler/i_s.h 2009-06-25 01:43:25 +0000 +++ b/storage/xtradb/handler/i_s.h 2010-03-11 15:02:03 +0000 @@ -40,4 +40,19 @@ extern struct st_mysql_plugin i_s_innodb_table_stats; extern struct st_mysql_plugin i_s_innodb_index_stats; +extern struct st_maria_plugin i_s_innodb_buffer_pool_pages_maria; +extern struct st_maria_plugin i_s_innodb_buffer_pool_pages_index_maria; +extern struct st_maria_plugin i_s_innodb_buffer_pool_pages_blob_maria; +extern struct st_maria_plugin i_s_innodb_trx_maria; +extern struct st_maria_plugin i_s_innodb_locks_maria; +extern struct st_maria_plugin i_s_innodb_lock_waits_maria; +extern struct st_maria_plugin i_s_innodb_cmp_maria; +extern struct st_maria_plugin i_s_innodb_cmp_reset_maria; +extern struct st_maria_plugin i_s_innodb_cmpmem_maria; +extern struct st_maria_plugin i_s_innodb_cmpmem_reset_maria; +extern struct st_maria_plugin i_s_innodb_patches_maria; +extern struct st_maria_plugin i_s_innodb_rseg_maria; +extern struct st_maria_plugin i_s_innodb_table_stats_maria; +extern struct st_maria_plugin i_s_innodb_index_stats_maria; + #endif /* i_s_h */

1 0