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

ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding

Details

    Description

      Create a test dataset:

      create table t1 (a int);
      		 
      insert into t1 select * from test.one_k;
      		 
       
      		 
      create table tsubq(
      		 
        id int primary key,
      		 
        key1 int,
      		 
        col1 int,
      		 
        key(key1)
      		 
      ) engine=innodb;
      		 
       
      		 
      insert into tsubq 
        select A.a + B.a*1000, A.a, 123456 from test.one_k A, test.one_k B;

      Then, check the plan:

      explain select 
         (SELECT 
            concat(id, '-', key1, '-', col1)
      		 
          FROM tsubq
      		 
          WHERE tsubq.key1 = t1.a
      		 
          ORDER BY tsubq.id ASC LIMIT 1)
      		 
      from 
        t1;
      		 

      +------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
      		 
      | id   | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      		 
      +------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
      		 
      |    1 | PRIMARY            | t1    | ALL   | NULL          | NULL    | NULL    | NULL | 1000 |             |
      		 
      |    2 | DEPENDENT SUBQUERY | tsubq | index | key1          | PRIMARY | 4       | NULL |    1 | Using where |
      		 
      +------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+

      The subquery uses "index" access, which is very inefficient. The estimate for #rows seems to come from the LIMIT clause and is very wrong in this case.

      The table is InnoDB (with extended keys). The index KEY(key1) is actually KEY(key1, id). The query has a restriction on key1 which makes it constant (tsubq.key1 = t1.a). After that, ORDER BY tsubq.id is achieved automatically.

      The problem seems to be specifically with references to outside of subquery. If I use a constant instead, the query plan is able to use key1:

      explain select     (SELECT        concat(id, '-', key1, '-', col1)     FROM tsubq     WHERE tsubq.key1 = 333     ORDER BY tsubq.id ASC LIMIT 1) from    t1;
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      		 
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL  | 1000 |             |
      		 
      |    2 | SUBQUERY    | tsubq | ref  | key1          | key1 | 5       | const |  999 | Using where |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6081 ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686)

          • Major - Major loss of function.
          • Closed

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              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.