Gauthier
asked on
A query works with MySQL 5.6, mariadb 10.1 but fail on MySQL 5.7
Here are the tables (edited for typo)
Here is the query:
Goal of the query is to return the article_sections record specified (9 here) only if it is published and an english translation exist.
In MySQL 5.6 and mariadb it return one record as it should, in MySQL 5.7 it return zero records!!
Apparent cause of the problem is that record 15, 16 reversed the order of en / fr
Odd ways to make it work on MySQL 5.7:
- replace count(*) by count(id)
- remove the index KEY `ndx_article_section_trans lations_lo cale` (`locale`)
- replace 'en' by 'fr'
- change the value of id.
Remarks,
The query is generated by a framework, altering it is not really an option.
The MySQL 5.7 tested came from the laravel homestead and include MySQL 5.7.10, 5.7.11, 5.7.16.
The failure is table content/structure specific, there are lots of other tables similarly structured and similarly queried which do not fail.
However, for them the order of the translation records is always the same: en then fr.
Questions:
Is it a known bug, or did I miss something else obvious?
--
-- Structure de la table `article_sections`
--
CREATE TABLE IF NOT EXISTS `article_sections` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`position` int(11) DEFAULT NULL,
`positionable` tinyint(1) NOT NULL,
`published` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;
--
-- Contenu de la table `article_sections`
--
INSERT INTO `article_sections` (`id`, `title`, `position`, `positionable`, `published`) VALUES
(1, NULL, 0, 0, 1),
(2, NULL, 1, 1, 1),
(3, NULL, 2, 1, 1),
(4, NULL, 3, 1, 1),
(5, NULL, 4, 1, 0),
(6, NULL, 5, 0, 1),
(7, NULL, 6, 0, 1),
(8, NULL, 8, 0, 1),
(9, NULL, 0, 1, 1),
(10, NULL, 1, 1, 1),
(11, NULL, 2, 1, 1),
(12, NULL, 3, 1, 1);
-- --------------------------------------------------------
--
-- Structure de la table `article_section_translations`
--
CREATE TABLE IF NOT EXISTS `article_section_translations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`active` tinyint(1) DEFAULT '1',
`article_section_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_article_section_translations_article_section_id` (`article_section_id`),
KEY `ndx_article_section_translations_locale` (`locale`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;
--
-- Contenu de la table `article_section_translations`
--
INSERT INTO `article_section_translations` (`id`, `active`, `article_section_id`, `locale`, `title`) VALUES
(1, 1, 1, 'en', 'Featured'),
(2, 1, 1, 'fr', 'A la une'),
(3, 1, 2, 'en', 'Opera'),
(4, 1, 2, 'fr', 'Opéra'),
(5, 1, 3, 'en', 'Ballet'),
(6, 1, 3, 'fr', 'Ballet'),
(7, 1, 4, 'en', 'Concert and Recital'),
(8, 1, 4, 'fr', 'Concert et Récital'),
(9, 1, 5, 'en', 'In brief'),
(10, 1, 5, 'fr', 'En bref'),
(11, 1, 6, 'en', 'Podcasts'),
(12, 1, 6, 'fr', 'Podcasts'),
(13, 1, 7, 'en', 'Videos'),
(14, 1, 7, 'fr', 'Vidéos'),
(15, 1, 8, 'fr', 'Communiqué'),
(16, 1, 8, 'en', 'Report'),
(17, 1, 9, 'en', 'Encounters'),
(18, 1, 9, 'fr', 'Rencontres'),
(19, 1, 10, 'en', 'Backstage'),
(20, 1, 10, 'fr', 'Coulisses'),
(21, 1, 11, 'en', 'Perspectives'),
(22, 1, 11, 'fr', 'Regards'),
(23, 1, 12, 'en', 'Medias'),
(24, 1, 12, 'fr', 'Médias');
--
-- Contraintes pour la table `article_section_translations`
--
ALTER TABLE `article_section_translations`
ADD CONSTRAINT `fk_article_section_translations_article_section_id` FOREIGN KEY (`article_section_id`) REFERENCES `article_sections` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
Here is the query:
select *
from `article_sections`
where
(select count(*)
from `article_section_translations`
where `article_section_translations`.`article_section_id` = `article_sections`.`id` and `locale` = 'en') >= 1
and `published` = 1
and `article_sections`.`id` = 9
limit 1
Goal of the query is to return the article_sections record specified (9 here) only if it is published and an english translation exist.
In MySQL 5.6 and mariadb it return one record as it should, in MySQL 5.7 it return zero records!!
Apparent cause of the problem is that record 15, 16 reversed the order of en / fr
Odd ways to make it work on MySQL 5.7:
- replace count(*) by count(id)
- remove the index KEY `ndx_article_section_trans
- replace 'en' by 'fr'
- change the value of id.
Remarks,
The query is generated by a framework, altering it is not really an option.
The MySQL 5.7 tested came from the laravel homestead and include MySQL 5.7.10, 5.7.11, 5.7.16.
The failure is table content/structure specific, there are lots of other tables similarly structured and similarly queried which do not fail.
However, for them the order of the translation records is always the same: en then fr.
Questions:
Is it a known bug, or did I miss something else obvious?
ASKER
Simplified test case rewrite
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');
select * from `T1`
where
(select count(*)
from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1
and `T1`.`id` = 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Steve Bink,
Thankx,
This is a great solution. Since I cannot alter the query, (framework generated) I'll change that optimizer switch on the dev computer.
For your info:
rewriting the query as
Thankx,
This is a great solution. Since I cannot alter the query, (framework generated) I'll change that optimizer switch on the dev computer.
For your info:
rewriting the query as
select * from `T1`
where
EXISTS (select *
from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en')
and `T1`.`id` = 2
also fail, replacing * by id solve it there also.
ASKER
index_merge_intersection gave me the good query for the mysql bug system, it's probably a duplicate of:
https://bugs.mysql.com/bug.php?id=79675
Classified as critical S1 since dec 2015
I guess I better not hold my breath for a quick fix by oracle...
And now I know why all production system running the laravel php framework are using mariadb...
https://bugs.mysql.com/bug.php?id=79675
Classified as critical S1 since dec 2015
I guess I better not hold my breath for a quick fix by oracle...
And now I know why all production system running the laravel php framework are using mariadb...
FYI, this was verified as a duplicate of an existing bug report: https://bugs.mysql.com/bug.php?id=79675
Current work-arounds include turning off the optimization, using an index hint, tuning the subquery to a specific field (vs the `*`), or modifying the query to use a JOIN.
Current work-arounds include turning off the optimization, using an index hint, tuning the subquery to a specific field (vs the `*`), or modifying the query to use a JOIN.
ASKER