Bug #29357 Filtering on view with group don't works.
Submitted: 26 Jun 2007 11:34 Modified: 6 Aug 2007 7:39
Reporter: Mauro Braggio Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version: 5.1.19-beta-log MySQL, 5.0 BK OS:Any
Assigned to: Igor Babaev CPU Architecture:Any

[26 Jun 2007 11:34] Mauro Braggio
Description:
if i create a view with a group statement and i execute a query with a where condition.

select * from v3 where f1='foobarbaz'; 

Mysql doesn't filter before the results ,but after.

Sorry for my bad english.

How to repeat:
create table t1 (f1 char(15), index(f1));
insert into t1 values('foo'), ('bar'), ('az'), ('foobarbaz'); 

create table t2 (f1 char(15), index(f1));
insert into t2 values('foo'), ('bar'), ('az'), ('foobarbaz'); 

create view v1 as select f1 from t1; 
create view v2 as select t1.f1 from t1,t2; 
create view v3 as select t1.f1 from t1,t2 group by t1.f1; 

explain select * from t1 where f1='foobarbaz';

explain select * from v1 where f1='foobarbaz';

mysql> explain select * from v2 where f1='foobarbaz'; 
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref   | f1            | f1   | 16      | const |    1 | Using where; Using index | 
|  1 | SIMPLE      | t2    | index | NULL          | f1   | 16      | NULL  |    4 | Using index              | 
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+

mysql> explain select * from v3 where f1='foobarbaz'; 
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL |    4 | Using where                                  | 
|  2 | DERIVED     | t1         | index | NULL          | f1   | 16      | NULL |    4 | Using index; Using temporary; Using filesort | 
|  2 | DERIVED     | t2         | index | NULL          | f1   | 16      | NULL |    4 | Using index                                  | 
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
3
[26 Jun 2007 12:51] Sveta Smirnova
Thank you for the report.

Verified as described.
[4 Jul 2007 6:32] Igor Babaev
MySQL always materialize views with GROUP BY.
[6 Aug 2007 7:39] Mauro Braggio
OK, but mysql would to filter the conditions before to group the rows.

Thanks.