Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
10.0.34, 10.1.31, 10.2.13, 10.1.32, 10.3.6-1
Description
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery
(expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...)
|
and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true:
expr[1]=col[1] AND ...
|
Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery.
This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see MDEV-9197).
EDIT
optimizer_switch flag name is condition_pushdown_for_subquery
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see mdev-9197). |
| Description |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see mdev-9197). |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see mdev-9197). |
| Summary | Push conditions into materialized subquiries | Push conditions into materialized subqueries |
| Description |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see mdev-9197). |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see |
| Component/s | Admin statements [ 11400 ] |
| Assignee | Igor Babaev [ igor ] | Galina Shalygina [ shagalla ] |
| Labels | gsoc17 |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Sprint | 10.0.34 [ 224 ] |
| Sprint | 10.0.34 [ 224 ] | 10.0.34, 10.1.31 [ 224, 225 ] |
| Sprint | 10.0.34, 10.1.31 [ 224, 225 ] | 10.0.34, 10.1.31, 10.2.13 [ 224, 225, 228 ] |
| Assignee | Galina Shalygina [ shagalla ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Igor Babaev [ igor ] | Galina Shalygina [ shagalla ] |
| Sprint | 10.0.34, 10.1.31, 10.2.13 [ 224, 225, 228 ] | 10.0.34, 10.1.31, 10.2.13, 10.1.32 [ 224, 225, 228, 235 ] |
| Sprint | 10.0.34, 10.1.31, 10.2.13, 10.1.32 [ 224, 225, 228, 235 ] | 10.0.34, 10.1.31, 10.2.13, 10.1.32, 10.3.6 [ 224, 225, 228, 235, 237 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Fix Version/s | 10.4.0 [ 23115 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Summary | Push conditions into materialized subqueries | Push conditions into materialized IN subqueries |
| Description |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see |
The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery {noformat} (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...) {noformat} and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true: {noformat} expr[1]=col[1] AND ... {noformat} Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery. This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see *EDIT* {{optimizer_switch}} flag name is {{condition_pushdown_for_subquery}} |
| Workflow | MariaDB v3 [ 80160 ] | MariaDB v4 [ 133195 ] |