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

Estimates for a non-merged join having implicit grouping is not showing 1 with optimizer_use_condition_selectivity >1

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Stalled (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.0
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4, 10.0
    • Component/s: Optimizer
    • Labels:
      None

      Description

      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');
       
      MariaDB [test]>  set optimizer_use_condition_selectivity=1;
      Query OK, 0 rows affected (0.003 sec)
      
      

      MariaDB [test]> explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
      +------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type  | table       | type  | possible_keys | key  | key_len | ref  | rows | Extra                                           |
      +------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | PRIMARY      | t1          | index | a             | a    | 5       | NULL |    2 | Using where; Using index                        |
      |    2 | SUBQUERY     | <subquery3> | ALL   | distinct_key  | NULL | NULL    | NULL |    1 |                                                 |
      |    2 | SUBQUERY     | t2          | ALL   | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join) |
      |    3 | MATERIALIZED | t3          | ALL   | NULL          | NULL | NULL    | NULL |    2 |                                                 |
      +------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
      4 rows in set (0.005 sec)
      

      MariaDB [test]> set optimizer_use_condition_selectivity=4;
      Query OK, 0 rows affected (0.000 sec)

      MariaDB [test]> explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref       | rows | Extra                    |
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
      |    1 | PRIMARY      | t1          | index  | a             | a            | 5       | NULL      |    2 | Using where; Using index |
      |    2 | SUBQUERY     | t2          | ALL    | NULL          | NULL         | NULL    | NULL      |    2 | Using where              |
      |    2 | SUBQUERY     | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | test.t2.c |    1 |                          |
      |    3 | MATERIALIZED | t3          | ALL    | NULL          | NULL         | NULL    | NULL      |    2 |                          |
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
      4 rows in set (0.003 sec)
      

      The <subquery3> is materialised and has implicit grouping, so we are sure that it would have 1 record, but in the second case the estimate is 2.

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta
            Reporter:
            varun Varun Gupta
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: