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.