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:
None 
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 11:59] Heike Reuter
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)
[10 Nov 2015 12:03] Heike Reuter
Version is 5.6.25
[10 Nov 2015 13:04] Miguel Solorzano
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] Miguel Solorzano
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 >