Comment 1 for bug 586051

Patrick Crews (patrick-crews) wrote :

Test case:
Run and observe the difference in result sets between query 0 and query 1

#/* Server0: Drizzle 2010.05.1561 */

--disable_warnings
DROP TABLE IF EXISTS `i`;
CREATE TABLE `i` (
  `col_int_key` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB;

ALTER TABLE `i` DISABLE KEYS;
INSERT INTO `i` VALUES (-480247808,1),(-1066663936,2),(NULL,3),(NULL,4),(-1583808512,5),(NULL,6),(NULL,7),(NULL,8),(0,9),(-219152384,10);
ALTER TABLE `i` ENABLE KEYS;

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `col_int_key` int DEFAULT NULL,
  `col_int` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB;

ALTER TABLE `b` DISABLE KEYS;
INSERT INTO `b` VALUES (NULL,7,1);
ALTER TABLE `b` ENABLE KEYS;
--enable_warnings

#/* Begin test case for query 0 */

SELECT table2 .`col_int` field3
FROM i table1 JOIN b table2 ON table1 .`col_int_key` = table2 .`col_int_key`
WHERE table1 .`pk` IN ( 4 )
ORDER BY field3 ;
#/* End of test case for query 0 */

#/* Begin test case for query 1 */

SELECT table2 .`col_int` field3
FROM i table1 JOIN b table2 ON table1 .`col_int_key` = table2 .`col_int_key`
WHERE table1 .`pk` IN ( 4 ) /* TRANSFORM_OUTCOME_UNORDERED_MATCH */ ;

#/* End of test case for query 1 */

DROP TABLE i;
DROP TABLE b;