[MDEV-8063] Unconditional ANALYZE DELETE does not delete rows Created: 2015-04-28  Updated: 2015-05-19  Resolved: 2015-05-19

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: 10.1
Fix Version/s: 10.1.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: verified


 Description    

MariaDB [test]> create table t1 (i int);
 
Query OK, 0 rows affected (1.53 sec)
 
 
 
MariaDB [test]> insert into t1 values (1),(2);
 
Query OK, 2 rows affected (0.10 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> analyze delete from t1;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra             |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL |    2 |   NULL |     NULL |       NULL | Deleting all rows |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
|    2 |
 
+------+
 
2 rows in set (0.00 sec)

Test case

 
create table t1 (i int);
 
insert into t1 values (1),(2);
 
analyze delete from t1;
 
select * from t1;
 
 
 
drop table t1;



 Comments   
Comment by Vicențiu Ciorbaru [ 2015-05-18 ]

After investigating this issue, I am in a bit of a predicament.

First, according to our documentation, analyze delete and update should perform the updates or deletes. It is my understanding that this is a technical limitation as we sometimes need to actually perform the operation, in order for us to report all the statistics produced by analyze statement.

However, after examining the code, we have all sorts of interesting inconsistencies, where we actually prefer to not execute anything. I am for not executing the query plan, but it is not consistent with the documentation.

  /*
 
    Ok, we have generated a query plan for the DELETE.
 
     - if we're running EXPLAIN DELETE, goto produce explain output 
     - otherwise, execute the query plan
 
  */
 
  if (thd->lex->describe)
 
    goto produce_explain_and_leave;

This part lacks any handling for the analyze_stmt flag. I think this is an oversight, yet here we specifically prefer to not execute the query plan.

Nonetheless, I have produced a patch that fixes the behavior so that we do perform the delete.

Comment by Sergei Petrunia [ 2015-05-18 ]

> However, after examining the code, we have all sorts of interesting inconsistencies, where we actually prefer to not execute anything.

I don't see any inconsistencies in the quoted code. We produce the query plan. Then, if we are in EXPLAIN statement, we jump to the code that prints the query plan and leaves. If we are not in EXPLAIN (i.e. we are in an actual DELETE or in ANALYZE DELETE), we execute the code.

Comment by Vicențiu Ciorbaru [ 2015-05-19 ]

Fixed with:
https://github.com/MariaDB/server/commit/29c7aff76784ff2b8329387f69c73e7282f28b2a
and
https://github.com/MariaDB/server/commit/bac6bbab804b6e5428fe9413069148c1dd701a3e

Two patches in this case due to a wrong push with the first patch.

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