Mergeable views and derived tables are not transparent for min/max optimization
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug 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)
Changed in maria: | |
importance: | Undecided → High |
status: | New → Confirmed |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |