[MDEV-11859] Execution plans of the the first and the second executions of a prepared statement are not the same. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.55
Component/s: Optimizer
Labels:
None

Description

If the where condition of a query contains a non-correlated, non-expensive subquery then
then different executions of the prepared statement made for the query may use
different execution plans.

The following test case demonstrates this.

 create table t1 (id int, c varchar(3), key idx(c))engine=myisam;

insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');

prepare stmt1 from

"explain extended

 select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";

execute stmt1;

execute stmt1;

deallocate prepare stmt1;

prepare stmt2 from

"explain extended

 select * from t1 where (1, 2) in ( select 3, 4 )";

execute stmt2;

execute stmt2;

deallocate prepare stmt2;

drop table t1;

Here for the first and the second executions of the prepared statement stmt1 we have:

MariaDB [test]> execute stmt1;

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

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

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

|    1 | PRIMARY     | t1    | ref  | idx           | idx  | 6       | const |    1 |   100.00 | Using index condition |

|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL  | NULL |     NULL | No tables used        |

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

MariaDB [test]> execute stmt1;

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

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

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

|    1 | PRIMARY     | t1    | ALL  | idx           | NULL | NULL    | NULL |    4 |   100.00 | Using where    |

|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |

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

While for the first and the second executions of the prepared statement stmt2 the plans are
as follows:

ariaDB [test]> execute stmt2;

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

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

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

|    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |

|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used   |

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

MariaDB [test]> execute stmt2;

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

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

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

|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |                |

|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |

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

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2017-01-21 04:40

Igor Babaev (Inactive) made changes - 2017-01-21 04:41

Field	Original Value	New Value
Assignee		Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2017-01-21 04:41

Description

If the where condition of a query contains a non-correlated, non-expensive subquery then
then different executions of the prepared statement made for the query may use
different execution plans.
The following test case demonstrate this.
{noformat}
create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');

prepare stmt1 from
"explain extended
select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

prepare stmt2 from
"explain extended
select * from t1 where (1, 2) in ( select 3, 4 )";
execute stmt2;
execute stmt2;
deallocate prepare stmt2;

drop table t1;
{noformat}

Here for the first and the second executions of the prepared statement stmt1 we have:
{noformat}
MariaDB [test]> execute stmt1;
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | PRIMARY | t1 | ref | idx | idx | 6 | const | 1 | 100.00 | Using index condition |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+

MariaDB [test]> execute stmt1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | idx | NULL | NULL | NULL | 4 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
{noformat}

While for the first and the second executions of the prepared statement stmt2 the plans are
as follows:
{noformat}
ariaDB [test]> execute stmt2;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

MariaDB [test]> execute stmt2;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
{noformat}

If the where condition of a query contains a non-correlated, non-expensive subquery then
then different executions of the prepared statement made for the query may use
different execution plans.

The following test case demonstrates this.
{noformat}
create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');

prepare stmt1 from
"explain extended
select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

prepare stmt2 from
"explain extended
select * from t1 where (1, 2) in ( select 3, 4 )";
execute stmt2;
execute stmt2;
deallocate prepare stmt2;

drop table t1;
{noformat}

Here for the first and the second executions of the prepared statement stmt1 we have:
{noformat}
MariaDB [test]> execute stmt1;
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | PRIMARY | t1 | ref | idx | idx | 6 | const | 1 | 100.00 | Using index condition |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+

MariaDB [test]> execute stmt1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | idx | NULL | NULL | NULL | 4 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
{noformat}

While for the first and the second executions of the prepared statement stmt2 the plans are
as follows:
{noformat}
ariaDB [test]> execute stmt2;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

MariaDB [test]> execute stmt2;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
{noformat}

Igor Babaev (Inactive) made changes - 2017-01-21 06:59

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2017-01-24 21:16

Fix Version/s		5.5.55 [ 22311 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 79304 ]

MariaDB v4 [ 151571 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-01-21 04:40

Updated:: 2017-01-24 21:16

Resolved:: 2017-01-24 21:16

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