Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
Here is the test (picked from derived_cond_pushdown.test in the main suite)
CREATE TABLE t1 (pk1 INT PRIMARY KEY, f INT) ENGINE=Aria; |
INSERT INTO t1 VALUES (1,0),(2,0); |
CREATE TABLE t2 (pk2 INT PRIMARY KEY) ENGINE=Aria; |
INSERT INTO t2 VALUES (1),(2),(3); |
CREATE VIEW v2 AS SELECT pk2, COUNT(*) AS cnt FROM t2 GROUP BY pk2; |
Now when i run this analyze command
MariaDB [test]> ANALYZE FORMAT=JSON SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5; |
the output is
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 1.1568,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"possible_keys": ["PRIMARY"],
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.0677,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t1.f <> 5"
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "4",
|
"used_key_parts": ["pk2"],
|
"ref": ["test.t1.pk1"],
|
"r_loops": 2,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.7043,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 2,
|
"r_total_time_ms": 0.8569,
|
"outer_ref_condition": "t1.pk1 is not null",
|
"table": {
|
"table_name": "t2",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["pk2"],
|
"ref": ["test.t1.pk1"],
|
"r_loops": 2,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0723,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
}
|
}
|
} |
|
So if you look closely at the "outer_ref_condition": "t1.pk1 is not null". But as we see in the create table statement t1.pk1 is a primary key and so it is guaranteed to be NOT NULL so this is a useless condition to have.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Here is the test (picked from derived_cond_pushdown.test in the main suite)
{code:sql} CREATE TABLE t1 (pk1 INT PRIMARY KEY, f INT) ENGINE=Aria; INSERT INTO t1 VALUES (1,0),(2,0); CREATE TABLE t2 (pk2 INT PRIMARY KEY) ENGINE=Aria; INSERT INTO t2 VALUES (1),(2),(3); CREATE VIEW v2 AS SELECT pk2, COUNT(*) AS cnt FROM t2 GROUP BY pk2; {code} Now when i run this analyze command {code:sql} MariaDB [test]> ANALYZE FORMAT=JSON SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5; {code} the output is {noformat} | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 1.1568, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["PRIMARY"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0677, "filtered": 100, "r_filtered": 100, "attached_condition": "t1.f <> 5" }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["pk2"], "ref": ["test.t1.pk1"], "r_loops": 2, "rows": 2, "r_rows": 1, "r_total_time_ms": 0.7043, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 2, "r_loops": 2, "r_total_time_ms": 0.8569, "outer_ref_condition": "t1.pk1 is not null", "table": { "table_name": "t2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk2"], "ref": ["test.t1.pk1"], "r_loops": 2, "rows": 1, "r_rows": 1, "r_total_time_ms": 0.0723, "filtered": 100, "r_filtered": 100, "using_index": true } } } } } } | {noformat} So if you look closely at the *"outer_ref_condition": "t1.pk1 is not null"*. But as we see in the create table statement t1.pk1 is a primary key and so it is guaranteed to be NOT NULL so this is a useless condition to have. |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Summary | Split by Group by has an unnecessary attached condition t2.pk IS NOT NULL where pk is a PRIMARY KEY | Splitting with Group BY has an unnecessary attached condition t2.pk IS NOT NULL where pk is a PRIMARY KEY |
| Summary | Splitting with Group BY has an unnecessary attached condition t2.pk IS NOT NULL where pk is a PRIMARY KEY | Using optimization Splitting with Group BY we see an unnecessary attached condition t1.pk IS NOT NULL where pk is a PRIMARY KEY |
| Assignee | Varun Gupta [ varun ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Igor Babaev [ igor ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.3.7 [ 23005 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 86851 ] | MariaDB v4 [ 154281 ] |
Patch
http://lists.askmonty.org/pipermail/commits/2018-April/012473.html