Bug #66896 | Distinct not distinguishing 0 from NULL when GROUP BY is used | ||
---|---|---|---|
Submitted: | 20 Sep 2012 20:24 | Modified: | 21 Sep 2012 0:05 |
Reporter: | Nick Bartley | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.5.24 | OS: | Linux (ubuntu 12.04) |
Assigned to: | CPU Architecture: | Any |
[20 Sep 2012 20:24]
Nick Bartley
[20 Sep 2012 21:23]
MySQL Verification Team
Thank you for the bug report. d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.29 Source distribution Copyright (c) 2000, 2012, 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 >drop table if exists t; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.5 >create table t (id int, b int); Query OK, 0 rows affected (0.06 sec) mysql 5.5 >insert into t values (1,NULL), (2,0); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.5 > mysql 5.5 >select distinct min(b) from t group by id; +--------+ | min(b) | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) d:\dbs>d:\dbs\5.6\bin\mysql -uroot --port=3540 --prompt="mysql 5.6 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.0-m10 Source distribution Copyright (c) 2000, 2012, 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.6 >use test Database changed mysql 5.6 >drop table if exists t; Query OK, 0 rows affected (0.13 sec) mysql 5.6 >create table t (id int, b int); Query OK, 0 rows affected (0.09 sec) mysql 5.6 >insert into t values (1,NULL), (2,0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.6 > mysql 5.6 >select distinct min(b) from t group by id; +--------+ | min(b) | +--------+ | NULL | +--------+ 1 row in set (0.04 sec)
[9 Jul 2013 23:25]
Arthur O'Dwyer
I'm also observing this bug, and a presumably related one. $ mysqld --version mysqld Ver 5.5.31-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu)) The related bug is interesting; adding a PRIMARY KEY to the table causes the behavior of SELECT DISTINCT to change in odd ways. To reproduce, start with Nick's test case, and then: SELECT DISTINCT b FROM t GROUP BY id; +------+ | NULL | +------+ I don't understand what "select distinct b ... group by id" is actually computing --- help me? --- but surely the resulting table should have one row per "id" group. ALTER TABLE t ADD PRIMARY KEY (id); SELECT DISTINCT b FROM t GROUP BY id; +------+ | NULL | | 0 | +------+ Notice that the data in the table haven't changed; all I did was add a primary key! Yet after that ALTER TABLE, the same query returns different (correct?) results. Dropping the key again restores the incorrect behavior. ALTER TABLE t DROP PRIMARY KEY; SELECT DISTINCT b FROM t GROUP BY id; +------+ | NULL | +------+ This old "Ask Tom" column implies that "GROUP BY <primary key>" is supposed to be a no-op, but clearly that's incorrect. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5229766627656