Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41793

DELETE with descending MEMORY BTREE scan stops after one row despite LIMIT 5

    XMLWordPrintable

Details

    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

          Activity

            People

              Gosselin Dave Gosselin
              Milo liu
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 1.5d
                  1.5d
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.