MySQL Bugs: #21418: Correlated subqueries with UNIQUE columns used in WHERE clause are incorrect

Bug #21418	Correlated subqueries with UNIQUE columns used in WHERE clause are incorrect
Submitted:	2 Aug 2006 15:04	Modified:	23 Aug 2006 14:36
Reporter:	Michael Peters	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.22/4.1;5.0;5.1BK	OS:	Linux (Linux (FC3))
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	correlated, subquery, unique

Description:
In 5.0.22 if a correlated subquery uses a column in a table that has a UNIQUE constraint, then your query will return 0 results, even though the subquery run by itself returns the correct results. This does not happen in 4.1 (tested on 4.12-4.1.21).

How to repeat:
Use the following to create your tables:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `t1_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1),(2);

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `t2_id` int(11) default NULL,
  `t3_id` smallint(5) unsigned default NULL,
  `t1_id` int(10) unsigned default NULL,
  UNIQUE KEY (`t2_id`),
  KEY (`t3_id`),
  KEY (`t1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t2` VALUES (1,1,1),(13,13,2);

DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `t3_id` smallint(5) unsigned NOT NULL auto_increment,
  `foo` int(11) default NULL,
  PRIMARY KEY  (`t3_id`),
  UNIQUE KEY (`foo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t3` VALUES (13,1039),(1,1011);

Then run the following query on 5.0.22:
SELECT t1.t1_id FROM t1 WHERE (SELECT COUNT(*) FROM t2 JOIN t3 ON(t2.t3_id = t3.t3_id) WHERE t3.foo = '1039' AND t1.t1_id = t2.t1_id) >= 1;

It will not return any results. This is wrong, it should return a result of 1 row where the t1_id is 2.

Now, change the KEY for the 'foo' column in the 't3' table from "UNIQUE KEY" to just "KEY' and re-run the same query and it will return the correct results.

Running EXPLAIN on the query for both 4.1 and 5.0.22 gave the exact same results even though the results of the execution where different.

Thank you for the bug report. Verified as described:

mysql> SELECT t1.t1_id FROM t1 WHERE (SELECT COUNT(*) FROM t2 JOIN t3 ON(t2.t3_id =
    -> t3.t3_id) WHERE t3.foo = '1039' AND t1.t1_id = t2.t1_id) >= 1;
Empty set (0.02 sec)

mysql> alter table t3 drop key foo;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t3 add index(foo);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT t1.t1_id FROM t1 WHERE (SELECT COUNT(*) FROM t2 JOIN t3 ON(t2.t3_id =
    -> t3.t3_id) WHERE t3.foo = '1039' AND t1.t1_id = t2.t1_id) >= 1;
+-------+
| t1_id |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec

I failed to repeat the problem with:mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.25-debug |
+--------------+

mysql> SELECT t1.t1_id FROM t1 WHERE (SELECT COUNT(*) FROM t2 JOIN t3 ON(t2.t3_id =
    -> t3.t3_id) WHERE t3.foo = '1039' AND t1.t1_id = t2.t1_id) >= 1;
+-------+
| t1_id |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)