Bug #26209 wrong result using COUNT(DISTINCT ?) querying a VIEW with GROUP BY and ORDER BY
Submitted: 9 Feb 2007 6:47 Modified: 26 Feb 2007 20:54
Reporter: Stefano Bittante Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.36-BK, 5.0.27 OS:Linux (Linux, winXP)
Assigned to: Igor Babaev
Tags: count, distinct, GROUP BY, SELECT, tables, Views

[9 Feb 2007 6:47] Stefano Bittante
Description:
If the "distinct" clause is used in a query with a group by statement, the behaviour is different whether the queried object is a table or a view.
The result using a view are wrong.

// THIS DOES NOT WORK AS EXPECTED
select
code,
count(distinct country)
from test_view
group by code
order by max(id);
+------+-------------------------+
| code | count(distinct country) |
+------+-------------------------+
|  100 |                       1 |
|  200 |                       1 |
|  100 |                       1 |
+------+-------------------------+

How to repeat:
CREATE TABLE test_table (
  id int(11) NOT NULL,
  country varchar(100),
  code int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_table VALUES (1,'ITALY',100);
INSERT INTO test_table VALUES (2,'ITALY',200);
INSERT INTO test_table VALUES (3,'ITALY',100);

create or replace view test_view as
select *
from test_table;

// THIS WORKS
select
code,
count(distinct country)
from test_table
group by code
order by max(id);
+------+-------------------------+
| code | count(distinct country) |
+------+-------------------------+
|  200 |                       1 |
|  100 |                       1 |
+------+-------------------------+
2 rows in set (0.00 sec)

// THIS WORKS
select
code,
count(country)
from test_view
group by code
order by max(id);
+------+----------------+
| code | count(country) |
+------+----------------+
|  200 |              1 |
|  100 |              2 |
+------+----------------+

// THIS DOES NOT WORK AS EXPECTED
select
code,
count(distinct country)
from test_view
group by code
order by max(id);
+------+-------------------------+
| code | count(distinct country) |
+------+-------------------------+
|  100 |                       1 |
|  200 |                       1 |
|  100 |                       1 |
+------+-------------------------+

Suggested fix:
workaround: don't use views.
[9 Feb 2007 9:04] Valerii Kravchuk
Thank you for a bug report. Verified just as described with 5.0.36-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.36    |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE test_table (
    ->   id int(11) NOT NULL,
    ->   country varchar(100),
    ->   code int(11) default NULL,
    ->   PRIMARY KEY  (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test_table VALUES (1,'ITALY',100);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_table VALUES (2,'ITALY',200);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table VALUES (3,'ITALY',100);
Query OK, 1 row affected (0.00 sec)

mysql> create or replace view test_view as
    -> select *
    -> from test_table;
Query OK, 0 rows affected (0.01 sec)

mysql> select
    -> code,
    -> count(distinct country)
    -> from test_table
    -> group by code
    -> order by max(id);
+------+-------------------------+
| code | count(distinct country) |
+------+-------------------------+
|  200 |                       1 |
|  100 |                       1 |
+------+-------------------------+
2 rows in set (0.01 sec)

mysql> select
    -> code,
    -> count(country)
    -> from test_view
    -> group by code
    -> order by max(id);
+------+----------------+
| code | count(country) |
+------+----------------+
|  200 |              1 |
|  100 |              2 |
+------+----------------+
2 rows in set (0.01 sec)

mysql> select
    -> code,
    -> count(distinct country)
    -> from test_view
    -> group by code
    -> order by max(id);
+------+-------------------------+
| code | count(distinct country) |
+------+-------------------------+
|  100 |                       1 |
|  200 |                       1 |
|  100 |                       1 |
+------+-------------------------+
3 rows in set (0.00 sec)
[12 Feb 2007 3:33] 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/19668

ChangeSet@1.2412, 2007-02-11 19:36:46-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #26209.
  The function make_unireg_sortorder ignored the fact that any
  view field is represented by a 'ref' object.
  This could lead to wrong results for the queries containing
  both GROUP BY and ORDER BY clauses.
[14 Feb 2007 10:35] Igor Babaev
The fix has been pushed into 5.0.36 and 5.1.16-beta.
[26 Feb 2007 20:54] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.

Using ORDER BY or GROUP BY could yield different results when
selecting from a view and selecting from the underlying table.