[MDEV-17011] “condition_pushdown_for_derived” optimization not used when using INSERT INTO Created: 2018-08-17  Updated: 2018-08-21  Resolved: 2018-08-21

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.17, 10.2, 10.3
Fix Version/s: 10.2.18

Type: Bug Priority: Major
Reporter: Risato Stefano Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: condition_pushdown_for_derived
Environment:

ubuntu server 16.04 64bit


 Description   

“condition_pushdown_for_derived” optimization correctly use index withing a simple SELECT query, but don't use it if the result records are inserted into an existing table.

Steps to reproduce:

CREATE TABLE `tbl` (
`a` INT(11) NULL DEFAULT NULL,
`b` INT(11) NULL DEFAULT NULL,
INDEX `a` (`a`)
);

INSERT INTO `tbl` (`a`, `b`) VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 2);

CREATE TABLE `tbl2` (
`a` INT(11) NULL DEFAULT NULL,
`b` INT(11) NULL DEFAULT NULL
);

Simple select query:

EXPLAIN
SELECT * FROM
(SELECT a,b FROM tbl GROUP BY a) t
WHERE a=1;

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
2 DERIVED tbl ref a a 5 const 2  

Adding INSERT INTO index is no more used:

EXPLAIN
INSERT INTO tbl2
SELECT * FROM
(SELECT a,b FROM tbl GROUP BY a) t
WHERE a=1;

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where
2 DERIVED tbl ALL NULL NULL NULL NULL 4 Using temporary; Using filesort

For comparison, i tried a query without GROUP BY (in this case the optimization involved is derived_merge), and the optimization is maintained when using INSERT INTO

EXPLAIN
SELECT * FROM
(SELECT * FROM tbl) t
WHERE a=1;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbl ref a a 5 const 2  

EXPLAIN
INSERT INTO tbl2
SELECT * FROM
(SELECT * FROM tbl) t
WHERE a=1;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbl ref a a 5 const 2  


 Comments   
Comment by Alice Sherepa [ 2018-08-17 ]

Thanks for the report!
Reproducible with MyIsam, not with Innodb, Mariadb 10.2, 10.3

CREATE TABLE t1 (a int ,b int)engine=myisam;
 
INSERT INTO t1 (a, b) VALUES (1, 1), (1, 2), (2, 1), (2, 2);
 
 
 
CREATE or replace TABLE t2 (a int, b int)engine=myisam;
 
 
 
explain extended SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;
 
explain extended INSERT INTO t2 SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;
 
 
 
 
 
MariaDB [test]> explain extended SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
 
|    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
2 rows in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select `t`.`a` AS `a`,`t`.`b` AS `b` from (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 1 group by `test`.`t1`.`a`) `t` where `t`.`a` = 1
 
MariaDB [test]> explain extended INSERT INTO t2 SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where                     |
 
|    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
2 rows in set (0.00 sec)

Comment by Igor Babaev [ 2018-08-21 ]

A fix for this bug was pushed into 10.2

Generated at Thu Feb 08 08:33:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.