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

LP:802023 - Mergeable views and derived tables are not transparent for min/max optimization

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      If to create and populate table t1 with the statements

      CREATE TABLE t1 (a int, b int, c varchar(32), INDEX idx(a,b));
      INSERT INTO t1 VALUES
      (7, 74, 'yyyyyyy'), (9, 97, 'aaaaaaaaa'), (2, 23, 'tt'),
      (5, 55, 'ddddd'), (2, 27, 'ss'), (7, 76, 'xxxxxxx'),
      (7, 79, 'zzzzzzz'), (9, 92, 'bbbbbbbbb'), (2, 25, 'pp'),
      (5, 53, 'eeeee'), (2, 23, 'qq'), (7, 76,'wwwwwww'),
      (7, 74, 'uuuuuuu'), (9, 92, 'ccccccccc'), (2, 25, 'oo');

      then this problem can be easily seen from the output of the following
      EXPLAIN commands:

      MariaDB [test]> EXPLAIN SELECT MAX(b) FROM t1 WHERE a=7 AND b<75;
      ------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ------------------------------------------------------------------------------------+

      1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

      ------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      MariaDB [test]> EXPLAIN SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75;
      --------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------------------------------------+

      1 SIMPLE t1 range idx idx 10 NULL 1 Using index condition; Rowid-ordered scan

      --------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      A similar problem we have for mergeable views:

      MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1;
      Query OK, 0 rows affected (0.01 sec)
      MariaDB [test]> EXPLAIN SELECT MAX(b) FROM v1 WHERE a=7 AND b<75;
      --------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------------------------------------+

      1 SIMPLE t1 range idx idx 10 NULL 1 Using index condition; Rowid-ordered scan

      --------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.