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

Expensive subqueries may be evaluated during optimization in merge_key_fields

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.25
    • 5.5.28
    • None
    • None

    Description

      The following example extracted from https://mariadb.atlassian.net/browse/MDEV-430 results in evaluation of expensive subqueries during query optimization.

      SET optimizer_switch = 'materialization=on,semijoin=on';

      CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (8),(0);

      CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (4,'j'),(6,'v');

      CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES ('b'),('c');

      EXPLAIN
      SELECT * FROM t1 WHERE a = (
      SELECT MAX(b) FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 )
      ) OR a = 10;

      The relevant call stack is:

      #6 0x084866e9 in Item_singlerow_subselect::val_int (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item_subselect.cc:1196
      #7 0x0819053c in Item::update_null_value (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item.h:1055
      #8 0x08493a94 in Item_subselect::is_null (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item_subselect.h:172
      #9 0x082879fd in merge_key_fields (start=0x970e410, new_fields=0x970e42c, end=0x970e448, and_level=3) at /home/psergey/dev2/5.5-look7/sql/sql_select.cc:3874

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            timour Timour Katchaounov (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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