Description:
In certain cases, use of ORDER BY in a subselect did not produce the expected result (does not return result, return NULL).
How to repeat:
Shema / data:
CREATE TABLE IF NOT EXISTS `ttable_1` (
`tt1_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`tt1_param` int(11) unsigned DEFAULT NULL,
`tt1_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tt1_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `ttable_1` (`tt1_id`, `tt1_param`, `tt1_name`) VALUES
(1, 514, '321'),
(2, 792, '456');
CREATE TABLE IF NOT EXISTS `ttable_2` (
`tt2_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tt2_param` int(11) DEFAULT NULL,
`tt2_name` varchar(255) NOT NULL DEFAULT '',
`tt2_rm` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`tt2_id`) USING BTREE,
KEY `i1` (`tt2_param`) USING BTREE,
KEY `i2` (`tt2_name`,`tt2_rm`,`tt2_param`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `ttable_2` (`tt2_id`, `tt2_param`, `tt2_name`, `tt2_rm`) VALUES
(1, 792, 'KK', 1);
Query:
SELECT
(
SELECT tt2_id
FROM ttable_2
WHERE (tt1_name = tt2_name OR (tt2_rm = 1)) AND (tt1_param = tt2_param OR tt2_param IS NULL)
ORDER BY tt2_id DESC
LIMIT 1
) as id
FROM ttable_1
#WHERE tt1_id = 2
Result (5.7.22-5.7.32; 8.0.12): NULL, NULL
Excepted/correct result (5.6.x; 8.0.18): NULL, 1
Also: If I delete indexes or delete ORDER BY or add WHERE to main query it start return correct result.
Looks like it fixed in last 8.0.x versions, but not fixed in 5.7
