Bug #22 double IN subselect incorrect results

• Submitted: 5 Jan 2003 18:20
• Modified: 17 May 2004 19:17
• Reporter: Oleksandr Byelkin
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Any (any)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[5 Jan 2003 18:20] Oleksandr Byelkin

Description:
double IN subselect incorrect results 

How to repeat:
mysql> create table a (a int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table b (b int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into a values (1),(2); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
mysql> insert into b values (1); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select a from a where a in (select a from a where a in (select b from 
b)); 
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
+------+ 
 

Suggested fix:
none for now

[28 Jan 2003 10:28] MySQL Developer

in my current developent tree this bug is gone

[17 May 2004 18:49] Mark Fowler

I've just experienced this bug on 4.1.1.  Has the bug reoccured, or did the changes that fix this that Oleksandr Byelkin mentioned never migrate into the current alpha?

Mark Fowler
[e-mail]

[17 May 2004 18:52] Mark Fowler

To confirm, I'm trying this:

Create the table:

CREATE TABLE `mytable` (
  `id` int(11) default NULL,
  `colour` text,
  `value` int(11) default NULL
) TYPE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("1","blue","100");
INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("2","blue","500");
INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("3","red","100");
INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("4","red","500");
INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("5","green","100");
INSERT INTO `mytable` (`id`,`colour`,`value`) VALUES ("6","green","500");

Run the query:

SELECT * FROM mytable
  WHERE id IN (SELECT id FROM mytable WHERE colour = "blue")
    AND id IN (SELECT id FROM mytable WHERE value < 400);

Expected result:
  1, blue, 100

Actual result with Mysql 4.1.1-alpha:
  all rows from table

[17 May 2004 18:57] Mark Fowler

Rearanging the brackets in the double IN to match Oleksandr Byelkin's structure works.  So this:

SELECT * FROM mytable
  WHERE id IN (SELECT id FROM mytable
                WHERE colour = "blue"
                  AND id IN (SELECT id FROM mytable WHERE value < 400));

Returns the expected results.  However, the previous SQL (without the nested INs) really should work.  Maybe this should therefore be submitted as a seperate bug.  Please advise if that's the case.

[17 May 2004 19:17] Alexander Keremidarski

Both queries work well for me:

mysql> SELECT VERSION(); 
+-----------------------+
| VERSION()             |
+-----------------------+
| 4.1.2-alpha-debug-log |
+-----------------------+

This is pre-4.1.2 from our source tree.

mysql> SELECT * FROM mytable
    ->   WHERE id IN (SELECT id FROM mytable WHERE colour = "blue")
    ->     AND id IN (SELECT id FROM mytable WHERE value < 400);
+------+--------+-------+
| id   | colour | value |
+------+--------+-------+
|    1 | blue   |   100 |
+------+--------+-------+
1 row in set (0.04 sec)
 
 
mysql> SELECT * FROM mytable
    ->   WHERE id IN (SELECT id FROM mytable
    ->                 WHERE colour = "blue"
    ->                   AND id IN (SELECT id FROM mytable WHERE value < 400));
+------+--------+-------+
| id   | colour | value |
+------+--------+-------+
|    1 | blue   |   100 |
+------+--------+-------+