Bug #38015 Bad result: inconsistent treatment of comparisons between data types in subquery
Submitted: 10 Jul 2008 13:11 Modified: 14 Sep 2009 10:50
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:6.0,5.4 OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: regression, subquery

[10 Jul 2008 13:11] Philip Stoev
Description:
Those three queries are equivalent but return different results:

1) SELECT  int_nokey FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR);
2) SELECT  * FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR ORDER BY pk);
3) SELECT  * FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR);

Please note that we are comparing int_nokey to varchar_nokey. The only way for this comparison to be true is for int_nokey to be 0 .

Query #1 returns 10 rows, equal to the number of rows in C, however for all it reports that int_nokey is zero. In fact, int_nokey is zero only in four rows.

Query #2 returns zero rows;

Query #2 returns four rows, which is the correct answer.

All those queries use different execution plans. In 5.1 all those queries returned the correct answer of 4 rows.

How to repeat:
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

INSERT INTO `C` VALUES (1,NULL,NULL),(2,NULL,'p'),(3,0,NULL),(4,6,'e'),(5,0,'w'),(6,0,NULL),(7,3,'p'),(8,9,'d'),(9,5,'w'),(10,0,'z');

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

INSERT INTO `CC` VALUES (1,3,NULL),(2,7,'x'),(3,0,'u'),(4,9,NULL),(5,7,'{'),(6,5,'i'),(7,0,NULL),(8,2,'i'),(9,4,'a'),(10,2,'t');

SELECT  int_nokey FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR);
SELECT  * FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR ORDER BY pk);
SELECT  * FROM C AS OUTR WHERE  int_nokey IN ( SELECT  INNR . `varchar_nokey` AS Y  FROM CC AS INNR);

Suggested fix:
* Uniform handling of comparisons between data types, even if they do not make sense;
* ORDER BY in an IN() subquery can be optimized away. Subqueries with and without ORDER BY should show the same execution plan.
[10 Jul 2008 14:32] MySQL Verification Team
Thank you for the bug report. Repeatable on 6.0.4/6.0.6. Not repeatable on
5.0/5.1/6.0.2.
[14 Jul 2009 22:21] Patrick Crews
My testing showed matching result sets between 5.1 and azalea for queries 2 and 3.
Query 1 produced differing result sets that were not affected by azalea's optimizer_switch, engine_condition_pushdown, and optimizer_use_mrr values.
[14 Sep 2009 10:50] Øystein Grøvlen
I am not able to repeat this on the current head of mysql-next.
All queries return 4 rows.
[14 Sep 2009 15:01] Philip Stoev
Agree. Bug is no longer repeatable.