Bug #154 Wrong group by or distinct on Innodb table indexed column with NULLs

• Submitted: 15 Mar 2003 5:44
• Modified: 15 Mar 2003 23:28
• Reporter: Alexander Keremidarski
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: All
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[15 Mar 2003 5:44] Alexander Keremidarski

Description:
Bug happens only under following conditions (all of them must be true):

* InnoDb table
* column used in group by or select distinct is indexed with non unique Key
* There is Primary Key on another column

First time when query is invoked there's hign chance to get correct result.
For subsecuent runs it return various "distributions" for NULLs like (4, 1, 1, 1) (5, 1, 1) (4, 2, 1)  

How to repeat:
CREATE TABLE t1 (
  id int(11) NOT NULL default '0',
  n int(11) default NULL,
  PRIMARY KEY  (id),
  KEY n (n)
) TYPE=InnoDB;

INSERT INTO t1 VALUES (1,NULL);
INSERT INTO t1 VALUES (2,NULL);
INSERT INTO t1 VALUES (3,NULL);
INSERT INTO t1 VALUES (5,NULL);
INSERT INTO t1 VALUES (234,NULL);
INSERT INTO t1 VALUES (298,NULL);
INSERT INTO t1 VALUES (4,1);

# Warning. Execute query more than once. Sometimes
# upon first run it returns correct result, but for
# subsequent runs result is wrong 

mysql> select n, count(*) from t1 group by n;
+------+----------+
| n    | count(*) |
+------+----------+
| NULL |        4 |
| NULL |        2 |
|    1 |        1 |
+------+----------+
3 rows in set (0.02 sec)

mysql> select distinct n from t1;
+------+
| n    |
+------+
| NULL |
| NULL |
|    1 |
+------+
3 rows in set (0.06 sec)

[15 Mar 2003 23:28] Heikki Tuuri

Fixed now in both the 3.23 and 4.0 trees. MySQL seems to assume that we set to zero the contents of a field whose value is marked as the SQL NULL.