| Bug #2483 | COUNT(*) on NULL incorrect for UNIQUE index | ||
|---|---|---|---|
| Submitted: | 22 Jan 2004 14:01 | Modified: | 27 Jan 2004 10:08 |
| Reporter: | James Farley | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.17-standard-log | OS: | Linux (RedHat 7.3) |
| Assigned to: | Jani Tolonen | CPU Architecture: | Any |
[22 Jan 2004 14:35]
Dean Ellis
Verified with 4.0.18 and 4.1.2. Does not occur with MyISAM, so I am changing the category. Thank you.
[26 Jan 2004 15:46]
Jani Tolonen
I believe the problem is that InnoDB assumes that if a search is
done on a unique key with exact match, only one match can occur.
This is true in any other case except if the key is NULL and there
are more than one NULL values in the key field.
Suggested fix:
Make the following change in row0sel.c
*** innobase/row/row0sel.c 2004-01-27 01:37:08.000000000 +0200
--- /my/tmp/row0sel.c 2004-01-27 01:36:43.000000000 +0200
***************
*** 2883,2906 ****
thus in a secondary index we must use next-key locks when locking
delete-marked records. */
! if (match_mode == ROW_SEL_EXACT
! && index->type & DICT_UNIQUE
! && dtuple_get_n_fields(search_tuple)
! == dict_index_get_n_unique(index)) {
! unique_search = TRUE;
!
! /* Even if the condition is unique, MySQL seems to try to
! retrieve also a second row if a primary key contains more than
! 1 column. Return immediately if this is not a HANDLER
! command. */
!
! if (direction != 0 && !prebuilt->used_in_HANDLER) {
!
! trx->op_info = (char *) "";
! return(DB_RECORD_NOT_FOUND);
! }
! }
!
mtr_start(&mtr);
/*-------------------------------------------------------------*/
--- 2883,2893 ----
thus in a secondary index we must use next-key locks when locking
delete-marked records. */
! if (match_mode == ROW_SEL_EXACT && index->type & DICT_UNIQUE &&
! dtuple_get_n_fields(search_tuple) ==
! dict_index_get_n_unique(index))
! unique_search = TRUE;
!
mtr_start(&mtr);
/*-------------------------------------------------------------*/
Tested and works for me at least. I will however wait that Heikki reviews
the patch before submitting it.
Regards,
Jani
[27 Jan 2004 10:08]
Heikki Tuuri
Hi! InnoDB assumed that there can only be one IS NULL row in a UNIQUE index, though there can be many! Fixed in 4.0.18. Thank you, Heikki
Description: COUNT(*) is reporting incorrect values for queries like: SELECT COUNT(*) FROM {TABLE} WHERE {COL} IS NULL if {COL} is indexed as UNIQUE. The value returned is always 1. If you force MySQL to ignore the index, ie: SELECT COUNT(*) FROM {TABLE} IGNORE INDEX ({SOME IDX}) WHERE {COL} IS NULL you get the correct number of values back. I did my tests on InnoDB tables. A full script dump is presented below in "How to repeat" showing (what I believe) to be the incorrect behavior. Thanks in advance! James How to repeat: (root@sagoqa) [4:38:47]> create table FOO (login varchar(255)) Type=INNODB; Query OK, 0 rows affected (0.02 sec) (root@sagoqa) [4:38:50]> show tables; +------------------+ | Tables_in_sagoqa | +------------------+ | foo | +------------------+ 1 row in set (0.00 sec) (root@sagoqa) [4:38:51]> desc foo; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | login | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) (root@sagoqa) [4:38:54]> alter table foo add unique sa_foo_login_idx_1 (login); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@sagoqa) [4:39:10]> desc foo; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | login | varchar(255) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) (root@sagoqa) [4:39:12]> show index from foo; +-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foo | 0 | sa_foo_login_idx_1 | 1 | login | A | 0 | NULL | NULL | YES | BTREE | | +-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) (root@sagoqa) [4:39:16]> insert into foo (login) values ('a'); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:25]> insert into foo (login) values ('a'); ERROR 1062: Duplicate entry 'a' for key 1 (root@sagoqa) [4:39:26]> insert into foo (login) values ('b'); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:28]> insert into foo (login) values ('c'); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:29]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:32]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:32]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:32]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:33]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:33]> insert into foo (login) values (null); Query OK, 1 row affected (0.00 sec) (root@sagoqa) [4:39:33]> select count(*) from foo; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) (root@sagoqa) [4:39:37]> select * from foo; +-------+ | login | +-------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | a | | b | | c | +-------+ 9 rows in set (0.00 sec) (root@sagoqa) [4:39:39]> select count(*) from foo where login is null; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) (root@sagoqa) [4:39:44]> select VERSION(); +---------------------+ | VERSION() | +---------------------+ | 4.0.17-standard-log | +---------------------+ 1 row in set (0.00 sec) (root@sagoqa) [4:43:44]> alter table foo drop index sa_foo_login_idx_1; Query OK, 9 rows affected (0.04 sec) Records: 9 Duplicates: 0 Warnings: 0 (root@sagoqa) [4:46:16]> select count(*) from foo where login is null; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)