[MDEV-42008] FILTER fails to take advantage of optimizations that a WHERE could - Jira

XML

Word

Printable

Side by side with a query that has a WHERE clause in instead of a FILTER, the WHERE clause is getting a superior query plan.

These are taking place in a contained cases:

There isn't another non-aggregated result;
or the other aggregated result as the same criteria; or
~~there is a HAVING clause around the aggregated result (that isn't HAVING agg IS NULL)~~

create table t (a int, b int, key(a));

insert into t values (10,1),(20,1),(30,2);

--source include/analyze-format.inc

explain format=json select max(a) from t where a=20;

select max(a) from t where a=20;

--source include/analyze-format.inc

explain format=json select max(a) filter (where a=20) from t;

select max(a) filter (where a=20) from t;

--source include/analyze-format.inc

explain format=json select b, max(a) as m from t where a=20 group by a having m > 0;

select b, max(a) as m from t where a=20 group by a having m > 0;

--source include/analyze-format.inc

explain format=json select b, max(a) filter (where a=20) as m from t group by a having m > 0;

select b, max(a) filter (where a=20) as m from t group by a having m > 0;

--source include/analyze-format.inc

explain format=json select max(b) from t where a=20 group by a;

select max(b) from t where a=20;

--source include/analyze-format.inc

explain format=json select max(b) filter (where a=20) from t group by a;

select max(b) filter (where a=20) from t;

--source include/analyze-format.inc

explain format=json select max(b) from t where b=20;

select max(b) from t where b=20;

--source include/analyze-format.inc

explain format=json select max(b) filter (where b=20) from t;

select max(b) filter (where b=20) from t;

drop table t;

optimised away (opt_sum_query)
create table t (a int, b int, key(a));
insert into t values (10,1),(20,1),(30,2);

explain format=json select max(a) from t where a=20;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"message": "Select tables optimized away"
}
}
}
select max(a) from t where a=20;
max(a)
20

explain format=json select max(a) filter (where a=20) from t;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "index",
"key": "a",
"key_length": "5",
"used_key_parts": ["a"],
"loops": 1,
"rows": 3,
"cost": "REPLACED",
"filtered": 100,
"using_index": true
}
}
]
}
}
select max(a) filter (where a=20) from t;
max(a) filter (where a=20)
20

nested loop without filesort
explain format=json select b, max(a) as m from t where a=20 group by a having m > 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"having_condition": "m > 0",
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": ["a"],
"key": "a",
"key_length": "5",
"used_key_parts": ["a"],
"ref": ["const"],
"loops": 1,
"rows": 1,
"cost": "REPLACED",
"filtered": 100
}
}
]
}
}
select b, max(a) as m from t where a=20 group by a having m > 0;
b m
1 20

explain format=json select b, max(a) filter (where a=20) as m from t group by a having m > 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"having_condition": "m > 0",
"filesort": {
"sort_key": "t.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "REPLACED",
"filtered": 100
}
}
]
}
}
}
}
select b, max(a) filter (where a=20) as m from t group by a having m > 0;
b m
1 20

ref scan without temp_table
explain format=json select max(b) from t where a=20 group by a;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": ["a"],
"key": "a",
"key_length": "5",
"used_key_parts": ["a"],
"ref": ["const"],
"loops": 1,
"rows": 1,
"cost": "REPLACED",
"filtered": 100
}
}
]
}
}
select max(b) from t where a=20;
max(b)
1

explain format=json select max(b) filter (where a=20) from t group by a;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"filesort": {
"sort_key": "t.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "REPLACED",
"filtered": 100
}
}
]
}
}
}
}
select max(b) filter (where a=20) from t;
max(b) filter (where a=20)
1

use attached condition
explain format=json select max(b) from t where b=20;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "REPLACED",
"filtered": 100,
"attached_condition": "t.b = 20"
}
}
]
}
}

select max(b) from t where b=20;
max(b)
NULL
explain format=json select max(b) filter (where b=20) from t;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "REPLACED",
"filtered": 100
}
}
]
}
}
select max(b) filter (where b=20) from t;
max(b) filter (where b=20)
NULL
drop table t;

is caused by

MDEV-24943 Add FILTER clause

Estimated:

Remaining:

2d 5h 40m

Logged:

2h 20m

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.