Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Consider an example
create table t10 (a int); |
insert into t10 select seq from seq_1_to_10; |
 |
create table t11 ( a int, b int, c int, index(a,b)); |
insert into t11 select A.seq, A.seq, A.seq from seq_1_to_300 A, seq_1_to_100 B; |
insert into t11 select A.seq, A.seq, A.seq from seq_301_to_900 A, seq_1_to_100 B; |
insert into t11 select NULL, NULL, NULL from seq_1_to_100 B; |
Two similar queries,
- one use T.b IS NULL
- the other using T.b=1234
explain
|
select * |
from |
t10,
|
(select a,b, count(*) as CNT from t11 where c>1 group by a, b) T |
where |
T.a=t10.a and T.b is null; |
+------+-----------------+------------+------+---------------+------+---------+------------+------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+------------+------+---------------------------------------------------------------------+
|
| 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t10.a | 9 | Using where |
|
| 2 | LATERAL DERIVED | t11 | ref | a | a | 5 | test.t10.a | 96 | Using index condition; Using where; Using temporary; Using filesort |
|
+------+-----------------+------------+------+---------------+------+---------+------------+------+---------------------------------------------------------------------+
|
explain
|
select *
|
from
|
t10,
|
(select a,b, count(*) as CNT from t11 where c>1 group by a, b) T
|
where
|
T.a=t10.a and T.b=1234;
|
+------+-----------------+------------+------+---------------+------+---------+------------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+------------+------+------------------------------------+
|
| 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t10.a | 9 | Using where |
|
| 2 | LATERAL DERIVED | t11 | ref | a | a | 5 | test.t10.a | 96 | Using index condition; Using where |
|
+------+-----------------+------------+------+---------------+------+---------+------------+------+------------------------------------+
|
Both show that restriction on T.b is not used.
EXPLAIN FORMAT=JSON shows the restriction is pushed down into the subquery. It is just not used for building ref access there:
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.018946916, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t10", |
"access_type": "ALL", |
"loops": 1, |
"rows": 6, |
"cost": 0.0118328, |
"filtered": 100, |
"attached_condition": "t10.a is not null" |
}
|
},
|
{
|
"table": { |
"table_name": "<derived2>", |
"access_type": "ref", |
"possible_keys": ["key0"], |
"key": "key0", |
"key_length": "5", |
"used_key_parts": ["a"], |
"ref": ["test.t10.a"], |
"loops": 6, |
"rows": 9, |
"cost": 0.007114116, |
"filtered": 100, |
"attached_condition": "T.b = 1234", |
"materialized": { |
"lateral": 1, |
"query_block": { |
"select_id": 2, |
"cost": 0.237611244, |
"outer_ref_condition": "t10.a is not null", |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t11", |
"access_type": "ref", |
"possible_keys": ["a"], |
"key": "a", |
"key_length": "5", |
"used_key_parts": ["a"], |
"ref": ["test.t10.a"], |
"loops": 1, |
"rows": 96, |
"cost": 0.17565936, |
"filtered": 100, |
"index_condition": "t11.b = 1234", |
"attached_condition": "t11.c > 1" |
}
|
}
|
]
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
Attachments
Issue Links
- relates to
-
-
- Closed
-