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

LP:1013343 - SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:

      Description

      There are two queries that are different only in that one of them accesses a table directly, while the other reads it from a "trivial" derived table, ie. through a "(SELECT * FROM t2) as alias" construct. EXPLAINs should be the same, but they are different:

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES
      (4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
      (1),(8),(8),(5),(7),(5),(1),(6),(2),(9);

      CREATE TABLE t2 (b INT);
      INSERT INTO t2 VALUES
      (1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
      (1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
      explain extended SELECT * FROM t1, t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); 

      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
      |    1 | PRIMARY     | alias | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
      |    2 | SUBQUERY    | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
      |    2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

      explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      |    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
      |    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
      |    3 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
      |    3 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: