Description:
.....
How to repeat:
CREATE TABLE `A` (
`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`)
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci ;
CREATE TABLE `B` (
`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`A_id` mediumint(8) UNSIGNED NOT NULL,
`value` tinyint(3) UNSIGNED NOT NULL,
PRIMARY KEY(`id`),
CONSTRAINT `rfc1` FOREIGN KEY (`A_id`)
REFERENCES `A` (`id`)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci ;
INSERT INTO `A` (`id`) VALUES (NULL);
INSERT INTO `B` VALUES (NULL, 1, 1);
INSERT INTO `B` VALUES (NULL, 1, 2);
INSERT INTO `B` VALUES (NULL, 1, 3);
INSERT INTO `B` VALUES (NULL, 1, 4);
SELECT * FROM `B` WHERE `A_id` = 1 ORDER BY `id` DESC LIMIT 1;
It doesn't order at all. I assume it's a bug, because when I execute it in version 5.0.33, everything works just fine.
When I try:
SELECT * FROM `B` HAVING `A_id` = 1 ORDER BY `id` DESC LIMIT 1;
it works ok, and actually, I do not see any "difference" between these queries.
Description: ..... How to repeat: CREATE TABLE `A` ( `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`) ) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci ; CREATE TABLE `B` ( `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT, `A_id` mediumint(8) UNSIGNED NOT NULL, `value` tinyint(3) UNSIGNED NOT NULL, PRIMARY KEY(`id`), CONSTRAINT `rfc1` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci ; INSERT INTO `A` (`id`) VALUES (NULL); INSERT INTO `B` VALUES (NULL, 1, 1); INSERT INTO `B` VALUES (NULL, 1, 2); INSERT INTO `B` VALUES (NULL, 1, 3); INSERT INTO `B` VALUES (NULL, 1, 4); SELECT * FROM `B` WHERE `A_id` = 1 ORDER BY `id` DESC LIMIT 1; It doesn't order at all. I assume it's a bug, because when I execute it in version 5.0.33, everything works just fine. When I try: SELECT * FROM `B` HAVING `A_id` = 1 ORDER BY `id` DESC LIMIT 1; it works ok, and actually, I do not see any "difference" between these queries.