Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2-2, 10.2.2-4
Description
It is possible to do condition pushdown into non-mergable VIEWs or derived tables.
Example:
select ...
|
from
|
(select col1, max(col2) as max_val
|
from t2
|
group by t1 ) TBL
|
where
|
col1 !='foo' and max_val > 100
|
here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause.
Implementation challenges
Moving condition from one select into another changes the context which condition is used in.
Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables.
Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from.
The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE.
Pushdown may also require adjustments to ref_pointer_array. Sanja and Igor seem to understand the details of this.
Attachments
Issue Links
- is part of
-
MDEV-10137 Providing compatibility to other databases
-
- Open
-
- relates to
-
-
- Closed
-
-
MDEV-10884 Wrong query result with condition pushdown into derived table with ORDER BY ... LIMIT
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
- links to
(1 links to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
It is possible to do condition pushdown into non-mergable VIEWs or derived tables. Example: {noformat} select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100 {noformat} here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause. |
| Component/s | Optimizer [ 10200 ] | |
| Labels | optimizer |
| Fix Version/s | 10.2 [ 14601 ] |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Description |
It is possible to do condition pushdown into non-mergable VIEWs or derived tables. Example: {noformat} select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100 {noformat} here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause. |
It is possible to do condition pushdown into non-mergable VIEWs or derived tables. Example: {noformat} select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100 {noformat} here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause. h2. Implementation challenges Moving condition from one select into another changes the context which condition is used in. Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables. Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from. The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE. |
| Description |
It is possible to do condition pushdown into non-mergable VIEWs or derived tables. Example: {noformat} select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100 {noformat} here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause. h2. Implementation challenges Moving condition from one select into another changes the context which condition is used in. Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables. Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from. The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE. |
It is possible to do condition pushdown into non-mergable VIEWs or derived tables. Example: {noformat} select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100 {noformat} here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause. h2. Implementation challenges Moving condition from one select into another changes the context which condition is used in. Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables. Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from. The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE. Pushdown may also require adjustments to {{ref_pointer_array}}. Sanja and Igor seem to understand the details of this. |
| Issue Type | Bug [ 1 ] | Task [ 3 ] |
| Labels | optimizer | gsoc16 optimizer |
| Link | This issue is part of MDEV-10137 [ MDEV-10137 ] |
| Assignee | Michael Widenius [ monty ] |
| Assignee | Michael Widenius [ monty ] | Sergei Petrunia [ psergey ] |
| Labels | gsoc16 optimizer | Compatibility gsoc16 optimizer |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Labels | Compatibility gsoc16 optimizer | Compatibility foundation gsoc16 optimizer |
| Labels | Compatibility foundation gsoc16 optimizer | Compatibility contribution foundation gsoc16 optimizer |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
| Remote Link | This issue links to "GitHub Pull Request #216 (Web Link)" [ 27450 ] |
| Assignee | Igor Babaev [ igor ] | Michael Widenius [ monty ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Michael Widenius [ monty ] | Igor Babaev [ igor ] |
| Rank | Ranked higher |
| Sprint | 10.2.2-2 [ 92 ] |
| Rank | Ranked lower |
| Sprint | 10.2.2-2 [ 92 ] | 10.2.2-2, 10.2.2-3 [ 92, 94 ] |
| Sprint | 10.2.2-2, 10.2.2-3 [ 92, 94 ] | 10.2.2-2, 10.2.2-3, 10.2.2-4 [ 92, 94, 96 ] |
| Sprint | 10.2.2-2, 10.2.2-3, 10.2.2-4 [ 92, 94, 96 ] | 10.2.2-2, 10.2.2-4 [ 92, 96 ] |
| Epic Link | MDEV-10872 [ 58182 ] |
| Link |
This issue relates to |
| Fix Version/s | 10.2.2 [ 22013 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Labels | Compatibility contribution foundation gsoc16 optimizer | Approved Compatibility contribution foundation gsoc16 optimizer |
| Attachment | MDEV-9197.zip [ 42680 ] |
| Labels | Approved Compatibility contribution foundation gsoc16 optimizer | Approved Compatibility NRE-307517 contribution foundation gsoc16 optimizer |
| NRE Projects | NRE-307517 |
| Labels | Approved Compatibility NRE-307517 contribution foundation gsoc16 optimizer | Approved Compatibility contribution foundation gsoc16 optimizer |
| NRE Approved | Yes [ 10304 ] |
| Labels | Approved Compatibility contribution foundation gsoc16 optimizer | Compatibility contribution foundation gsoc16 optimizer |
| Link | This issue relates to MDEV-21388 [ MDEV-21388 ] |
| Workflow | MariaDB v3 [ 72747 ] | MariaDB v4 [ 132736 ] |