[MDEV-7486] Condition pushdown from HAVING into WHERE Created: 2015-01-21  Updated: 2019-03-22  Resolved: 2019-02-24

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

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Galina Shalygina (Inactive)
Resolution: Fixed Votes: 1
Labels: Compatibility

Issue Links:
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open
Problem/Incident
causes MDEV-18668 Server crash or ASAN use-after-poison... Closed
causes MDEV-18675 Server crashes in COND_EQUAL::copy Closed
causes MDEV-18937 Wrong result caused by equality pushd... Closed
causes MDEV-19025 No 'Impossible WHERE' message after p... Closed
Relates
relates to MDEV-4517 Upper query conditions push down to (... Closed
relates to MDEV-12387 Push conditions into materialized IN ... Closed
relates to MDEV-4571 CLOSE - Query cache like features Closed
relates to MDEV-9197 Pushdown conditions into non-mergeabl... Closed
Epic Link: Oracle Compatibility

 Description   

Consider a query 

SELECT agg_func(...), ... 
FROM table ... WHERE ... 
GROUP BY col 
HAVING cond1(col) AND cond2(agg_func(...))

Here, cond1(col) can be pushed down from HAVING into WHERE clause. cond2(agg_func(..)) cannot be pushed down.

Basically, we can push down conditions that remain constant across the GROUP BY groups. Pushdown allows to filter out the whole groups.

This task is about

  • splitting the condition that can be pushed down
  • moving from HAVING into WHERE, adjusting it appropriately

 Comments   
Comment by Sergei Petrunia [ 2015-12-01 ]

See also https://mariadb.com/kb/en/mariadb/moving-from-oracle-to-mariadb/#comment_1839

Comment by Sergei Petrunia [ 2018-03-07 ]

Note that despite MDEV-9197 being fixed, this particular issue is not fixed on the current 10.3 tree. I was motivated to check again, because I got this question on the "optimizer status update and plans" session in New York.

create table t20 (a int, b int, c int); 
insert into t20 values (1,1,1), (2,2,2), (3,3,3);

An example that shows HAVING->WHERE pushdown is not happening:

explain format=json 
select a, max(b) from t20 group by a having a<4;
 
 
 
EXPLAIN: {
 
  "query_block": {
 
    "select_id": 1,
 
    "having_condition": "t20.a < 4",
 
    "filesort": {
 
      "sort_key": "t20.a",
 
      "temporary_table": {
 
        "table": {
 
          "table_name": "t20",
 
          "access_type": "ALL",
 
          "rows": 6,
 
          "filtered": 100
 
        }
 
      }
 
    }
 
  }
 
}

An example shows that if one uses derived table, pushdown will happen:

explain format=json
 
select * 
from
 
  (select a, max(b) from t20 group by a) TBL
 
where 
  TBL.a<4;
 
 
 
EXPLAIN: {
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "<derived2>",
 
      "access_type": "ALL",
 
      "rows": 6,
 
      "filtered": 100,
 
      "attached_condition": "TBL.a < 4",
 
      "materialized": {
 
        "query_block": {
 
          "select_id": 2,
 
          "filesort": {
 
            "sort_key": "t20.a",
 
            "temporary_table": {
 
              "table": {
 
                "table_name": "t20",
 
                "access_type": "ALL",
 
                "rows": 6,
 
                "filtered": 100,
 
                "attached_condition": "t20.a < 4"
 
              }
 
            }
 
          }
 
        }
 
      }
 
    }
 
  }
 
}

Comment by Igor Babaev [ 2019-02-24 ]

Patches for this task was pushed into 10.4

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