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

Fix "Subqueries: n" in EXPLAIN for condition pushdown

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      (This has been previously discussed with Timour, I'm just filing the task now)

      Currently, EXPLAIN shows subqueries like this:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1 (a int, b int) as select a as a, a as b from ten;
       
      mysql> explain select * from ten where a > (select max(b) from t1 where t1.b <= ten.a);
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------------------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                      |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------------------------+
      |    1 | PRIMARY            | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Subqueries: 2 |
      |    2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where                |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------------------------+

      This is not user-friendly, "Subqueries: 2" reads as if there were two
      subqueries attached, while actually it is one subquery #2 that is attached.

      Tentative new syntax:

        Subquery #n
        Subquery #n1, #n2

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: