Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.8, 12.3, 13.1, 12.3.3
-
Linux/amd64
Description
Running the following SQL in a MariaDB client reproduces the failure on MariaDB 12.3.3 on Linux/amd64.
DROP DATABASE IF EXISTS memory_delete; |
CREATE DATABASE memory_delete; |
USE memory_delete; |
|
|
CREATE TABLE descending_rows ( |
k INT NOT NULL, |
pad CHAR(20), |
INDEX idx_k USING BTREE (k) |
) ENGINE=MEMORY;
|
INSERT INTO descending_rows |
SELECT seq, LPAD(seq, 20, '0') FROM seq_1_to_20; |
|
|
FLUSH STATUS;
|
DELETE FROM descending_rows FORCE INDEX (idx_k) |
ORDER BY k DESC LIMIT 5; |
SELECT 'descending_affected_rows', ROW_COUNT(); |
SELECT VARIABLE_NAME, VARIABLE_VALUE |
FROM information_schema.SESSION_STATUS |
WHERE VARIABLE_NAME IN ('HANDLER_READ_LAST', 'HANDLER_READ_PREV') |
ORDER BY VARIABLE_NAME; |
SELECT 'remaining', COUNT(*), MIN(k), MAX(k), SUM(k > 15) |
FROM descending_rows; |
|
|
CREATE TABLE ascending_rows LIKE descending_rows; |
INSERT INTO ascending_rows |
SELECT seq, LPAD(seq, 20, '0') FROM seq_1_to_20; |
DELETE FROM ascending_rows FORCE INDEX (idx_k) |
ORDER BY k ASC LIMIT 5; |
SELECT 'ascending_affected_rows', ROW_COUNT(); |
SELECT 'ascending_remaining', COUNT(*), MIN(k), MAX(k), SUM(k <= 5) |
FROM ascending_rows; |
|
|
DROP DATABASE memory_delete; |
The recorded output is:
descending_affected_rows 1
|
HANDLER_READ_LAST 1
|
HANDLER_READ_PREV 1
|
remaining 19 1 19 4
|
ascending_affected_rows 5
|
ascending_remaining 15 6 20 0
|
The descending DELETE removes only key 20, reports one affected row, and leaves 19 rows with keys 1 through 19. The equivalent ascending DELETE correctly removes five rows and leaves keys 6 through 20.
DELETE ... ORDER BY k DESC LIMIT 5 over 20 distinct indexed keys should delete the five highest-key rows, report five affected rows, and leave keys 1 through 15. The limit permits five qualifying deletions, and the descending ordering selects those five keys first.
Source inspection indicates that heap_rlast() does not preserve lastkey. Deleting the first row invalidates the structural cursor, after which heap_rprev() cannot resume strictly before the deleted key.
This occurs when a limited DELETE scans a MEMORY BTREE index in descending key order.
Attachments
Issue Links
- relates to
-
MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.
-
- Closed
-