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 */ ;
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; 1),(-1066663936 ,2),(NULL, 3),(NULL, 4),(-1583808512 ,5),(NULL, 6),(NULL, 7),(NULL, 8),(0,9) ,(-219152384, 10);
INSERT INTO `i` VALUES (-480247808,
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 OUTCOME_ UNORDERED_ MATCH */ ;
FROM i table1 JOIN b table2 ON table1 .`col_int_key` = table2 .`col_int_key`
WHERE table1 .`pk` IN ( 4 ) /* TRANSFORM_
#/* End of test case for query 1 */
DROP TABLE i;
DROP TABLE b;