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
• Status: Can't repeat
• 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

[2 Aug 2006 15:04] Michael Peters

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.

[2 Aug 2006 15:11] Michael Peters

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.

[2 Aug 2006 17:26] Miguel Solorzano

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

[23 Aug 2006 14:36] Igor Babaev

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)