Bug #87339 Duplicating the condition gives a different result set
Submitted: 7 Aug 2017 12:36 Modified: 30 Oct 2018 8:12
Reporter: Magnar Myrtveit Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7 and 8.0, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[7 Aug 2017 12:36] Magnar Myrtveit
Description:
I have the following query:

SELECT *
FROM dp_organisation_member t82
WHERE (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level + '1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
) AND (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level+'1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
)
As you can see, the two conditions are identical, so if I removed one of the conditions, the query should give the same result. However, the query returns a different result when both of the conditions are there, than when using only one of them!

The inner query used in the conditions (below) returns 115,131,153 in my case.

SELECT GROUP_CONCAT(`Ids`)
FROM (
   SELECT @Level := @Level+'1' `Level`, @Ids := (
      SELECT GROUP_CONCAT(`OrganisationId`)
      FROM dp_organisation
      WHERE FIND_IN_SET(`ParentId`, @Ids)
   ) `Ids`
   FROM (SELECT @Ids := '1', @Level := '0') temp1
   INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
) temp2
When both conditions are used, the result only includes the rows having OrganisationId = 1. When only one of the conditions is used, also the rows having OrganisationId equal to 115, 131, or 153 are included.

So the query below produces the correct result:

SELECT *
FROM dp_organisation_member t82
WHERE (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level + '1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
)
Also if I substitute one of the inner queries with the result of that query, the query gives the correct result:

SELECT *
FROM dp_organisation_member t82
WHERE (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, "115,131,153")
) AND (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level+'1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
)
The first query in this question, having the duplicated condition, however, does not give the correct result.

How to repeat:
Build this schema:

CREATE TABLE `dp_organisation` (
  `OrganisationId` bigint(32) NOT NULL AUTO_INCREMENT,
  `ParentId` bigint(32) DEFAULT NULL,
  PRIMARY KEY (`OrganisationId`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `dp_organisation_member` (
  `OrganisationId` bigint(32) NOT NULL,
  `UserId` bigint(32) NOT NULL,
  PRIMARY KEY (`OrganisationId`,`UserId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `dp_organisation` VALUES (1,NULL),(2,NULL),(3,2),(115,1),(131,1),(153,115);

INSERT INTO `dp_organisation_member` VALUES (1,1),(2,2),(3,3),(115,4),(131,5),(153,6);

Compare the results of the two queries below. They are the same in MySQL 5.5.51 and 5.6.35, but not in MySQL 5.7.17.

SELECT *
FROM dp_organisation_member t82
WHERE (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level + '1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
) AND (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level+'1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
)

SELECT *
FROM dp_organisation_member t82
WHERE (
   t82.`OrganisationId` = '1' OR FIND_IN_SET(t82.`OrganisationId`, (
      SELECT GROUP_CONCAT(`Ids`)
      FROM (
         SELECT @Level := @Level + '1' `Level`, @Ids := (
            SELECT GROUP_CONCAT(`OrganisationId`)
            FROM dp_organisation
            WHERE FIND_IN_SET(`ParentId`, @Ids)
         ) `Ids`
         FROM (SELECT @Ids := '1', @Level := '0') temp1
         INNER JOIN dp_organisation ON NOT(ISNULL(@Ids))
      ) temp2
   ))
)
[7 Aug 2017 12:40] Magnar Myrtveit
Here is a SQL Fiddle using MySQL 5.6, giving the correct result: http://sqlfiddle.com/#!9/9aa8bd/1

Unfortunately, SQL Fiddle does not support MySQL 5.7, which gives an incorrect result for the same query.
[7 Aug 2017 12:44] Magnar Myrtveit
Changed severity to S2, since there is a workaround (just don't have any duplicated conditions).
[7 Aug 2017 17:12] MySQL Verification Team
Thank you for the bug report. Verified as described.
[30 Oct 2018 8:12] Magnar Myrtveit
The issue is also present in MySQL 8.0. DB Fiddle supports MySQL 5.7 and 8.0, so now you can see that the same query yields different results in 5.7/8.0 than in 5.5/5.6: https://www.db-fiddle.com/f/5SAJZJosQXMZsDmU1Lmi8X/0