| Bug #79203 | incorrect result set with block_nested_loop=on | ||
|---|---|---|---|
| Submitted: | 10 Nov 2015 11:59 | Modified: | 10 Nov 2015 13:25 |
| Reporter: | Heike Reuter | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.25 | OS: | Debian (Debian GNU/Linux 7.8 (wheezy)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | block_nested_loop | ||
[10 Nov 2015 12:03]
Heike Reuter
Version is 5.6.25
[10 Nov 2015 13:04]
MySQL Verification Team
mysql 5.6 > select `view_object`.`id`, `view_object`.`active`, `view_object`.`activefrom`, `view_object`.`activeto`, `view_object`.`title`
-> from view_object
-> where (view_object.active = 1 or (view_object.activefrom < '2015-11-01 12:00:00' and view_object.activeto > '2015-11-21 12:00:00' ) ) and (
-> select
-> if(EXISTS(select 1 from type2object, typeA
-> where typeA.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeA' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeA' and type2object.typeid='firstA'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeB
-> where view_typeB.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeB' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeB' and type2object.typeid='firstB'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeC
-> where view_typeC.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='oxgroups' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeC' and type2object.typeid in ('firstC') ),
-> 1)
-> );
Empty set (0.01 sec)
mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------+
| innodb_version | 5.6.28 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.28 |
| version_comment | Source distribution PULL: 2015-NOV-07 |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)
mysql 5.5 > select `view_object`.`id`, `view_object`.`active`, `view_object`.`activefrom`, `view_object`.`activeto`, `view_object`.`title`
-> from view_object
-> where (view_object.active = 1 or (view_object.activefrom < '2015-11-01 12:00:00' and view_object.activeto > '2015-11-21 12:00:00' ) ) and (
-> select
-> if(EXISTS(select 1 from type2object, typeA
-> where typeA.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeA' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeA' and type2object.typeid='firstA'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeB
-> where view_typeB.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeB' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeB' and type2object.typeid='firstB'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeC
-> where view_typeC.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='oxgroups' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeC' and type2object.typeid in ('firstC') ),
-> 1)
-> );
+--------------+--------+---------------------+---------------------+---------------+
| ID | ACTIVE | ACTIVEFROM | ACTIVETO | TITLE |
+--------------+--------+---------------------+---------------------+---------------+
| firstObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | first object |
| secondObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | second object |
+--------------+--------+---------------------+---------------------+---------------+
2 rows in set (0.02 sec)
mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------+
| innodb_version | 5.5.47 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.47 |
| version_comment | Source distribution PULL: 2015-NOV-07 |
| version_compile_machine | AMD64 |
| version_compile_os | Win64 |
+-------------------------+---------------------------------------+
7 rows in set (0.13 sec)
[10 Nov 2015 13:25]
MySQL Verification Team
Thank you for the bug report. Only 5.6 affected.
mysql 5.7 > select `view_object`.`id`, `view_object`.`active`, `view_object`.`activefrom`, `view_object`.`activeto`, `view_object`.`title`
-> from view_object
-> where (view_object.active = 1 or (view_object.activefrom < '2015-11-01 12:00:00' and view_object.activeto > '2015-11-21 12:00:00' ) ) and (
-> select
-> if(EXISTS(select 1 from type2object, typeA
-> where typeA.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeA' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeA' and type2object.typeid='firstA'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeB
-> where view_typeB.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeB' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeB' and type2object.typeid='firstB'),
-> 1) &&
-> if(EXISTS(select 1 from type2object, view_typeC
-> where view_typeC.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='oxgroups' LIMIT 1),
-> EXISTS(select type2object.id from type2object
-> where type2object.objectid=view_object.id and type2object.type='typeC' and type2object.typeid in ('firstC') ),
-> 1)
-> );
+--------------+--------+---------------------+---------------------+---------------+
| ID | ACTIVE | ACTIVEFROM | ACTIVETO | TITLE |
+--------------+--------+---------------------+---------------------+---------------+
| firstObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | first object |
| secondObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | second object |
+--------------+--------+---------------------+---------------------+---------------+
2 rows in set (0.00 sec)
mysql 5.7 > show variables like "version";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.7.10 |
+---------------+--------+
1 row in set (0.00 sec)
mysql 5.7 >
[2 Jun 2021 7:24]
Bernhard Scheffold
There is a regression for this bug in MySQL 5.7.29. A workaround is to set all optimizer switches to off: set global optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off';
Description: We get incorrect results (empty result set where it should not be empty) for MySQL 5.6.25 and optimizer switch block_nested_loop=on for a certain type of query, see below. With optimizer switch block_nested_loop=off result set is as expected. Same test setup with MySQL 5.5 works fine. How to repeat: Example database and tables DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; CONNECT `test`; CREATE TABLE `object` ( `ID` char(32) COLLATE latin1_general_ci NOT NULL COMMENT 'id', `MAPID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Integer mapping identifier', `SHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id', `ACTIVE` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Active', `ACTIVEFROM` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Active from specified date', `ACTIVETO` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Active to specified date', `TITLE` varchar(128) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'Title', PRIMARY KEY (`ID`), KEY `SHOPID` (`SHOPID`), KEY `MAPID` (`MAPID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='objects'; INSERT INTO object (`ID`, `MAPID`, `SHOPID`, `ACTIVE`, `ACTIVEFROM`, `ACTIVETO`, `TITLE`) VALUES ('firstObject', 1, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'first object'), ('secondObject', 2, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'second object'), ('thirdObject', 2, 1, 1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'third object'); CREATE TABLE `object2shop` ( `SHOPID` int(11) NOT NULL COMMENT 'Mapped shop id', `MAPOBJECTID` int(11) NOT NULL COMMENT 'Mapped object id', UNIQUE KEY `MAPIDX` (`SHOPID`,`MAPOBJECTID`), KEY `MAPOBJECTID` (`MAPOBJECTID`), KEY `SHOPID` (`SHOPID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Mapping table for element subshop assignments'; INSERT INTO object2shop (`SHOPID`, `MAPOBJECTID`) VALUES (1, 1), (1, 2), (1, 3); CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `view_object` AS select `object`.`ID` AS `ID`, `object`.`MAPID` AS `MAPID`, `object`.`SHOPID` AS `SHOPID`, `object`.`ACTIVE` AS `ACTIVE`, `object`.`ACTIVEFROM` AS `ACTIVEFROM`, `object`.`ACTIVETO` AS `ACTIVETO`, `object`.`TITLE` AS `TITLE` from (`object` join `object2shop` `t2s` on((`t2s`.`MAPOBJECTID` = `object`.`MAPID`))) where (`t2s`.`SHOPID` = 1); CREATE TABLE `typeA` ( `ID` char(32) COLLATE latin1_general_ci NOT NULL COMMENT 'id', `CONTENTS` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'contents', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='typeA'; INSERT INTO typeA (`ID`,`CONTENTS`) VALUES ('firstA', 'first type A'); CREATE TABLE `typeB` ( `ID` char(32) COLLATE latin1_general_ci NOT NULL COMMENT 'id', `CONTENTS` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'contents', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='typeB'; INSERT INTO typeB (`ID`,`CONTENTS`) VALUES ('firstB', 'first type B'), ('secondB', 'second type B'); CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `view_typeB` AS select `typeB`.`ID` AS `ID`, `typeB`.`CONTENTS` AS `CONTENTS` from `typeB`; CREATE TABLE `typeC` ( `ID` char(32) COLLATE latin1_general_ci NOT NULL COMMENT 'id', `CONTENTS` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'contents', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='typeC'; INSERT INTO typeB (`ID`,`CONTENTS`) VALUES ('firstC', 'first type C'); CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `view_typeC` AS select `typeC`.`ID` AS `ID`, `typeC`.`CONTENTS` AS `CONTENTS` from `typeC`; CREATE TABLE `type2object` ( `ID` char(32) COLLATE latin1_general_ci NOT NULL COMMENT 'id', `OBJECTID` char(32) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'object.id', `TYPEID` char(32) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'type id', `TYPE` char(32) COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'Record type', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='relationship table'; INSERT INTO type2object (`ID`, `OBJECTID`, `TYPEID`, `TYPE`) VALUES ('relationB', 'secondObject', 'firstB', 'typeB'), ('relationC', 'thirdObject', 'secondB', 'typeB'); Query select `view_object`.`id`, `view_object`.`active`, `view_object`.`activefrom`, `view_object`.`activeto`, `view_object`.`title` from view_object where (view_object.active = 1 or (view_object.activefrom < '2015-11-01 12:00:00' and view_object.activeto > '2015-11-21 12:00:00' ) ) and ( select if(EXISTS(select 1 from type2object, typeA where typeA.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeA' LIMIT 1), EXISTS(select type2object.id from type2object where type2object.objectid=view_object.id and type2object.type='typeA' and type2object.typeid='firstA'), 1) && if(EXISTS(select 1 from type2object, view_typeB where view_typeB.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeB' LIMIT 1), EXISTS(select type2object.id from type2object where type2object.objectid=view_object.id and type2object.type='typeB' and type2object.typeid='firstB'), 1) && if(EXISTS(select 1 from type2object, view_typeC where view_typeC.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='oxgroups' LIMIT 1), EXISTS(select type2object.id from type2object where type2object.objectid=view_object.id and type2object.type='typeC' and type2object.typeid in ('firstC') ), 1) ); Short query, does not change the outcome select `view_object`.`id`, `view_object`.`active`, `view_object`.`activefrom`, `view_object`.`activeto`, `view_object`.`title` from view_object where (view_object.active = 1 or (view_object.activefrom < '2015-11-01 12:00:00' and view_object.activeto > '2015-11-21 12:00:00' ) ) and ( select if(EXISTS(select 1 from type2object, view_typeB where view_typeB.id=type2object.typeid and type2object.objectid=view_object.id and type2object.type='typeB' LIMIT 1), EXISTS(select type2object.id from type2object where type2object.objectid=view_object.id and type2object.type='typeB' and type2object.typeid='firstB'), 1) ); Result with MySQL 5.5 .. code:: mysql Query result: +--------------+--------+---------------------+---------------------+---------------+ | ID | ACTIVE | ACTIVEFROM | ACTIVETO | TITLE | +--------------+--------+---------------------+---------------------+---------------+ | firstObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | first object | | secondObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | second object | +--------------+--------+---------------------+---------------------+---------------+ 2 rows in set (0.01 sec) Result with MySQL 5.6.25 and optimizer_switch block_nested_loop=off .. code:: mysql Set SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on'; Query result: +--------------+--------+---------------------+---------------------+---------------+ | ID | ACTIVE | ACTIVEFROM | ACTIVETO | TITLE | +--------------+--------+---------------------+---------------------+---------------+ | firstObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | first object | | secondObject | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | second object | +--------------+--------+---------------------+---------------------+---------------+ 2 rows in set (0.00 sec) Result with MySQL 5.6.25 and optimizer_switch block_nested_loop=on .. code:: mysql Set SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on'; Query result: Empty set (0.00 sec)