[MDEV-21243] Join buffer: condition is checked in wrong place for range access Created: 2019-12-06  Updated: 2020-08-25  Resolved: 2020-01-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.3.10, 10.3.21, 10.4
Fix Version/s: 10.3.22, 10.4.12

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-21383 Possible range plan is not used under... Closed

 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.

 Comments   
Comment by Sergei Petrunia [ 2019-12-07 ]

The condition that is attached to the table is created in JOIN_TAB::make_scan_filter.

make_scan_filter is invoked in two places:

  • from check_join_cache_usage() when using BKA.
  • from make_join_select()

Relevant code in make_join_select :

      bool use_quick_range=0;
 
      ...
 
      if (tab->type == JT_REF && tab->quick &&
 
	  (((uint) tab->ref.key == tab->quick->index &&
 
	    tab->ref.key_length < tab->quick->max_used_key_length) ||
 
           (!is_hash_join_key_no(tab->ref.key) &&
 
            tab->table->intersect_keys.is_set(tab->ref.key))))
 
      {
 
	/* Range uses longer key;  Use this instead of ref on key */
 
	tab->type=JT_ALL;
 
	use_quick_range=1;
 
        ...
 
      }


	else if (tab->type == JT_ALL && ! use_quick_range)
 
	{
 
          ...
 
            if (tab->make_scan_filter())
 
              DBUG_RETURN(1);

That is, make_scan_filter is not invoked when this heuristic is fired:

	/* Range uses longer key;  Use this instead of ref on key */

I see no reason for this.

Comment by Sergei Petrunia [ 2019-12-07 ]

Patch committed into bb-10.3-mdev21243 tree.
http://lists.askmonty.org/pipermail/commits/2019-December/014087.html

Generated at Thu Feb 08 09:05:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.