| Bug #62504 | "select count(distinct N1), count(distinct N2) from test.AA" works incorrectly | ||
|---|---|---|---|
| Submitted: | 22 Sep 2011 19:44 | Modified: | 12 Jun 2013 16:30 |
| Reporter: | Vyacheslav Brover | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.5.15-log, 5.5.17, 5.6.10 | OS: | Linux |
| Assigned to: | Neeraj Bisht | CPU Architecture: | Any |
| Tags: | count, distinct, regression, SELECT, unique | ||
[22 Sep 2011 19:44]
Vyacheslav Brover
[22 Sep 2011 20:04]
Vyacheslav Brover
This bug happens when a unique index exists. How to repeat: create table test.AA (N1 int, N2 int); /*engine='MyISAM' or engine='InnoDB'*/ create unique index AA_uq on test.AA(N1,N2); insert into test.AA (N1, N2) values (1, 1); insert into test.AA (N1, N2) values (1, 2); select count(distinct N1), count(distinct N2) from test.AA; -- Result: 2 2 -- Expected: 1 2
[22 Sep 2011 20:42]
Vyacheslav Brover
sql_buffer_result may help: select sql_buffer_result count(distinct N1), count(distinct N2) from test.AA; -- Result: 1 2 (correct) but select sql_buffer_result T.N1, T.N2 from (select count(distinct N1) N1, count(distinct N2) N2 from test.AA) T; -- Result: 2 2 (incorrect)
[22 Sep 2011 21:20]
MySQL Verification Team
C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.5.17-log Source distribution Copyright (c) 2000, 2011, 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 5.5 >use test Database changed mysql 5.5 >create table test.AA (N1 int, N2 int); /*engine='MyISAM' or engine='InnoDB'*/ Query OK, 0 rows affected (0.15 sec) mysql 5.5 >create unique index AA_uq on test.AA(N1,N2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.5 >insert into test.AA (N1, N2) values (1, 1); Query OK, 1 row affected (0.09 sec) mysql 5.5 >insert into test.AA (N1, N2) values (1, 2); Query OK, 1 row affected (0.09 sec) mysql 5.5 > mysql 5.5 >select count(distinct N1), count(distinct N2) from test.AA; +--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 2 | 2 | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql 5.5 >alter table AA drop index AA_uq; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.5 >select count(distinct N1), count(distinct N2) from test.AA; +--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 1 | 2 | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql 5.5 >
[22 Sep 2011 21:25]
MySQL Verification Team
Not repeatable with 5.1: C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.60-Win X64-log Source distribution Copyright (c) 2000, 2011, 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 5.1 >use test Database changed mysql 5.1 >create table test.AA (N1 int, N2 int); /*engine='MyISAM' or engine='InnoDB'*/ Query OK, 0 rows affected (0.05 sec) mysql 5.1 >create unique index AA_uq on test.AA(N1,N2); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1 >insert into test.AA (N1, N2) values (1, 1); Query OK, 1 row affected (0.01 sec) mysql 5.1 >insert into test.AA (N1, N2) values (1, 2); Query OK, 1 row affected (0.00 sec) mysql 5.1 > mysql 5.1 >select count(distinct N1), count(distinct N2) from test.AA; +--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 1 | 2 | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql 5.1 >
[23 Sep 2011 7:47]
Valeriy Kravchuk
Thank you for the bug report. Indeed, looks like a regression bug: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.17-debug Source distribution Copyright (c) 2000, 2011, 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> create table test.AA (N1 int, N2 int); /*engine='MyISAM' or engine='InnoDB'*/ Query OK, 0 rows affected (0.08 sec) mysql> create unique index AA_uq on test.AA(N1,N2); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into test.AA (N1, N2) values (1, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into test.AA (N1, N2) values (1, 2); Query OK, 1 row affected (0.00 sec) mysql> select count(distinct N1), count(distinct N2) from test.AA; +--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 2 | 2 | +--------------------+--------------------+ 1 row in set (0.02 sec) mysql> explain select count(distinct N1), count(distinct N2) from test.AA; +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------------------------------+ | 1 | SIMPLE | AA | range | NULL | AA_uq | 10 | NULL | 3 | Using index for group-by (scanning) | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------------------------------+ 1 row in set (0.00 sec) If we add sql_buffer_result and change the plan: mysql> select sql_buffer_result count(distinct N1), count(distinct N2) from test.AA; +--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 1 | 2 | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql> explain select sql_buffer_result count(distinct N1), count(distinct N2) from test.AA; +----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------------------+ | 1 | SIMPLE | AA | range | NULL | AA_uq | 10 | NULL | 3 | Using index for group-by (scanning); Using temporary | +----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------------------+ 1 row in set (0.01 sec) results are correct. Same without index, as Miguel already pointed out: mysql> alter table AA drop index AA_uq; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(distinct N1), count(distinct N2) from test.AA;+--------------------+--------------------+ | count(distinct N1) | count(distinct N2) | +--------------------+--------------------+ | 1 | 2 | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql> explain select count(distinct N1), count(distinct N2) from test.AA; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | AA | ALL | NULL | NULL | NULL | NULL | 2 | | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.01 sec) So, we have a clear bug here.
[29 Sep 2011 21:11]
Vyacheslav Brover
OS is Linux.
[16 Feb 2012 4:20]
Valeriy Kravchuk
Bug #64343 was marked as a duplicate of this one.
[3 Jul 2012 15:09]
Daniel DeLorme
I've also run across this bug; in my case the unique index had a third column, and I noticed that the result of count distinct was the number of distinct rows for the first two columns. In other words, SELECT COUNT(DISTINCT a), COUNT(DISTINCT b) was equivalent to SELECT COUNT(DISTINCT a,b), COUNT(DISTINCT a,b)
[19 Mar 2013 11:43]
MySQL Verification Team
Bug #68716 was marked as a duplicate of this one.
[19 Mar 2013 18:09]
MySQL Verification Team
As reporter mentioned in #68716 - is similar of Bug #62504 but adding sql_buffer_result the result is the same. I think that Bug #68716 and Bug #62504 are duplicates and not separate bugs because sql_buffer_result is not 100% reliable workaround in 62504 too. ##### 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)
[12 Jun 2013 16:30]
Erlend Dahl
[10 Jun 2013 11:21] Paul Dubois Noted in 5.5.33, 5.6.13, 5.7.2 changelogs. Incorrect results could be returned from queries that used several aggr_func(DISTINCT) functions (where aggr_func is an aggregate function such as COUNT()) when these referred to different columns of the same composite key.
