MySQL Bugs: #18237: COUNT(*) on view returns different results than base table

Bug #18237	COUNT(*) on view returns different results than base table
Submitted:	14 Mar 2006 19:01	Modified:	11 Apr 2006 13:26
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The use of COUNT() over a view that does SELECT * from a MyISAM table can give an erroneous result.

The result returned is the product of the number of rows in the base table and the number of rows in the view.

Apparently views over InnoDB tables are not affected by this bug.

How to repeat:
create table t1 (id int unsigned auto_increment not null primary key) engine=myisam;
create table t2 like t1 engine=myisam;
insert into t1 values (),();
insert into t2 values (),(),();
create view v2 as select * from t2;
select count(*) from t1 left join t2 on t1.id=t2.id;
select count(*) from t1 left join v2 on t1.id=v2.id;

mysql 5.0.19-max (root) [test]> select count(*) from t1 left join t2 on t1.id=t2.id;
+----------+
| count(*) |
+----------+
| 2        |
+----------+
1 row in set (0.01 sec)

mysql 5.0.19-max (root) [test]> select count(*) from t1 left join v2 on t1.id=t2.id;
ERROR 1054 (42S22): Unknown column 't2.id' in 'on clause'
mysql 5.0.19-max (root) [test]> select count(*) from t1 left join v2 on t1.id=v2.id;
+----------+
| count(*) |
+----------+
| 6        |
+----------+
1 row in set (0.00 sec)

mysql 5.0.19-max (root) [test]> alter table t2 engine=innodb;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0.19-max (root) [test]> select count(*) from t1 left join v2 on t1.id=v2.id;
+----------+
| count(*) |
+----------+
| 2        |
+----------+
1 row in set (0.00 sec)

Suggested fix:
COUNT() should give the expected result.

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/4459

ChangeSet
  1.2138 06/04/04 12:55:02 igor@rurik.mysql.com +3 -0
  Fixed bug #18237.
  The code in opt_sum_query that prevented the COUNT/MIN/MAX 
  optimization from being applied to outer joins  was not adjusted 
  after introducing nested joins. As a result if an outer join
  contained a reference to a view as an inner table the code of
  opt_sum_query missed the presence of an on expressions and
  erroneously applied the mentioned optimization.

The fix will appear in 5.0.21. It was merged into 5.1

Noted in 5.0.21, 5.1.9 changelog.

<literal>COUNT(*)</literal> on a <literal>MyISAM</literal>
table could return different results for the base table and a 
view on the base table. (Bug #18237)