[#MDEV-20283] Optimizer bug when making INNER JOIN with Foreign Keys

It seems that optimizer fails when executing INNER JOIN select when tables are related by a foreign key.

CREATE TABLE `default_work` (

  `add_date` datetime(6) NOT NULL,

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(255) NOT NULL,

  `keywords` varchar(255) DEFAULT NULL,

  `short_text` longtext DEFAULT NULL,

  `downloads` int(10) unsigned NOT NULL,

  `published` tinyint(1) NOT NULL,

  `subject_id` int(11) NOT NULL,

  `work_type_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `default_work_subject_id_IDX` (`subject_id`) USING BTREE,

  KEY `default_work_work_type_id_IDX` (`work_type_id`) USING BTREE,

  CONSTRAINT `default_work_FK` FOREIGN KEY (`subject_id`) REFERENCES `default_subject` (`id`),

  CONSTRAINT `default_work_FK_1` FOREIGN KEY (`work_type_id`) REFERENCES `default_worktype` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=210673 DEFAULT CHARSET=utf8

CREATE TABLE `default_subject` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `subject` varchar(255) NOT NULL,

  `old_id` int(10) unsigned NOT NULL,

  `subject_literal` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8

CREATE TABLE `default_worktype` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `work_type` varchar(250) NOT NULL,

  `description` longtext DEFAULT NULL,

  `old_id` int(10) unsigned NOT NULL,

  `work_type_literal` varchar(250) NOT NULL,

  `title` varchar(255) NOT NULL,

  `multiple` varchar(255) NOT NULL,

  `keywords` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `default_worktype_old_id_a8b508fe_uniq` (`old_id`),

  UNIQUE KEY `default_worktype_work_type_literal_1e609434_uniq` (`work_type_literal`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

The default_work has about 200,000 records, default_subject - 42, and default_worktype - 12

SELECT `default_work`.`id`, `default_work`.`title`, `default_worktype`.`work_type`,`default_subject`.`subject`

FROM `default_work`

INNER JOIN `default_subject` ON (`default_work`.`subject_id` = `default_subject`.`id`)

INNER JOIN `default_worktype` ON (`default_work`.`work_type_id` = `default_worktype`.`id`)

ORDER BY `default_work`.`id` DESC LIMIT 100

I got about 9 seconds of execution time, with explain says what no primary key is used. But when I deleted foreign keys and appropriate indexes - the query runs fast and smooth. (Both explain results shown in attached images).

[MDEV-20283] Optimizer bug when making INNER JOIN with Foreign Keys Created: 2019-08-08 Updated: 2019-08-08
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4.7
Fix Version/s:	10.4

[MDEV-20283] Optimizer bug when making INNER JOIN with Foreign Keys Created: 2019-08-08 Updated: 2019-08-08