[MDEV-4517] Upper query conditions push down to (aggregated) VIEWs Created: 2013-05-13  Updated: 2021-12-28  Resolved: 2021-12-22

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Oleksandr Byelkin Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 4
Labels: optimizer

Issue Links:
Relates
relates to MDEV-9197 Pushdown conditions into non-mergeabl... Closed
relates to MDEV-7486 Condition pushdown from HAVING into W... Closed

 Description    

create table employee (dept int, emp_no int, name varchar(64));
 
create table salary (emp_no int, year int, month int, month_salary int);
 
create view v (emp_no, year, income) as 
select 
  emp_no, year, sum(month_salary) as income_per_year 
from
 
   salary 
group by 
  emp_no, year;
 
 
 
select 
  name 
from 
  employee t, v 
where 
  v.emp_no between 1 and 1000 and 
  v.year=2012 and 
  v.emp_no=t.emp_no and income>100000;

Now above query first materialize whole view an then start joining, when the size of materialized table could be reduced by pushing conditions related to the view it its HAVING clause.

Problems:
1. 'strip' view field references when pushed into lower SELECT (SELECT of the view)
2. ref_pointer_array predefined size

Implementation:
1. Clone() method should be done for most (all) items to be able to construct expression for pusing down to the VIEW
2. If we will construct whole item tree then fields for view should be substituted by its translation table entry (also cloned?).
3. It is better to avoid pushing subqueries down to the VIEW because it requre resorting SELECT_LEX tree.
4. for UNION based VIEW condition can be pushed to fake select HAVING.
5. All transformation shouldbe rolled back before next execution.

 Comments   
Comment by Sergei Petrunia [ 2021-12-21 ]

A testcase:

 
 
create table employee (dept int, emp_no int, name varchar(64));
 
create table salary (emp_no int, year int, month int, month_salary int);
 
insert into employee select 
  seq / 100, seq, concat('emp-', seq) from seq_1_to_10000;
 
 
 
insert into salary select
 
  EMP.seq, _YEAR.seq, _MONTH.seq, 99999
 
from
 
  seq_1_to_1000 EMP,
 
  seq_2000_to_2010 _YEAR,
 
  seq_1_to_12 _MONTH;
 
 
 
create view v (emp_no, year, income) as 
select 
  emp_no, year, sum(month_salary) as income_per_year 
from
 
   salary 
group by 
  emp_no, year;
 
 
select 
  name 
from 
  employee t, v 
where 
  v.emp_no between 1 and 1000 and 
  v.year=2012 and 
  v.emp_no=t.emp_no and income>100000;

Comment by Sergei Petrunia [ 2021-12-21 ]

EXPLAIN that I get on 10.8:

 
 
EXPLAIN: {
 
  "query_block": { 
    "select_id": 1, 
    "nested_loop": [ 
      { 
        "table": {
 
          "table_name": "t",
 
          "access_type": "ALL",
 
          "rows": 10123,
 
          "filtered": 100,
 
          "attached_condition": "t.emp_no between 1 and 1000 and t.emp_no is not null"
 
        }
 
      },
 
      {
 
        "table": {
 
          "table_name": "<derived2>",
 
          "access_type": "ref",
 
          "possible_keys": ["key0"],
 
          "key": "key0",
 
          "key_length": "5",
 
          "used_key_parts": ["emp_no"],
 
          "ref": ["test.t.emp_no"],
 
          "rows": 13,
 
          "filtered": 100,
 
          "attached_condition": "v.`year` = 2012 and v.income > 100000",
 
          "materialized": {
 
            "query_block": {
 
              "select_id": 2,
 
              "having_condition": "income > 100000",
 
              "filesort": {
 
                "sort_key": "salary.emp_no",
 
                "temporary_table": {
 
                  "nested_loop": [
 
                    {
 
                      "table": {
 
                        "table_name": "salary",
 
                        "access_type": "ALL",
 
                        "rows": 132130,
 
                        "filtered": 100,
 
                        "attached_condition": "salary.`year` = 2012 and salary.emp_no between 1 and 1000"
 
                      }
 
                    }
 
                  ]
 
                }
 
              }
 
            }
 
          }
 
        }
 
      }
 
    ]
 
  }
 
}
 
1 row in set (0.003 sec)

Comment by Sergei Petrunia [ 2021-12-22 ]

Closing as duplicate of MDEV-9197

Generated at Thu Feb 08 06:57:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.