[MDEV-11161] second time execution of derived tables (SP/PS) do not use indices. Created: 2016-10-27  Updated: 2017-02-20  Resolved: 2016-11-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.54, 10.0.29, 10.1.20

Type: Bug Priority: Critical
Reporter: Oleksandr Byelkin Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

EXPAIN shows the same plan, but ANALYSE STATEMENT shows reality: full table scan on second execution

create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
 
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
 
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
 
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
 
 
 
create procedure p1()
 
 EXPLAIN SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 
 
 
create procedure p2()
 
 ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 
 
 
call p1();
 
call p1();
 
call p2();
 
call p2();
 
 
 
prepare stmt1 from "ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum";
 
 
 
execute stmt1;
 
execute stmt1;
 
 
 
drop procedure p1,p2;
 
drop table t1,t2;

for first execution:

"table": {
"table_name": "<derived2>",
"access_type": "ref",
"possible_keys": ["key0"],
"key": "key0",
"key_length": "7",
"used_key_parts": ["matintnum"],
"ref": ["test.m2.matintnum"],
"r_loops": 9,
"rows": 2,
"r_rows": 0.6667,
"r_total_time_ms": 0.0207,
"filtered": 100,
"r_filtered": 100,
"materialized": {

second execution:
"block-nl-join": {
"table":

{ "table_name": "<derived2>", "access_type": "ALL", "r_loops": 1, "rows": 6, "r_rows": 6, "r_total_time_ms": 0.0011, "filtered": 100, "r_filtered": 100 }

,
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "(d.matintnum = m2.matintnum)",
"r_filtered": 11.111,
"materialized": {

 Comments   
Comment by Oleksandr Byelkin [ 2016-10-31 ]

On second execution temporary table for derived table created and filled way earlier (just after optimize derived first call). Then it has no keys to use:

m2 type: ALL q_keys: 0 refs: 0 key: -1 len: 0
d type: ref q_keys: 1 refs: 1 key: 0 len: 7
select used
refs: test.m2.matintnum

vs

Info about JOIN
m2 type: ALL q_keys: 0 refs: 0 key: -1 len: 0
d type: ALL q_keys: 0 refs: 0 key: -1 len: 0
select used

and so second time full scan used

Comment by Igor Babaev [ 2016-11-09 ]

This is a mariadb-5.5 bug (actually a mariadb-5.3 bug).

This bug could be observed in maridb-5.5:

MariaDB [test]> prepare stmt1 from "SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum";
 
Query OK, 0 rows affected (0.00 sec)
 
Statement prepared
 
 
 
MariaDB [test]> flush status;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> execute stmt1;
 
+--------+--------+
 
| pla_id | mat_id |
 
+--------+--------+
 
|    102 |      1 |
 
|    101 |      1 |                                                                                                        
|    100 |      1 |                                                                                                        
|    104 |      2 |
 
|    103 |      2 |
 
|    105 |      3 |
 
+--------+--------+
 
6 rows in set (0.00 sec)
 
 
 
MariaDB [test]> show status like '%Handler_read%';
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 21    |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 6     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_rnd         | 6     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 27    |
 
+--------------------------+-------+
 
8 rows in set (0.00 sec)
 
 
 
MariaDB [test]> flush status;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> execute stmt1;
 
+--------+--------+
 
| pla_id | mat_id |
 
+--------+--------+
 
|    100 |      1 |
 
|    101 |      1 |
 
|    102 |      1 |
 
|    103 |      2 |
 
|    104 |      2 |
 
|    105 |      3 |
 
+--------+--------+
 
6 rows in set (0.00 sec)
 
 
 
MariaDB [test]> show status like '%Handler_read%';
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 12    |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 0     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_rnd         | 6     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 34    |
 
+--------------------------+-------+
 
8 rows in set (0.00 sec)

The different handler counters for the above two executions of the same prepared statement
show that the second execution uses an execution plan that differs from that used by the
first execution.

Comment by Igor Babaev [ 2016-11-12 ]

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

Comment by Igor Babaev [ 2016-11-14 ]

Will,
To whom do you address your comment?
This is a one line fix. Could be done any time for 10.1.
Yet I have no clue about the next 10.1 release.

Generated at Thu Feb 08 07:47:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.