[MDEV-12670] Merge of derived tables / views is not transparent for semi-join optimizations - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 5.5.57
Component/s: Optimizer
Labels:
None

Description

For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table

SELECT * FROM ( Q ) t

is always executed without semi-join optimization.
In a general case any merge of a derived table / view blocks usage of semi-join optimizations
for it.
Here' s a test case that demonstrate this problem.

create table t1 (a int) engine=myisam;

insert into t1  values (5),(3),(2),(7),(2),(5),(1);

create table t2 (b int, index idx(b)) engine=myisam;

insert into t2 values (2),(3),(2),(1),(3),(4);

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

insert into t2 select b+10 from t2;

analyze table t1,t2;

explain select a from t1 where a in (select b from t2);

explain select * from (select a from t1 where a in (select b from t2)) t;

For the EXPLAINs from the testcase the following is returned:

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

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

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

|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    7 | Using where                 |

|    1 | PRIMARY     | t2    | ref  | idx           | idx  | 5       | test.t1.a |  140 | Using index; FirstMatch(t1) |

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

MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t;

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

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

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

|    1 | SIMPLE             | t1    | ALL            | NULL          | NULL | NULL    | NULL |    7 | Using where |

|    3 | DEPENDENT SUBQUERY | t2    | index_subquery | idx           | idx  | 5       | func |  140 | Using index |

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

This is not expected as the merge transformation applied to t converts Q1 to Q.

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2017-05-03 03:04

Igor Babaev (Inactive) made changes - 2017-05-03 03:06

Field	Original Value	New Value
Description	For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table {noformat} SELECT * FROM ( Q ) t {noformat\| is always executed without semi-join optimization. In a general case any merge of a derived table / view blocks usage of semi-join optimizations for it. Here' s a test case that demonstrate this problem. {noformat} create table t1 (a int) engine=myisam; insert into t1 values (5),(3),(2),(7),(2),(5),(1); create table t2 (b int, index idx(b)) engine=myisam; insert into t2 values (2),(3),(2),(1),(3),(4); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; explain select a from t1 where a in (select b from t2); explain select * from (select a from t1 where a in (select b from t2)) t; {noformat} For the EXPLAINs from the testcase the following is returned: {noformat} MariaDB [test]> explain select a from t1 where a in (select b from t2); +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 1 \| PRIMARY \| t2 \| ref \| idx \| idx \| 5 \| test.t1.a \| 11 \| Using index; FirstMatch(t1) \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t; +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 3 \| DEPENDENT SUBQUERY \| t2 \| index_subquery \| idx \| idx \| 5 \| func \| 89 \| Using index \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ {noformat} This is not expected as the merge transformation applied to t converts Q1 to Q.	For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table {noformat} SELECT * FROM ( Q ) t {noformat} is always executed without semi-join optimization. In a general case any merge of a derived table / view blocks usage of semi-join optimizations for it. Here' s a test case that demonstrate this problem. {noformat} create table t1 (a int) engine=myisam; insert into t1 values (5),(3),(2),(7),(2),(5),(1); create table t2 (b int, index idx(b)) engine=myisam; insert into t2 values (2),(3),(2),(1),(3),(4); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; explain select a from t1 where a in (select b from t2); explain select * from (select a from t1 where a in (select b from t2)) t; {noformat} For the EXPLAINs from the testcase the following is returned: {noformat} MariaDB [test]> explain select a from t1 where a in (select b from t2); +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 1 \| PRIMARY \| t2 \| ref \| idx \| idx \| 5 \| test.t1.a \| 11 \| Using index; FirstMatch(t1) \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t; +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 3 \| DEPENDENT SUBQUERY \| t2 \| index_subquery \| idx \| idx \| 5 \| func \| 89 \| Using index \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ {noformat} This is not expected as the merge transformation applied to t converts Q1 to Q.

Igor Babaev (Inactive) made changes - 2017-05-03 03:36

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2017-05-05 16:44

Description	For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table {noformat} SELECT * FROM ( Q ) t {noformat} is always executed without semi-join optimization. In a general case any merge of a derived table / view blocks usage of semi-join optimizations for it. Here' s a test case that demonstrate this problem. {noformat} create table t1 (a int) engine=myisam; insert into t1 values (5),(3),(2),(7),(2),(5),(1); create table t2 (b int, index idx(b)) engine=myisam; insert into t2 values (2),(3),(2),(1),(3),(4); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; explain select a from t1 where a in (select b from t2); explain select * from (select a from t1 where a in (select b from t2)) t; {noformat} For the EXPLAINs from the testcase the following is returned: {noformat} MariaDB [test]> explain select a from t1 where a in (select b from t2); +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 1 \| PRIMARY \| t2 \| ref \| idx \| idx \| 5 \| test.t1.a \| 11 \| Using index; FirstMatch(t1) \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t; +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 3 \| DEPENDENT SUBQUERY \| t2 \| index_subquery \| idx \| idx \| 5 \| func \| 89 \| Using index \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ {noformat} This is not expected as the merge transformation applied to t converts Q1 to Q.	For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table {noformat} SELECT * FROM ( Q ) t {noformat} is always executed without semi-join optimization. In a general case any merge of a derived table / view blocks usage of semi-join optimizations for it. Here' s a test case that demonstrate this problem. {noformat} create table t1 (a int) engine=myisam; insert into t1 values (5),(3),(2),(7),(2),(5),(1); create table t2 (b int, index idx(b)) engine=myisam; insert into t2 values (2),(3),(2),(1),(3),(4); insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; analyze table t1,t2; explain select a from t1 where a in (select b from t2); explain select * from (select a from t1 where a in (select b from t2)) t; {noformat} For the EXPLAINs from the testcase the following is returned: {noformat} MariaDB [test]> explain select a from t1 where a in (select b from t2); +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 1 \| PRIMARY \| t2 \| ref \| idx \| idx \| 5 \| test.t1.a \| 11 \| Using index; FirstMatch(t1) \| +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+ MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t; +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ \| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 7 \| Using where \| \| 3 \| DEPENDENT SUBQUERY \| t2 \| index_subquery \| idx \| idx \| 5 \| func \| 89 \| Using index \| +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+ {noformat} This is not expected as the merge transformation applied to t converts Q1 to Q.
Summary	Merge of derived tables / views iis not transparent for semi-join optimizations	Merge of derived tables / views is not transparent for semi-join optimizations

Igor Babaev (Inactive) made changes - 2017-05-05 17:37

Description

For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table
{noformat}
SELECT * FROM ( Q ) t
{noformat}
is always executed without semi-join optimization.
In a general case any merge of a derived table / view blocks usage of semi-join optimizations
for it.
Here' s a test case that demonstrate this problem.
{noformat}
create table t1 (a int) engine=myisam;
insert into t1 values (5),(3),(2),(7),(2),(5),(1);
create table t2 (b int, index idx(b)) engine=myisam;
insert into t2 values (2),(3),(2),(1),(3),(4);
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
analyze table t1,t2;
explain select a from t1 where a in (select b from t2);
explain select * from (select a from t1 where a in (select b from t2)) t;
{noformat}
For the EXPLAINs from the testcase the following is returned:
{noformat}
MariaDB [test]> explain select a from t1 where a in (select b from t2);
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 11 | Using index; FirstMatch(t1) |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t;
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 3 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 89 | Using index |
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
{noformat}
This is not expected as the merge transformation applied to t converts Q1 to Q.

For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table
{noformat}
SELECT * FROM ( Q ) t
{noformat}
is always executed without semi-join optimization.
In a general case any merge of a derived table / view blocks usage of semi-join optimizations
for it.
Here' s a test case that demonstrate this problem.
{noformat}
create table t1 (a int) engine=myisam;
insert into t1 values (5),(3),(2),(7),(2),(5),(1);
create table t2 (b int, index idx(b)) engine=myisam;
insert into t2 values (2),(3),(2),(1),(3),(4);
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
insert into t2 select b+10 from t2;
analyze table t1,t2;
explain select a from t1 where a in (select b from t2);
explain select * from (select a from t1 where a in (select b from t2)) t;
{noformat}
For the EXPLAINs from the testcase the following is returned:
{noformat}
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 140 | Using index; FirstMatch(t1) |
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+

MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t;
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 3 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 140 | Using index |
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
{noformat}
This is not expected as the merge transformation applied to t converts Q1 to Q.

Igor Babaev (Inactive) made changes - 2017-05-09 07:53

Assignee	Igor Babaev [ igor ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Igor Babaev (Inactive) made changes - 2017-05-10 16:10

Assignee

Oleksandr Byelkin [ sanja ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2017-05-10 16:10

Assignee

Igor Babaev [ igor ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin added a comment - 2017-05-10 16:24

ok to push

Oleksandr Byelkin added a comment - 2017-05-10 16:24 ok to push

Oleksandr Byelkin made changes - 2017-05-10 16:24

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2017-05-10 16:27

Assignee

Oleksandr Byelkin [ sanja ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) added a comment - 2017-05-10 16:29 - edited

The fix for this bug was pushed into the 5.5 tree.

Igor Babaev (Inactive) added a comment - 2017-05-10 16:29 - edited The fix for this bug was pushed into the 5.5 tree.

Igor Babaev (Inactive) made changes - 2017-05-10 16:29

Fix Version/s		5.5.57 [ 22539 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 80579 ]

MariaDB v4 [ 152073 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-05-03 03:04

Updated:: 2017-05-17 23:16

Resolved:: 2017-05-10 16:29

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