MySQL Bugs: #68716: select count(distinct N1), count(distinct N2) works incorrectly

Bug #68716	select count(distinct N1), count(distinct N2) works incorrectly
Submitted:	19 Mar 2013 10:24	Modified:	19 Mar 2013 12:06
Reporter:	Alessandro D'Anca	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	count, distinct, regression, SELECT

Description:
Select statement which has more than 1 "count(distinct (COL))" expression works incorrectly.
This is similar of Bug #62504 but adding sql_buffer_result the result is the same.
Looks like a regression bug; version 5.5.28 work fine.

How to repeat:
use test;
create table AA (N1 int, N2 int, PRIMARY KEY (N1), KEY (N2));
insert into AA (N1, N2) values (1, 1);
insert into AA (N1, N2) values (2, 1);

SELECT count(distinct (N1)), count(distinct (N2)) FROM AA;
Result: 2 2
Expected: 2 1

Hello Alessandro,

Thank you for the bug report. Indeed, looks like a regression bug:

mysql> create table AA (N1 int, N2 int, PRIMARY KEY (N1), KEY (N2));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into AA (N1, N2) values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into AA (N1, N2) values (2, 1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT count(distinct (N1)), count(distinct (N2)) FROM AA;
+----------------------+----------------------+
| count(distinct (N1)) | count(distinct (N2)) |
+----------------------+----------------------+
|                    2 |                    2 |
+----------------------+----------------------+
1 row in set (0.01 sec)

mysql> SELECT sql_buffer_result count(distinct (N1)), count(distinct (N2)) FROM AA;
+----------------------+----------------------+
| count(distinct (N1)) | count(distinct (N2)) |
+----------------------+----------------------+
|                    2 |                    2 |
+----------------------+----------------------+
1 row in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.10-debug-log |
+------------------+
1 row in set (0.00 sec)

##########

[root@ushastry mysql-5.5.31]# bin/mysql -u root -p -S /tmp/5531_/sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.31-debug Source distribution

Copyright (c) 2000, 2013, 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 test;
Database changed
mysql> create table AA (N1 int, N2 int, PRIMARY KEY (N1), KEY (N2));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into AA (N1, N2) values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into AA (N1, N2) values (2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT count(distinct (N1)), count(distinct (N2)) FROM AA;
+----------------------+----------------------+
| count(distinct (N1)) | count(distinct (N2)) |
+----------------------+----------------------+
|                    2 |                    1 |
+----------------------+----------------------+
1 row in set (0.03 sec)

Duplicate of http://bugs.mysql.com/bug.php?id=62504

How can this be a duplicate if "is similar of Bug #62504 but adding sql_buffer_result the result is the same"?

I'd understand the "duplicate" if the extra information was folded into the original bug report, but by just making this one a duplicate without any further action more or less looses the additional information provided by this report as it probably never gets looked at again by anyone anymore ...