Details
-
Bug
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5(EOL), 10.6, 10.11, 11.4, 11.8, 12.0(EOL), 12.1(EOL)
-
None
-
Q2/2026 Server Development
Description
In the example below, table merge optimization is not used for delete with derived table but with view it is used.
Setup (requires Sequence engine)
create table t1 (a int, b int); insert into t1 select seq, seq from seq_1_to_10; |
create table t2 (a int, b int); insert into t2 select seq, seq from seq_1_to_10; |
create table t3 (a int, b int); insert into t3 select seq, seq from seq_1_to_10; |
DELETE does not merge the derived table
DELETE:
MariaDB [test]> explain delete t1.* from t1, (select t2.b as b1 from t2, t3 where t3.a=t2.a) DT where t1.b+1=1+DT.b1;
|
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 2 | DERIVED | t3 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
Equivalent SELECT (derived table merged):
MariaDB [test]> explain select * from t1, (select t2.b as b1 from t2, t3 where t3.a=t2.a) DT where t1.b+1=1+DT.b1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
DELETE from VIEW merges the derived table
Transforming the subquery to a VIEW and rewriting the DELETE shows that the VIEW is merged:
create view DT as select t2.b as b1 from t2, t3 where t3.a=t2.a;
|
explain delete t1.* from t1, DT where t1.b+1=1+DT.b1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
Attachments
Issue Links
- causes
-
MDEV-39550 Support [NO_]DERIVED_TABLE_MERGE for UPDATE, DELETE
-
- Open
-
- relates to
-
-
- Closed
-