MySQL Bugs: #77632: count distinct returns in-correct result

Bug #77632	count distinct returns in-correct result
Submitted:	6 Jul 2015 19:07	Modified:	26 Aug 2015 13:29
Reporter:	Son Nguyen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I have the query below that returns correct result

select 
`PRODUCTTYPE`.`PRODUCTTYPE` as `C0` , 
count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`  
from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT` 
on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`) 
INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER` 
where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
group by `PRODUCTTYPE`.`PRODUCTTYPE`

Get Data All:
"C0", "C1"
"Watches", 5
"Woods", 4

If I wraps the query as a derived table, then the result is incorrect.
select T1.C0, T1.C1 from
(
select 
`PRODUCTTYPE`.`PRODUCTTYPE` as `C0` , 
count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`  
from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT` 
on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`) 
INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER` 
where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
group by `PRODUCTTYPE`.`PRODUCTTYPE`
) T1

Get Data All:
"C0", "C1"
"Watches", 1
"Woods", 1

Here are my database and driver versions:  The issue can be reproduced using MS ODBCTest on Windows.

SQL_DBMS_NAME=17, 10, "MySQL"
SQL_DBMS_VER=18, 24, "5.7.7-rc-log"
SQL_DRIVER_NAME=6, 24, "myodbc5a.dll"
SQL_DRIVER_VER=7, 20, "05.02.0004"

How to repeat:

Please let me know if you need data of tables involving in the query.

Please attach a dump file with create table and data inserts. Thanks.

dump file of GOSALES1 database

Attachment: gosales1.sql.zip (application/x-zip-compressed, text), 926.75 KiB.

Please find attached the dump file of our database.  The schema name is GOSALES1.

Thanks

Son Nguyen

Hello Son Nguyen,

Thank you for the bug report.
Verified this behavior on MySql Server 5.7.7 as described.

Thanks,
Chiranjeevi.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.7-rc-enterprise-commercial-advanced-log MySQL Enterprise Ser
ver - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1
Database changed
mysql> select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `OR
DERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDE
RDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  5 |
| Woods   |  4 |
+---------+----+
2 rows in set (0.00 sec)

mysql> select T1.C0, T1.C1 from
    -> (
    -> select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `OR
DERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDE
RDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`
    -> ) T1
    -> ;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  1 |
| Woods   |  1 |
+---------+----+
2 rows in set (0.00 sec)

mysql>

Test results in 5.5 and 5.6

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.45-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1;
Database changed
mysql> select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  5 |
| Woods   |  4 |
+---------+----+
2 rows in set (0.00 sec)

mysql> select T1.C0, T1.C1 from (select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`) T1;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  5 |
| Woods   |  4 |
+---------+----+
2 rows in set (0.00 sec)

mysql>

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1;
Database changed
mysql> select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  5 |
| Woods   |  4 |
+---------+----+
2 rows in set (0.01 sec)

mysql> select T1.C0, T1.C1 from (select
    -> `PRODUCTTYPE`.`PRODUCTTYPE` as `C0` ,
    -> count(distinct `PRODUCT`.`PRODUCTNAME`) as `C1`
    -> from (`PRODUCTTYPE` `PRODUCTTYPE` INNER JOIN `PRODUCT` `PRODUCT`
    -> on `PRODUCTTYPE`.`PRODUCTTYPECODE` = `PRODUCT`.`PRODUCTTYPECODE`)
    -> INNER JOIN `ORDERDETAIL` `ORDERDETAIL` on `PRODUCT`.`PRODUCTNUMBER` = `ORDERDETAIL`.`PRODUCTNUMBER`
    -> where ( `PRODUCTTYPE`.`PRODUCTTYPE` like 'W%' ) and ( `ORDERDETAIL`.`ORDERDETAILCODE` > 115000 )
    -> group by `PRODUCTTYPE`.`PRODUCTTYPE`) T1;
+---------+----+
| C0      | C1 |
+---------+----+
| Watches |  5 |
| Woods   |  4 |
+---------+----+
2 rows in set (0.00 sec)

mysql>

Posted by developer:
 
This bug is fixed in the most recent version of 5.7

yes, fixed in 5.7.8 by the patch for
Bug#20145024: WRONG RESULT FOR COUNT DISTINCT QUERY IN DERIVED TABLE

Fixed under the heading of 

Bug #20145024 	WRONG RESULT FOR COUNT DISTINCT QUERY IN DERIVED TABLE

[7 May 2015 9:35] Paul Dubois

Noted in 5.7.8, 5.8.0 changelogs.

Use of SELECT COUNT(DISTINCT) in a subquery in the FROM clause could
produce incorrect results.