MySQL Forums
Forum List  »  General

5.7 missing a row in response with correlated subquery bug?
Posted by: Gauthier Van Vreckem
Date: February 03, 2017 03:53PM

Test case

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;

CREATE TABLE `T1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `T1` (`id`) VALUES
(1),
(2);


CREATE TABLE `T2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t1_id` int(10) unsigned DEFAULT NULL,
`locale` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ndx_t2_t1_id` (`t1_id`),
KEY `ndx_t2_locale` (`locale`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `T2` (`id`, `t1_id`, `locale`, `title`) VALUES
(1, 1, 'en', 'e1'),
(2, 1, 'fr', 'f1'),
(3, 2, 'en', 'e2'),
(4, 2, 'fr', 'f2');

This query works in 5.6 (return row 9)
But it fails in 5.7.10, 5.7.11, 5.7.16 (doesn't return any row)

select * from `T1`
where
(select count(*)
from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1
and `T1`.`id` = 2

Odd ways to make it work in 5.7:
- replace count(*) by count(id)
- remove the index KEY `ndx_t2_locale` (`locale`)
- replace 'en' by 'fr'

There is no question, the index on locale make no sense and I can remove it. Still I would expect a correct answer from 5.7

Did I hit a known bug?
Is it resolved in the latest 5.7 ?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.