WL#6033: Add transformed query to EXPLAIN INSERT/UPDATE/DELETE

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

EXPLAIN EXTENDED SELECT statement outputs a notification(warning) with a transformed SELECT query text. However, EXPLAIN EXTENDED INSERT/REPLACE/UPDATE/DELETE commands lack of the same feature.

See BUG#62617: EXPLAIN EXTENDED WIHT DELETE, UPDATE ETC.

EXTENDED keyword has been removed in mysql-8.0 following WL#9678.

F1) There should be a warning for EXPLAIN insert/update/replace/delete statements.

F2) The content of the warning should reflect the query transformations that was done during optimization.

CREATE TABLE tbl1 (c1 INT NOT NULL, c2 varchar (64), PRIMARY KEY (c1)) ENGINE =
InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (0)
 (SUBPARTITION subp0,
  SUBPARTITION subp1),
 PARTITION p1 VALUES LESS THAN (100000)
 (SUBPARTITION subp6,
  SUBPARTITION subp7));


CREATE TABLE tbl2 (c1 int);
CREATE TABLE tbl3 LIKE tbl2;
INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO tbl2 SELECT c1 FROM tbl1;
INSERT INTO tbl3 SELECT c1 FROM tbl2;


Functional and Non-Functional Requirements

EXTENDED keyword has been removed in mysql-8.0 following WL#9678. 

F1) DELETE
   F1.a) Single table DELETE:
         EXPLAIN DELETE LOW_PRIORITY QUICK IGNORE FROM tbl1
           PARTITION (p1)
           WHERE c1 > 0
           ORDER BY c2
           LIMIT 10;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions        | type  | possible_keys | key   
 | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+
|  1 | DELETE      | tbl1  | p1_subp6,p1_subp7 | range | PRIMARY       | PRIMARY
| 4       | const |    3 |   100.00 | Using where; Using filesort |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)


mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | delete low_priority ignore from `test`.`tbl1` PARTITION (`p1`)
where (`test`.`tbl1`.`c1` > 0) order by `test`.`tbl1`.`c2` limit 10 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)




  F1.b) Multi-table DELETE:
        EXPLAIN DELETE LOW_PRIORITY QUICK IGNORE 
             tbl1 , tbl2 
             FROM tbl1 , tbl2, tbl3
             WHERE tbl1.c1 > 0;
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions        | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | tbl1  | p1_subp6,p1_subp7 | ALL  | PRIMARY       | NULL |
NULL    | NULL |    3 |   100.00 | Using where |
|  1 | DELETE      | tbl2  | NULL              | ALL  | NULL          | NULL |
NULL    | NULL |    3 |   100.00 | NULL        |
|  1 | SIMPLE      | tbl3  | NULL              | ALL  | NULL          | NULL |
NULL    | NULL |    3 |   100.00 | NULL        |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql>  SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                                                 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | delete low_priority ignore `test`.`tbl1`, `test`.`tbl2` from
`test`.`tbl1` join `test`.`tbl2` join `test`.`tbl3` where (`test`.`tbl1`.`c1` > 0) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



F2) UPDATE
   F2.a) Single table UPDATE:
         EXPLAIN UPDATE LOW_PRIORITY IGNORE tbl1 SET c1 = 20 WHERE c1 > 100;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions        | type  | possible_keys | key   
 | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | tbl1  | p1_subp6,p1_subp7 | range | PRIMARY       | PRIMARY
| 4       | const |    1 |   100.00 | Using where; Using temporary |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                       |
+-------+------+-------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update low_priority ignore `test`.`tbl1` set `test`.`tbl1`.`c1`
= 20 where (`test`.`tbl1`.`c1` > 100) |
+-------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


   F2.b) Multi-table UPDATE:
mysql> EXPLAIN UPDATE LOW_PRIORITY IGNORE tbl1 LEFT JOIN tbl2 ON tbl1.c1 =
tbl2.c1 SET tbl1.c1 = 20 WHERE tbl1.c1 > 0;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions        | type  | possible_keys | key   
 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | tbl1  | p1_subp6,p1_subp7 | range | PRIMARY       | PRIMARY
| 4       | NULL |    3 |   100.00 | Using where |
|  1 | SIMPLE      | tbl2  | NULL              | ALL   | NULL          | NULL  
 | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                                               
                                        |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update low_priority ignore `test`.`tbl1``test`.`tbl1` left join
`test`.`tbl2` on((`test`.`tbl2`.`c1` = `test`.`tbl1`.`c1`)) set
`test`.`tbl1`.`c1` = 20 where (`test`.`tbl1`.`c1` > 0) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



F3) INSERT
   F3.a) INSERT-values
         EXPLAIN INSERT DELAYED IGNORE INTO tbl1 PARTITION(p0, p1) (c1, c2)
VALUES (1, 'a'), (2, 'b');
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions        | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | tbl1  | p1_subp6,p1_subp7 | ALL  | NULL          | NULL |
NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                     
                                                         |
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
| Warning | 3005 | INSERT DELAYED is no longer supported. The statement was
converted to INSERT.                                         |
| Note    | 1003 | insert into ignore `test`.`tbl1` PARTITION (`p0`,`p1`)
(`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') |
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

     F3.b) INSERT VALUES.. ON DUPLICATE KEY ....
           EXPLAIN INSERT LOW_PRIORITY IGNORE INTO tbl1 PARTITION(p0, p1) (c1,
c2) VALUES (1, 'a'), (2, 'b') ON DUPLICATE KEY UPDATE c2 = 'c' ;
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions        | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | tbl1  | p1_subp6,p1_subp7 | ALL  | NULL          | NULL |
NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                                               
                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | insert into low_priority ignore `test`.`tbl1` PARTITION
(`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') on
duplicate key update `test`.`tbl1`.`c2` = 'c' |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 


     F3.c) INSERT ... SELECT
           EXPLAIN INSERT DELAYED IGNORE INTO tbl1 PARTITION(p0, p1) (c1, c2)
SELECT c1, 'a' FROM tbl2 ON DUPLICATE KEY UPDATE c2 = 'c' ;
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                          | type |
possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | tbl1  | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL  | NULL 
        | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  1 | SIMPLE      | tbl2  | NULL                                | ALL  | NULL 
        | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                     
                                                                               
                                                                                  |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3005 | INSERT DELAYED is no longer supported. The statement was
converted to INSERT.                                                           
                                                                               
      |
| Note    | 1003 | insert into ignore `test`.`tbl1` PARTITION (`p0`,`p1`)
(`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) /* select#1 */ select `test`.`tbl2`.`c1`
AS `c1`,'a' AS `a` from `test`.`tbl2` on duplicate key update `test`.`tbl1`.`c2`
= 'c' |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


  f3.d) REPLACE ... VALUES
        EXPLAIN REPLACE LOW_PRIORITY INTO tbl1 PARTITION(p0, p1) (c1, c2) VALUES
(1, 'a'), (2, 'b');
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions        | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | REPLACE     | tbl1  | p1_subp6,p1_subp7 | ALL  | NULL          | NULL |
NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                              |
+-------+------+------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | replace low_priority into `test`.`tbl1` PARTITION (`p0`,`p1`)
(`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') |
+-------+------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

       F3.e) REPLACE ... SELECT
             EXPLAIN REPLACE DELAYED INTO tbl1 PARTITION(p0, p1) (c1, c2) SELECT
c1, 'a' FROM tbl2;
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                          | type |
possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | REPLACE     | tbl1  | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL  | NULL 
        | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  1 | SIMPLE      | tbl2  | NULL                                | ALL  | NULL 
        | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                     
                                                                               
                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3005 | REPLACE DELAYED is no longer supported. The statement was
converted to REPLACE.                                                          
                              |
| Note    | 1003 | replace into `test`.`tbl1` PARTITION (`p0`,`p1`)
(`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) /* select#1 */ select `test`.`tbl2`.`c1`
AS `c1`,'a' AS `a` from `test`.`tbl2` |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)


    F3.f) REPLACE ... SET (//Internally there is no distinction between SET and
VALUES. So explain will also have no difference.) 

          EXPLAIN REPLACE  INTO tbl1 PARTITION(p0, p1) SET c1 = (SELECT c1 from
tbl2);
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                          | type |
possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | REPLACE     | tbl1  | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL  | NULL 
        | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  2 | SUBQUERY    | tbl2  | NULL                                | ALL  | NULL 
        | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (3.52 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                                                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | replace into `test`.`tbl1` PARTITION (`p0`,`p1`)
(`test`.`tbl1`.`c1`) values ((select `test`.`tbl2`.`c1` from `test`.`tbl2`)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)