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:
None 
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
Description:
Query: select distinct [x] from t group by id;

not returning correct results when [x] contains NULL's and 0's.

How to repeat:
drop table if exists t;
create table t (id int, b int);
insert into t values (1,NULL), (2,0);

select distinct min(b) from t group by id;

I would expect the result to be
+------+
| NULL |
|    0 |
+------+

but it returns
+------+
| NULL |
+------+

Suggested fix:
Not sure
[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