Link to home
Start Free TrialLog in
Avatar of Gauthier
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)
--
-- 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;

Open in new window


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

Open in new window


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_translations_locale` (`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?
Avatar of Gauthier
Gauthier

ASKER

sorry, made a typo in the table code, it's corrected
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');

Open in new window


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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
select * from `T1` 
where 
EXISTS (select *
  from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en')
and `T1`.`id` = 2

Open in new window

also fail, replacing * by id solve it there also.
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...
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.