[MDEV-26278] Table elimination does not work across derived tables - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.10.0
Component/s: Optimizer
Labels:

Description

A dataset (not necessarily minimal):

create table t1 (a int);

insert into t1 select seq from seq_1_to_10;

create table t11 (

  a int not null,

  b int,

  key(a)

);

insert into t11 select A.seq, A.seq+B.seq

from

  seq_1_to_100 A,

  seq_1_to_1000 B;

create table t12 (

  pk int primary key,

  col1 int

);

insert into t12 select seq, seq from seq_1_to_100000;

A non-mergeable view where table t12 can be eliminated:

create view v2b as

select

  t11.a as a,

  count(*) as b

from

  t11 left join t12 on t12.pk=t11.b

group by

  t11.a;

EXPLAIN shows it it is indeed eliminated:

explain

select t1.*

from

  t1 left join v2b on v2b.a=t1.a;

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

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

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

|    1 | PRIMARY         | t1         | ALL  | NULL          | NULL | NULL    | NULL    | 10   |             |

|    1 | PRIMARY         | <derived2> | ref  | key0          | key0 | 5       | j5.t1.a | 2    | Using where |

|    2 | LATERAL DERIVED | t11        | ref  | a             | a    | 4       | j5.t1.a | 1    |             |

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

Now, let's add a column from t12 into the select list:

create view v2c as

select

  t11.a as a,

  max(t12.col1) as b

from

  t11 left join t12 on t12.pk=t11.b

group by

  t11.a;

and run a query that doesn't use it:

explain

select t1.*

from

  t1 left join v2c on v2c.a=t1.a;

EXPLAIN shows t12 was not eliminated:

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

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

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

|    1 | PRIMARY         | t1         | ALL    | NULL          | NULL    | NULL    | NULL     | 10   |             |

|    1 | PRIMARY         | <derived2> | ref    | key0          | key0    | 5       | j5.t1.a  | 2    | Using where |

|    2 | LATERAL DERIVED | t11        | ref    | a             | a       | 4       | j5.t1.a  | 1    |             |

|    2 | LATERAL DERIVED | t12        | eq_ref | PRIMARY       | PRIMARY | 4       | j5.t11.b | 1    | Using where |

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

Attachments

Issue Links

causes

MDEV-28881 Server crashes in Dep_analysis_context::create_table_value/check_func_dependency

Closed

MDEV-30007 SIGSEGV in st_select_lex_unit::is_derived_eliminated, runtime error: member access within null pointer of type 'struct TABLE' in st_select_lex_unit::is_derived_eliminated()

Closed

relates to

MDEV-27201 Non-merged derived table: do not compute unused fields

In Review

MDEV-28818 Eliminate derived tables having DISTINCT and/or UNION

Open

MDEV-28869 Eliminated tables are not shown in EXPLAIN [FORMAT=JSON] output

In Review

MDEV-28817 Derived table elimination does not work for multiple occurencies of a field

In Review

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2021-07-29 17:57

Sergei Petrunia made changes - 2021-07-29 18:00

Field	Original Value	New Value
Description	A dataset (not necessarily minimal) {code} create table t1 (a int); insert into t1 select seq from seq_1_to_10; create table t11 ( a int not null, b int, key(a) ); insert into t11 select A.seq, A.seq+B.seq from seq_1_to_100 A, seq_1_to_1000 B; {code} {code} create table t12 ( pk int primary key, col1 int ); insert into t12 select seq, seq from seq_1_to_100000; {code} {code} create view v2b as select t11.a as a, count() as b from t11 left join t12 on t12.pk=t11.b group by t11.a; {code} {code} explain select t1. from t1 left join v2b on v2b.a=t1.a; {code} {code} +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 10 \| \| \| 1 \| PRIMARY \| <derived2> \| ref \| key0 \| key0 \| 5 \| j5.t1.a \| 2 \| Using where \| \| 2 \| LATERAL DERIVED \| t11 \| ref \| a \| a \| 4 \| j5.t1.a \| 1 \| \| +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ {code} create view v2c as select t11.a as a, max(t12.col1) as b from t11 left join t12 on t12.pk=t11.b group by t11.a; {code} {code} explain select t1.* from t1 left join v2c on v2c.a=t1.a; {code} {code} +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 10 \| \| \| 1 \| PRIMARY \| <derived2> \| ref \| key0 \| key0 \| 5 \| j5.t1.a \| 2 \| Using where \| \| 2 \| LATERAL DERIVED \| t11 \| ref \| a \| a \| 4 \| j5.t1.a \| 1 \| \| \| 2 \| LATERAL DERIVED \| t12 \| eq_ref \| PRIMARY \| PRIMARY \| 4 \| j5.t11.b \| 1 \| Using where \| +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ {code}	A dataset (not necessarily minimal): {code:sql} create table t1 (a int); insert into t1 select seq from seq_1_to_10; create table t11 ( a int not null, b int, key(a) ); insert into t11 select A.seq, A.seq+B.seq from seq_1_to_100 A, seq_1_to_1000 B; {code} {code:sql} create table t12 ( pk int primary key, col1 int ); insert into t12 select seq, seq from seq_1_to_100000; {code} A non-mergeable view where table t12 can be eliminated: {code:sql} create view v2b as select t11.a as a, count() as b from t11 left join t12 on t12.pk=t11.b group by t11.a; {code} EXPLAIN shows it it is indeed eliminated: {code:sql} explain select t1. from t1 left join v2b on v2b.a=t1.a; {code} {code} +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 10 \| \| \| 1 \| PRIMARY \| <derived2> \| ref \| key0 \| key0 \| 5 \| j5.t1.a \| 2 \| Using where \| \| 2 \| LATERAL DERIVED \| t11 \| ref \| a \| a \| 4 \| j5.t1.a \| 1 \| \| +------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+ {code} Now, let's add a column from t12 into the select list: {code:sql} create view v2c as select t11.a as a, max(t12.col1) as b from t11 left join t12 on t12.pk=t11.b group by t11.a; {code} but make the view to not use it: {code:sql} explain select t1.* from t1 left join v2c on v2c.a=t1.a; {code} EXPLAIN shows it is not eliminated: {code} +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 10 \| \| \| 1 \| PRIMARY \| <derived2> \| ref \| key0 \| key0 \| 5 \| j5.t1.a \| 2 \| Using where \| \| 2 \| LATERAL DERIVED \| t11 \| ref \| a \| a \| 4 \| j5.t1.a \| 1 \| \| \| 2 \| LATERAL DERIVED \| t12 \| eq_ref \| PRIMARY \| PRIMARY \| 4 \| j5.t11.b \| 1 \| Using where \| +------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+ {code}

Sergei Petrunia made changes - 2021-07-29 18:04

Description

A dataset (not necessarily minimal):

{code:sql}
create table t1 (a int);
insert into t1 select seq from seq_1_to_10;

create table t11 (
  a int not null,
  b int,
  key(a)
);
insert into t11 select A.seq, A.seq+B.seq
from
  seq_1_to_100 A,
  seq_1_to_1000 B;
{code}

{code:sql}
create table t12 (
  pk int primary key,
  col1 int
);
insert into t12 select seq, seq from seq_1_to_100000;
{code}

A non-mergeable view where table t12 can be eliminated:
{code:sql}
create view v2b as
select
  t11.a as a,
  count(*) as b
from
  t11 left join t12 on t12.pk=t11.b
group by
  t11.a;
{code}
EXPLAIN shows it it is indeed eliminated:

{code:sql}
explain
select t1.*
from
  t1 left join v2b on v2b.a=t1.a;
{code}

{code}
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j5.t1.a | 2 | Using where |
| 2 | LATERAL DERIVED | t11 | ref | a | a | 4 | j5.t1.a | 1 | |
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
{code}

Now, let's add a column from t12 into the select list:
{code:sql}
create view v2c as
select
  t11.a as a,
  max(t12.col1) as b
from
  t11 left join t12 on t12.pk=t11.b
group by
  t11.a;
{code}

but make the view to not use it:
{code:sql}
explain
select t1.*
from
  t1 left join v2c on v2c.a=t1.a;
{code}

EXPLAIN shows it is not eliminated:
{code}
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j5.t1.a | 2 | Using where |
| 2 | LATERAL DERIVED | t11 | ref | a | a | 4 | j5.t1.a | 1 | |
| 2 | LATERAL DERIVED | t12 | eq_ref | PRIMARY | PRIMARY | 4 | j5.t11.b | 1 | Using where |
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
{code}

A dataset (not necessarily minimal):

{code:sql}
create table t1 (a int);
insert into t1 select seq from seq_1_to_10;

create table t11 (
  a int not null,
  b int,
  key(a)
);
insert into t11 select A.seq, A.seq+B.seq
from
  seq_1_to_100 A,
  seq_1_to_1000 B;
{code}

{code:sql}
create table t12 (
  pk int primary key,
  col1 int
);
insert into t12 select seq, seq from seq_1_to_100000;
{code}

A non-mergeable view where table t12 can be eliminated:
{code:sql}
create view v2b as
select
  t11.a as a,
  count(*) as b
from
  t11 left join t12 on t12.pk=t11.b
group by
  t11.a;
{code}
EXPLAIN shows it it is indeed eliminated:

{code:sql}
explain
select t1.*
from
  t1 left join v2b on v2b.a=t1.a;
{code}

{code}
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j5.t1.a | 2 | Using where |
| 2 | LATERAL DERIVED | t11 | ref | a | a | 4 | j5.t1.a | 1 | |
+------+-----------------+------------+------+---------------+------+---------+---------+------+-------------+
{code}

Now, let's add a column from t12 into the select list:
{code:sql}
create view v2c as
select
  t11.a as a,
  max(t12.col1) as b
from
  t11 left join t12 on t12.pk=t11.b
group by
  t11.a;
{code}

and run a query that doesn't use it:
{code:sql}
explain
select t1.*
from
  t1 left join v2c on v2c.a=t1.a;
{code}

EXPLAIN shows t12 was not eliminated:
{code}
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j5.t1.a | 2 | Using where |
| 2 | LATERAL DERIVED | t11 | ref | a | a | 4 | j5.t1.a | 1 | |
| 2 | LATERAL DERIVED | t12 | eq_ref | PRIMARY | PRIMARY | 4 | j5.t11.b | 1 | Using where |
+------+-----------------+------------+--------+---------------+---------+---------+----------+------+-------------+
{code}

Sergei Petrunia made changes - 2021-08-05 10:43

Labels

optimizer-feature

Sergei Petrunia made changes - 2021-10-29 14:34

Assignee

Sergei Petrunia [ psergey ]

Sergei Krivonos [ JIRAUSER49805 ]

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

Workflow

MariaDB v3 [ 124004 ]

MariaDB v4 [ 143054 ]

Sergei Petrunia made changes - 2021-12-22 15:58

Labels

optimizer-feature

optimizer-easy optimizer-feature

Sergei Petrunia made changes - 2021-12-22 17:38

Assignee

Sergei Krivonos [ JIRAUSER49805 ]

Oleg Smirnov [ JIRAUSER50405 ]

Oleg Smirnov made changes - 2022-01-13 16:32

Status

Open [ 1 ]

In Progress [ 3 ]

Oleg Smirnov made changes - 2022-01-13 16:33

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.7 [ 24805 ]

Sergei Petrunia made changes - 2022-01-18 15:44

Link

This issue relates to MDEV-27201 [ MDEV-27201 ]

Oleg Smirnov made changes - 2022-04-02 06:51

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2022-04-12 13:22

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.9 [ 26905 ]

Sergei Golubchik made changes - 2022-04-12 13:22

Assignee

Oleg Smirnov [ JIRAUSER50405 ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2022-04-12 13:22

Assignee	Sergei Golubchik [ serg ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2022-05-17 13:09

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Petrunia made changes - 2022-05-19 16:01

Assignee	Sergei Petrunia [ psergey ]	Oleg Smirnov [ JIRAUSER50405 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleg Smirnov made changes - 2022-05-24 02:45

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleg Smirnov made changes - 2022-05-24 02:46

Assignee	Oleg Smirnov [ JIRAUSER50405 ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2022-06-13 07:09

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Sergei Petrunia made changes - 2022-06-13 07:09

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Sergei Petrunia made changes - 2022-06-13 07:09

Assignee

Sergei Petrunia [ psergey ]

Elena Stepanova [ elenst ]

Sergei Petrunia made changes - 2022-06-13 07:12

Assignee	Elena Stepanova [ elenst ]	Oleg Smirnov [ JIRAUSER50405 ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Oleg Smirnov made changes - 2022-06-13 10:11

Link

This issue relates to MDEV-28818 [ MDEV-28818 ]

Oleg Smirnov made changes - 2022-06-14 07:21

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Sergei Petrunia made changes - 2022-06-14 12:23

Assignee

Oleg Smirnov [ JIRAUSER50405 ]

Elena Stepanova [ elenst ]

Sergei Petrunia made changes - 2022-06-15 14:31

Affects Version/s	10.2 [ 14601 ]
Affects Version/s	10.3 [ 22126 ]
Affects Version/s	10.4 [ 22408 ]
Affects Version/s	10.5 [ 23123 ]
Affects Version/s	10.6 [ 24028 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Oleg Smirnov made changes - 2022-06-16 12:03

Link

This issue relates to MDEV-28817 [ MDEV-28817 ]

Oleg Smirnov made changes - 2022-06-16 12:10

Link

This issue relates to MDEV-28869 [ MDEV-28869 ]

Alice Sherepa made changes - 2022-06-17 07:10

Assignee

Elena Stepanova [ elenst ]

Alice Sherepa [ alice ]

Alice Sherepa made changes - 2022-06-17 10:47

Link

This issue causes ~~MDEV-28875~~ [ ~~MDEV-28875~~ ]

Alice Sherepa made changes - 2022-06-17 16:32

Link

This issue causes ~~MDEV-28881~~ [ ~~MDEV-28881~~ ]

Alice Sherepa made changes - 2022-06-20 09:16

Link

This issue causes ~~MDEV-28875~~ [ ~~MDEV-28875~~ ]

Alice Sherepa made changes - 2022-07-20 08:34

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Alice Sherepa made changes - 2022-07-20 08:35

Assignee

Alice Sherepa [ alice ]

Oleg Smirnov [ JIRAUSER50405 ]

Oleg Smirnov made changes - 2022-07-31 14:12

Fix Version/s		10.10.0 [ 27912 ]
Fix Version/s	10.10 [ 27530 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Roel Van de Paar made changes - 2022-11-14 21:41

Link

This issue causes ~~MDEV-30007~~ [ ~~MDEV-30007~~ ]

Ralf Gebhardt made changes - 2023-03-21 10:44

Labels

optimizer-easy optimizer-feature

Preview_10.10 optimizer-easy optimizer-feature

People

Assignee:: Oleg Smirnov

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2021-07-29 17:57

Updated:: 2023-03-21 10:44

Resolved:: 2022-07-31 14:12

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration