MySQL Bugs: #2089: subquery's in where clause returning incorrect results

Bug #2089	subquery's in where clause returning incorrect results
Submitted:	11 Dec 2003 1:41	Modified:	17 Jan 2004 5:52
Reporter:	Murray Steele	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1	OS:	Windows (Windows, Solaris)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
The following seems to be another subquery fault.  I'm not sure what the problem is, just that it doesn't return the correct results, and worryingly at one point returns different results the first time you run it.

Seems to me that the subquery isn't always getting run the correct number of times.  I know there were bugs in 4.1.0 similar to this (as when using 4.1.0 the below sql pretty much was useless, now it's less so) so it's possible that this slipped through the net.

If the tables are created as myisam it all works fine, but in my app I need the transactional support of innodb.

How to repeat:
CREATE TABLE TERM (
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(200) NOT NULL,
    LOGICALLY_DELETED_INDICATOR TINYINT(1) DEFAULT 0 NOT NULL,
) type = innodb;
insert into term values (1,'term1',0);
insert into term values (2,'term2',0);
insert into term values (3,'npterm1',0);
insert into term values (4,'npterm2',0);
insert into term values (5,'npterm1and2',0);

CREATE TABLE RELATIONSHIP (
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SOURCE_TERM_ID INTEGER UNSIGNED NOT NULL,
    TARGET_TERM_ID INTEGER UNSIGNED NOT NULL,
    INDEX RELATIONSHIP_FROM_TO_TERM (SOURCE_TERM_ID,TARGET_TERM_ID),
    INDEX RELATIONSHIP_FROM_TERM (SOURCE_TERM_ID),
    INDEX RELATIONSHIP_TO_TERM (TARGET_TERM_ID)
) type = innodb;
insert into relationship values (1,3,1);
insert into relationship values (2,4,2);
insert into relationship values (3,5,1);
insert into relationship values (4,5,2);

First we try to find out which terms are related to 'term1' (id: 1) that aren't related to any other 'live' terms.
A 'live' term is one that has it's logically_deleted_indicator field set to 0.

select t.NAME
from RELATIONSHIP r, 
     TERM t 
where r.TARGET_TERM_ID = 1 
  and r.SOURCE_TERM_ID = t.ID 
  and t.LOGICALLY_DELETED_INDICATOR = 0
  and not exists (
       select 1 
         from RELATIONSHIP r2,
              TERM t2
        where r2.TARGET_TERM_ID != 1
          and r2.SOURCE_TERM_ID = r.SOURCE_TERM_ID
          and r2.TARGET_TERM_ID = t2.ID
          and t2.LOGICALLY_DELETED_INDICATOR = 0
    )

First run (seems to be first run on that data, rather than first run per connection or whatever) returns: 
+-------------+
| NAME        |
+-------------+
| npterm1     |
| npterm1and2 |
+-------------+

This is WRONG.  'npterm1and2' is also related to 'term2' which is a live term)

Second and subsequent runs return:
+---------+
| NAME    |
+---------+
| npterm1 |
+---------+
This is CORRECT

Then we 'delete' 'term1' and the terms that are only related to it. 

update term set logically_deleted_indicator = 1 where id = 1 or id = 3;

Then w try to find out which terms are related to 'term2' (id: 2) that aren't related to any other 'live' terms

select t.NAME
from RELATIONSHIP r, 
     TERM t 
where r.TARGET_TERM_ID = 2
  and r.SOURCE_TERM_ID = t.ID 
  and t.LOGICALLY_DELETED_INDICATOR = 0
  and not exists (
       select 1 
         from RELATIONSHIP r2,
              TERM t2
        where r2.TARGET_TERM_ID != 2
          and r2.SOURCE_TERM_ID = r.SOURCE_TERM_ID
          and r2.TARGET_TERM_ID = t2.ID
          and t2.LOGICALLY_DELETED_INDICATOR = 0
    )

This returns: 
+---------+
| NAME    |
+---------+
| npterm2 |
+---------+

This is WRONG - should return:

+-------------+
| NAME        |
+-------------+
| npterm2     |
| npterm1and2 |
+-------------+

This is becuase 'npterm2' is only related to 'term2' and although 'npterm1and2' is related to 'term1', 'term1' is marked as deleted.

Is my example simpler belong to this problem too?
Version: 4.1.1-alpha-max-log
OS: Slackware 9.1

mysql> create table test1 (id int not null auto_increment, value char(255), primary key(id)) type=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> create table test2 (id int not null auto_increment, value char(255), primary key(id)) type=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (1,'a');
Query OK, 1 row affected (0.04 sec)

mysql> insert into test1 values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values (3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 values (1,'z');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 values (2,'x');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 values (3,'y');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+----+-------+
| id | value |
+----+-------+
|  1 | z     |
|  2 | x     |
|  3 | y     |
+----+-------+
3 rows in set (0.00 sec)

mysql> select test2.id,test2.value,(select test1.value from test1 where test1.id=test2.id) from test2;

 I think that this result is WRONG (because I run this test on Oracle and get other result).

+----+-------+---------------------------------------------------------+
| id | value | (select test1.value from test1 where test1.id=test2.id) |
+----+-------+---------------------------------------------------------+
|  1 | z     | a                                                       |
|  2 | x     | NULL                                                    |
|  3 | y     | NULL                                                    |
+----+-------+---------------------------------------------------------+
3 rows in set (0.00 sec)

Should be:
1 z a
2 x b
3 y c

ChangeSet 
  1.1674 04/01/03 00:12:07 bell@sanja.is.com.ua +12 -0 
  removed droping field->query_id for reinitialization tables for subquery. 
  (BUG#2089)

Thank you for bugreport. Bug is fixed now in pour source repository