MySQL Bugs: #26209: wrong result using COUNT(DISTINCT ?) querying a VIEW with GROUP BY and ORDER BY

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	CPU Architecture:	Any
Tags:	count, distinct, GROUP BY, SELECT, tables, Views

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.

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)

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.

The fix has been pushed into 5.0.36 and 5.1.16-beta.

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.