Description:
I've finally tracked down a bug to a MySQL bug and not an application one :)
This has shown up in porting an application from postgresql to mysql.
How to repeat:
table definitions:
mysql> show create table election_vote\G
*************************** 1. row ***************************
Table: election_vote
Create Table: CREATE TABLE `election_vote` (
`member_id` int(11) NOT NULL,
`candidate_id` int(11) NOT NULL,
`preference` int(11) NOT NULL,
PRIMARY KEY (`member_id`,`candidate_id`),
UNIQUE KEY `election_vote_uniq` (`member_id`,`candidate_id`,`preference`),
KEY `election_vote_candidate_id_fkey` (`candidate_id`),
CONSTRAINT `election_vote_candidate_id_fkey` FOREIGN KEY (`candidate_id`) REFERENCES `election_candidate` (`id`),
CONSTRAINT `election_vote_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
mysql> show create table election_candidate\G
*************************** 1. row ***************************
Table: election_candidate
Create Table: CREATE TABLE `election_candidate` (
`id` int(11) NOT NULL auto_increment,
`election_position_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`spiel` text,
PRIMARY KEY (`id`),
UNIQUE KEY `election_candidate_pkey` (`election_position_id`,`member_id`),
KEY `election_candidate_member_id` (`member_id`),
CONSTRAINT `election_candidate_election_position_id_fkey` FOREIGN KEY (`election_position_id`) REFERENCES `election_position` (`id`),
CONSTRAINT `election_candidate_member_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.06 sec)
mysql> select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5);
+-----------+
| member_id |
+-----------+
| XXX |
| XXX |
+-----------+
2 rows in set (0.00 sec)
mysql> create temporary table t1 as select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5);
Query OK, 53 rows affected (0.05 sec)
Records: 53 Duplicates: 0 Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 53 |
+----------+
1 row in set (0.00 sec)
NOTE: the second result is correct.
mysql> explain select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5)\G *************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: election_vote
type: range
possible_keys: NULL
key: election_vote_uniq
key_len: 4
ref: NULL
rows: 199
Extra: Using where; Using index for group-by
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: election_candidate
type: unique_subquery
possible_keys: PRIMARY,election_candidate_pkey
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using index; Using where
2 rows in set (0.00 sec)
So, let's look at the subquery:
mysql> select id from election_candidate where election_position_id=5;
+----+
| id |
+----+
| 7 |
| 5 |
+----+
2 rows in set (0.00 sec)
To further add to the fun:
mysql> select count(distinct member_id) from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +---------------------------+
| count(distinct member_id) |
+---------------------------+
| 53 |
+---------------------------+
1 row in set (0.01 sec)
mysql> select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5);
+-----------+
| member_id |
+-----------+
| XXX |
| XXX |
+-----------+
2 rows in set (0.00 sec)
mysql>
So let's explain it:
mysql> explain select count(distinct member_id) from election_vote where candidate_id in (select id from election_candidate where election_position_id=5);
+----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+
| 1 | PRIMARY | election_vote | index | NULL | election_vote_candidate_id_fkey | 4 | NULL | 2181 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | election_candidate | unique_subquery | PRIMARY,election_candidate_pkey | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5);
+----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | election_vote | range | NULL | election_vote_uniq | 4 | NULL | 199 | Using where; Using index for group-by |
| 2 | DEPENDENT SUBQUERY | election_candidate | unique_subquery | PRIMARY,election_candidate_pkey | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+
2 rows in set (0.00 sec)
Suggested fix:
use the correct index and get the correct result.
Description: I've finally tracked down a bug to a MySQL bug and not an application one :) This has shown up in porting an application from postgresql to mysql. How to repeat: table definitions: mysql> show create table election_vote\G *************************** 1. row *************************** Table: election_vote Create Table: CREATE TABLE `election_vote` ( `member_id` int(11) NOT NULL, `candidate_id` int(11) NOT NULL, `preference` int(11) NOT NULL, PRIMARY KEY (`member_id`,`candidate_id`), UNIQUE KEY `election_vote_uniq` (`member_id`,`candidate_id`,`preference`), KEY `election_vote_candidate_id_fkey` (`candidate_id`), CONSTRAINT `election_vote_candidate_id_fkey` FOREIGN KEY (`candidate_id`) REFERENCES `election_candidate` (`id`), CONSTRAINT `election_vote_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.03 sec) mysql> show create table election_candidate\G *************************** 1. row *************************** Table: election_candidate Create Table: CREATE TABLE `election_candidate` ( `id` int(11) NOT NULL auto_increment, `election_position_id` int(11) NOT NULL, `member_id` int(11) NOT NULL, `spiel` text, PRIMARY KEY (`id`), UNIQUE KEY `election_candidate_pkey` (`election_position_id`,`member_id`), KEY `election_candidate_member_id` (`member_id`), CONSTRAINT `election_candidate_election_position_id_fkey` FOREIGN KEY (`election_position_id`) REFERENCES `election_position` (`id`), CONSTRAINT `election_candidate_member_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.06 sec) mysql> select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +-----------+ | member_id | +-----------+ | XXX | | XXX | +-----------+ 2 rows in set (0.00 sec) mysql> create temporary table t1 as select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); Query OK, 53 rows affected (0.05 sec) Records: 53 Duplicates: 0 Warnings: 0 mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 53 | +----------+ 1 row in set (0.00 sec) NOTE: the second result is correct. mysql> explain select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: election_vote type: range possible_keys: NULL key: election_vote_uniq key_len: 4 ref: NULL rows: 199 Extra: Using where; Using index for group-by *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: election_candidate type: unique_subquery possible_keys: PRIMARY,election_candidate_pkey key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using index; Using where 2 rows in set (0.00 sec) So, let's look at the subquery: mysql> select id from election_candidate where election_position_id=5; +----+ | id | +----+ | 7 | | 5 | +----+ 2 rows in set (0.00 sec) To further add to the fun: mysql> select count(distinct member_id) from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +---------------------------+ | count(distinct member_id) | +---------------------------+ | 53 | +---------------------------+ 1 row in set (0.01 sec) mysql> select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +-----------+ | member_id | +-----------+ | XXX | | XXX | +-----------+ 2 rows in set (0.00 sec) mysql> So let's explain it: mysql> explain select count(distinct member_id) from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+ | 1 | PRIMARY | election_vote | index | NULL | election_vote_candidate_id_fkey | 4 | NULL | 2181 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | election_candidate | unique_subquery | PRIMARY,election_candidate_pkey | PRIMARY | 4 | func | 1 | Using index; Using where | +----+--------------------+--------------------+-----------------+---------------------------------+---------------------------------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec) mysql> explain select distinct member_id from election_vote where candidate_id in (select id from election_candidate where election_position_id=5); +----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+ | 1 | PRIMARY | election_vote | range | NULL | election_vote_uniq | 4 | NULL | 199 | Using where; Using index for group-by | | 2 | DEPENDENT SUBQUERY | election_candidate | unique_subquery | PRIMARY,election_candidate_pkey | PRIMARY | 4 | func | 1 | Using index; Using where | +----+--------------------+--------------------+-----------------+---------------------------------+--------------------+---------+------+------+---------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: use the correct index and get the correct result.