MySQL Bugs: #87339: Duplicating the condition gives a different result set

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

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
   ))
)

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.

Changed severity to S2, since there is a workaround (just don't have any duplicated conditions).

Thank you for the bug report. Verified as described.

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