Details
-
Bug
-
Status: In Progress (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8, 12.3, 13.1, 12.3.3
-
MariaDB 12.3.3-MariaDB-ubu2404 on Linux x86_64
-
Q4/2026 Server Development
Description
A multi-table DELETE using a LEFT JOIN, ORDER BY and LIMIT selects the pairs (3,NULL) and (1,12), but deletes only the two left-table rows. The selected right-table row with ID 12 remains.
The following single-session reproducer prepares the same DELETE once and executes it under two temporary-table memory budgets. Each execution is rolled back so that both use identical data.
CREATE DATABASE delete_test; |
USE delete_test; |
|
|
CREATE TABLE a ( |
id INT PRIMARY KEY |
) ENGINE=InnoDB;
|
|
|
CREATE TABLE b ( |
id INT PRIMARY KEY, |
aid INT, |
sort_value INT |
) ENGINE=InnoDB;
|
|
|
INSERT INTO a VALUES (1),(2),(3); |
INSERT INTO b VALUES (11,1,30),(12,1,10),(21,2,20); |
|
|
SET SESSION sql_buffer_result=1; |
|
|
EXPLAIN
|
DELETE a, b |
FROM a LEFT JOIN b ON a.id=b.aid |
ORDER BY COALESCE(b.sort_value,-1),a.id,b.id |
LIMIT 2;
|
|
|
SELECT a.id,b.id |
FROM a LEFT JOIN b ON a.id=b.aid |
ORDER BY COALESCE(b.sort_value,-1),a.id,b.id |
LIMIT 2;
|
|
|
PREPARE del_stmt FROM |
'DELETE a, b
|
FROM a LEFT JOIN b ON a.id=b.aid
|
ORDER BY COALESCE(b.sort_value,-1),a.id,b.id
|
LIMIT 2'; |
|
|
SET SESSION tmp_table_size=16777216; |
SET SESSION max_heap_table_size=16777216; |
START TRANSACTION; |
EXECUTE del_stmt; |
SELECT 'affected_mode_0',ROW_COUNT(); |
SELECT 'a_mode_0',GROUP_CONCAT(id ORDER BY id) FROM a; |
SELECT 'b_mode_0',GROUP_CONCAT(id ORDER BY id) FROM b; |
ROLLBACK; |
|
|
SET SESSION tmp_table_size=1024; |
SET SESSION max_heap_table_size=16384; |
START TRANSACTION; |
EXECUTE del_stmt; |
SELECT 'affected_mode_1',ROW_COUNT(); |
SELECT 'a_mode_1',GROUP_CONCAT(id ORDER BY id) FROM a; |
SELECT 'b_mode_1',GROUP_CONCAT(id ORDER BY id) FROM b; |
ROLLBACK; |
|
|
DEALLOCATE PREPARE del_stmt; |
DROP DATABASE delete_test; |
EXPLAIN reports Using temporary; Using filesort, and the preliminary SELECT identifies the two joined rows used by the limit:
id id
|
3 NULL
|
1 12
|
Both executions produce the same wrong result:
affected_mode_0 2
|
a_mode_0 2
|
b_mode_0 11,12,21
|
|
|
affected_mode_1 2
|
a_mode_1 2
|
b_mode_1 11,12,21
|
The selected rows identify three distinct physical targets: rows 3 and 1 from a, and row 12 from b. The DELETE should therefore affect three rows, leaving {{a=
{2}}} and {{b=
{11,21}}}. Instead, row 12 in b survives while both selected rows from a are deleted.
The two budget settings reproduced the same result; they configure temporary-table memory limits but do not establish which internal temporary-table engine was used.
This occurs when an ordered, limited multi-table DELETE materializes rows from a LEFT JOIN whose selected set includes both a null-complemented row and a matched right-table row.
Attachments
Issue Links
- relates to
-
MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.
-
- Closed
-
-
-
- Closed
-
