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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 10.0.0
    • Components: None
    • Labels:
    • Sprint:

      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: