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

[14 Mar 2006 19:01] Kolbe Kegel
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.
[4 Apr 2006 19:55] Bugs System
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
[8 Apr 2006 17:13] Igor Babaev
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
[11 Apr 2006 13:26] Paul DuBois
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)