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

DELETE derived table query

    XMLWordPrintable

Details

    • Q3/2025 Maintenance

    Description

      DELETE derived table query taking very long to execute on 10.6 CS version whereas same query was running fast enough on 10.2 CS.
      Workaround:

      set optimizer_switch='split_materialized=off';

      Table definition:

      CREATE TABLE `COMPLETED_TXN_COMP
      		 
      ` (
      		 
        `TEST_TXNID` bigint(20) NOT NULL,
      		 
        `TEST_DATABASE` varchar(128) NOT NULL,
      		 
        `TEST_TABLE` varchar(256) DEFAULT NULL,
      		 
        `TEST_PARTITION` varchar(767) DEFAULT NULL,
      		 
        `TEST_TIMESTAMP` timestamp NOT NULL DEFAULT current_timestamp(),
      		 
        `TEST_WRITEID` bigint(20) DEFAULT NULL,
      		 
        `TEST_UPDATE_DELETE` char(1) NOT NULL,
      		 
        KEY `COMPLETED_TXN_COMP_idx` (`TEST_DATABASE`,`TEST_TABLE`,`TEST_PARTITION`) USING BTREE
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1

      All other relevant info is attached including explain, optimizer_trace output etc.

      Attachments

        Issue Links

          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37050 Lousy join order without EITS

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37057 Wrong result with LATERAL DERIVED

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16057 Using optimization Splitting with Group BY we see an unnecessary attached condition t1.pk IS NOT NULL where pk is a PRIMARY KEY

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37044 derived_with_keys optimization not applied where it should

          • Major - Major loss of function.
          • In Progress

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37181 ref_or_null optimizer depends on the order of conditions in WHERE

          • Major - Major loss of function.
          • Open

          Activity

            People

              Johnston Rex Johnston
              muhammad.irfan Muhammad Irfan
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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