[MDEV-3006] LP:802023 - Mergeable views and derived tables are not transparent for min/max optimization Created: 2011-06-25  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug802023.xml    

 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)

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 802023

Generated at Thu Feb 08 06:45:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.