Mergeable views and derived tables are not transparent for min/max optimization

[Igor Babaev]

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)