Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.10, 10.3.21, 5.5(EOL), 10.4(EOL)
-
None
Description
When one uses
- join buffer on table $T, with BNL-join
- the table $T uses a range access $RA
- There is also a possible ref access on the same index, but $RA uses
more key parts
then the non-index condition $COND on $T is checked after $T is joined with the contents of the join buffer. It should be checked before that.
(Note that Only condition on non-indexed columns is affected. Index Condition Pushdown works - whatever can be pushed to ICP, will be pushed and checked at appropriate place)
create table ten(a int primary key); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table hundred (a int); |
insert into hundred select A.a + 10*B.a from ten A, ten B; |
|
|
create table t12 ( |
kp1 int, |
kp2 int, |
col1 int, |
col2 int, |
key (kp1, kp2) |
);
|
|
|
insert into t12 |
select
|
A.a,
|
B.a,
|
A.a + 100*B.a,
|
A.a + 100*B.a
|
from |
hundred A, hundred B;
|
analyze table t12; |
explain format=json
|
select * |
from ten A, t12 B |
where |
B.kp1=A.a and |
B.kp1 <= 10 and |
B.kp2 <= 10 and |
B.col1 +1 < 33333;
|
shows
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "A",
|
"access_type": "index",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["a"],
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "A.a <= 10",
|
"using_index": true
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "B",
|
"access_type": "range",
|
"possible_keys": ["kp1"],
|
"key": "kp1",
|
"key_length": "10",
|
"used_key_parts": ["kp1", "kp2"],
|
"rows": 1011,
|
"filtered": 10.148,
|
"index_condition": "B.kp2 <= 10"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "54",
|
"join_type": "BNL",
|
"attached_condition": "B.kp1 = A.a and B.col1 + 1 < 33333"
|
}
|
}
|
} |
|
Note that B.col1 + 1 < 33333 is checked when doing the join with the join buffer.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Patch committed into bb-10.3-mdev21243 tree.
http://lists.askmonty.org/pipermail/commits/2019-December/014087.html
The condition that is attached to the table is created in JOIN_TAB::make_scan_filter.
make_scan_filter is invoked in two places:
Relevant code in make_join_select :
...{tab->type=JT_ALL;use_quick_range=1;...}{...DBUG_RETURN(1);That is, make_scan_filter is not invoked when this heuristic is fired:
I see no reason for this.