MySQL Bugs: #16016: Problem with optimization of SELECT MAX() FROM view

Bug #16016	Problem with optimization of SELECT MAX() FROM view
Submitted:	27 Dec 2005 12:34	Modified:	13 Jan 2006 0:14
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.18	OS:	Linux (linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
SELECT MAX() from view is executed slower than SELECT MAX() from base_table:

mysql> select max(id) from v1;
+---------+
| max(id) |
+---------+
|  100000 |
+---------+
1 row in set (0.32 sec)

mysql> select max(id) from t1;
+---------+
| max(id) |
+---------+
|  100000 |
+---------+
1 row in set (0.00 sec)

Explain output for those queries are also different:

mysql> explain select max(id) from v1;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | PRIMARY     | t1    | index | NULL          | PRIMARY | 4       | NULL | 100000 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select max(id) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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)
 

How to repeat:
1. load table t1
2. create view:
mysql> show create view v1;
+------+----------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                                |
+------+----------------------------------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id` from `t1` |
+------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. run queries:
select max(id) from t1;
select max(id) from v1;
explain select max(id) from t1;
explain select max(id) from v1;

table t1

Attachment: t1.zip (application/zip, text), 190.92 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/733

ChangeSet
  1.2021 06/01/06 22:28:26 igor@rurik.mysql.com +3 -0
  Fixed bug #16016: MIN/MAX optimization was not applied to views.

  sql/opt_sum.cc
    1.49 06/01/06 22:28:02 igor@rurik.mysql.com +5 -5
    Fixed bug #16016: MIN/MAX optimization was not applied to views.
    The fix employs the standard way of handling direct references to view fields.

  mysql-test/t/view.test
    1.132 06/01/06 22:28:02 igor@rurik.mysql.com +24 -0
    Added a test case for bug #16016.

  mysql-test/r/view.result
    1.143 06/01/06 22:28:02 igor@rurik.mysql.com +29 -0
    Added a test case for bug #16016.

The fix will appear in 5.0.20

Added entry to 5.0.20 changelog:

   <listitem>
        <para>
          <literal>MIN()</literal> and <literal>MAX()</literal>
          operations were not optimized for views. (Bug #16016)
        </para>
      </listitem>

The changelog version is actually 5.0.19.