[MDEV-6003] EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.9
Fix Version/s: 10.0.11
Component/s: None
Labels:
- eits

Description

Prepare the dataset:

create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (

  kp1 int, kp2 int,

  filler1 char(100),

  filler2 char(100),

  key(kp1, kp2)

);

insert into t1

select

  A.a,

  B.a,

  'filler-data-1',

  'filler-data-2'

from ten A, ten B, ten C;

set histogram_size=100;

set use_stat_tables='preferably';

set optimizer_use_condition_selectivity=4;

analyze table t1 persistent for all;

Now, let's try a ref access. Let's start without ref(const):

explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=ten.a+1;

+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra                 |

+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+

|    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL           |   10 |   100.00 | Using where           |

|    1 | SIMPLE      | t1    | ref  | kp1           | kp1  | 10      | j19.ten.a,func |   10 |   100.00 | Using index condition |

+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+

So, ref access will give us 10 rows (on every index lookup). Ok.

explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=4;

+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref             | rows | filtered | Extra       |

+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+

|    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL            |   10 |   100.00 | Using where |

|    1 | SIMPLE      | t1    | ref  | kp1           | kp1  | 10      | j19.ten.a,const |   10 |     9.90 |             |

+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+

This one seems to be wrong. ref access still produces 10 rows, but then filtered=9.90% , which is what selectivity would be if we weren't using ref access.

Indeed, if we disable ref access:

explain extended select * from ten, t1 ignore index(kp1) where t1.kp1=ten.a and t1.kp2=4;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

|    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 |                                                 |

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |     9.90 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

we get selectivity=9.90%.

Attachments

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2014-04-02 00:19

Sergei Petrunia made changes - 2014-04-02 00:19

Field	Original Value	New Value
Labels		eits

Sergei Petrunia made changes - 2014-04-02 00:22

Description

{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
  kp1 int, kp2 int,
  filler1 char(100),
  filler2 char(100),
  key(kp1, kp2)
);

insert into t1
select
  A.a,
  B.a,
  'filler-data-1',
  'filler-data-2'
from ten A, ten B, ten C;
set histogram_size=100;
set use_stat_tables='preferably';
set optimizer_use_condition_selectivity=4;
analyze table t1 persistent for all;
{noformat}

{noformat}
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=ten.a+1;
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,func | 10 | 100.00 | Using index condition |
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
{noformat}
So, we use ref access, which gives us 10 rows. Ok.

{noformat}
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=4;
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,const | 10 | 9.90 | |
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
{noformat}

This one seems to be wrong. ref access still produces 10 rows, but then filtered=9.90% , which is what selectivity would be if we weren't using ref access.

Indeed, if we disable ref access:
{noformat}
explain extended select * from ten, t1 ignore index(kp1) where t1.kp1=ten.a and t1.kp2=ten.a+1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
{noformat}

Prepare the dataset:

{noformat}
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
  kp1 int, kp2 int,
  filler1 char(100),
  filler2 char(100),
  key(kp1, kp2)
);

insert into t1
select
  A.a,
  B.a,
  'filler-data-1',
  'filler-data-2'
from ten A, ten B, ten C;
set histogram_size=100;
set use_stat_tables='preferably';
set optimizer_use_condition_selectivity=4;
analyze table t1 persistent for all;
{noformat}

Now, let's try a ref access. Let's start without ref(const):
{noformat}
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=ten.a+1;
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,func | 10 | 100.00 | Using index condition |
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
{noformat}
So, ref access will give us 10 rows (on every index lookup). Ok.

{noformat}
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=4;
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,const | 10 | 9.90 | |
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
{noformat}

This one seems to be wrong. ref access still produces 10 rows, but then filtered=9.90% , which is what selectivity would be if we weren't using ref access.

Indeed, if we disable ref access:
{noformat}
explain extended select * from ten, t1 ignore index(kp1) where t1.kp1=ten.a and t1.kp2=4;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 9.90 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
{noformat}

we get selectivity=9.90%.

Sergei Petrunia made changes - 2014-04-02 00:22

Assignee

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2014-04-02 00:22

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2014-04-02 00:24

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2014-04-21 14:42

Fix Version/s

10.0.11 [ 15200 ]

Sergei Petrunia added a comment - 2014-04-21 14:42

Patch submitted for review

Sergei Petrunia added a comment - 2014-04-21 14:42 Patch submitted for review

Sergei Petrunia added a comment - 2014-04-25 17:40

For this particular example, these lines in table_cond_selectivity()

            if (keyparts == keyuse->keypart &&

                !(~(keyuse->val->used_tables()) & pos->ref_depend_map) &&

                !(found_part_ref_or_null & keyuse->optimize))

are incorrect

Sergei Petrunia added a comment - 2014-04-25 17:40 For this particular example, these lines in table_cond_selectivity() if (keyparts == keyuse->keypart && !(~(keyuse->val->used_tables()) & pos->ref_depend_map) && !(found_part_ref_or_null & keyuse->optimize)) are incorrect

Sergei Petrunia added a comment - 2014-04-25 17:52

The other place in that function that is wrong (it becomes apparent after you fix the first one) is:

            if (keyuse->val->const_item())

              sel*= table->field[fldno]->cond_selectivity;

Here we should divide, not multiply.

Sergei Petrunia added a comment - 2014-04-25 17:52 The other place in that function that is wrong (it becomes apparent after you fix the first one) is: if (keyuse->val->const_item()) sel*= table->field[fldno]->cond_selectivity; Here we should divide, not multiply.

Sergei Petrunia added a comment - 2014-04-25 18:11

Committed another patch for review.

Sergei Petrunia added a comment - 2014-04-25 18:11 Committed another patch for review.

Sergei Petrunia made changes - 2014-04-28 20:56

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Petrunia made changes - 2014-04-28 20:56

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 37922 ]

MariaDB v2 [ 43385 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 43385 ]

MariaDB v3 [ 63148 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 63148 ]

MariaDB v4 [ 147736 ]

People

Assignee:: Sergei Petrunia

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2014-04-02 00:19

Updated:: 2014-04-28 20:56

Resolved:: 2014-04-28 20:56

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration