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

DELETE derived table query

    XMLWordPrintable

Details

    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 with highly skewed data distribution

          • Major - Major loss of function.
          • Stalled

          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.
          • Closed
          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.
          • Closed

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-37230 Incorrect handling of NULL join conditions when using split-materialized

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

          Activity

            People

              Johnston Rex Johnston
              muhammad.irfan Muhammad Irfan
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.