Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
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