Bug #2914 Adding UNIQUE index causes unexpected result of the query
Submitted: 21 Feb 2004 6:21 Modified: 25 Feb 2004 11:23
Reporter: Victoria Reznichenko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.1 OS:
Assigned to: Dean Ellis CPU Architecture:Any

[21 Feb 2004 6:21] Victoria Reznichenko
Description:
Reported by Duncan Hill:

Mysql version: 4.1.1
Platform: Linux, pre-compiled RPMs from mysql.com

Table 1:
CREATE TABLE `notifications` (
  `recid` int(11) NOT NULL auto_increment,
  `recdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `expiry` datetime default NULL,
  `notify_title` varchar(150) default NULL,
  `notify_body` text,
  PRIMARY KEY  (`recid`),
  KEY `idx_recdate` (`recdate`)
) TYPE=MyISAM DEFAULT CHARSET=latin1

Table 2:
CREATE TABLE `notifications_seen` (
  `notifid` int(11) NOT NULL default '0',
  `notif_loginid` int(11) NOT NULL default '0',
  `seenon` datetime default NULL
) TYPE=MyISAM DEFAULT CHARSET=latin1

Table 1 contains a list of notifications that exist.
Table 2 contains mappings of which notification IDs have been seen by which
loginids.  (If you're a lloyds TSB user, you'll know what I'm aiming at.)

My query string:
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=$loginid);

My problem:
Right now, I use a routine that selects the IDs that haven't been seen, and
promptly does an insert into notifications_seen to flag that it has been
seen.  This works fine.  The moment I do  alter table notifications_seen add
unique unq_notifid_loginid (notifid, notif_loginid), my query starts
returning utter foolishness - basically, no records where there were records.
Why does adding a unique index cause this?  (Or have I just found a bug?)

Stage 1:  No unique index:
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
+-------+---------------------+
| recid | notify_title        |
+-------+---------------------+
|     3 | Some title here     |
+-------+---------------------+

explain  SELECT recid, notify_title FROM notifications WHERE recid NOT IN
(SELECT notifid FROM notifications_seen WHERE notif_loginid=2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: notifications
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: notifications_seen
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30
        Extra: Using where

Stage 2: Add unique .
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
Empty set (0.00 sec)

explain  SELECT recid, notify_title FROM notifications WHERE recid NOT IN
(SELECT notifid FROM notifications_seen WHERE notif_loginid=2);
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: notifications
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: notifications_seen
         type: index_subquery
possible_keys: unq_notif_login
          key: unq_notif_login
      key_len: 8
          ref: func,const
         rows: 2
        Extra: Using index; Using where

How to repeat:
Tables (dhill_notifications_for_reznichenko.tar) were uploaded to the ftp.mysql.com:/pub/mysql/Incoming directory.

SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);

ALTER TABLE notifications_seen ADD UNIQUE unq_notifid_loginid (notifid, notif_loginid);

SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
[21 Feb 2004 9:53] Sinisa Milivojevic
test case

Attachment: dhill_notifications_for_reznichenko.tar (application/x-tar, text), 30.00 KiB.

[25 Feb 2004 11:23] Dean Ellis
The supplied tables do not have data consistent with the test case (the queries should and do return empty sets for the provided data).

Aside from that, however, this issue appears to have been already corrected in the 4.1.2 tree as I am unable to reproduce it.  (It may be related to 2089.)