Bug #107994 Query optimiser change between 8.0.21 and 8.0.23 changes results
Submitted: 27 Jul 5:45 Modified: 2 Aug 0:24
Reporter: andrew lorien Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23, 8.0.26, 8.0.27, 8.0.28 OS:Any (Amazon RDS)
Assigned to: CPU Architecture:Any

[27 Jul 5:45] andrew lorien
Description:
Between version 8.0.21 and 8.0.23 many of our complex queries (generated by dotnet entity framework) started returning different results.  Running EXPLAIN on the queries shows that the optimiser is interpreting the query differently.  I don't have a simple case yet, but the example below recreates some of our tables and finishes with two queries which return identical results in 8.0.21 but different results in 8.0.23.  Running EXPLAIN on them shows that the one which returns a different result is optimised differently.

How to repeat:

-- SON, Contract, Agency, ProcurementMethod, Category, SONSupplier, Supplier

CREATE TABLE `SON` (
  `SONUUID` char(36) NOT NULL,
  `ContractUUID` char(36) DEFAULT NULL,
  `ParentSONUUID` char(36) DEFAULT NULL,
  `ContactName` varchar(255) DEFAULT NULL,
  `ContactPhone` varchar(30) DEFAULT NULL,
  `ContactEmail` varchar(255) DEFAULT NULL,
  `Division` varchar(255) DEFAULT NULL,
  `Branch` varchar(255) DEFAULT NULL,
  `AgencyPostcode` varchar(255) DEFAULT NULL,
  `AgencyRefID` varchar(255) DEFAULT NULL,
  `ConfidentialityContractFlag` tinyint DEFAULT NULL,
  `ProcurementCode` char(3) DEFAULT NULL,
  `AllAgenciesParticipate` tinyint DEFAULT '0',
  `MultiAgencyAccess` tinyint DEFAULT NULL,
  `MAAType` varchar(20) DEFAULT NULL,
  `PanelArrangement` tinyint DEFAULT NULL,
  `CancellationNotifyPanelOperators` tinyint(1) DEFAULT NULL,
  `CancellationNotifySuppliers` tinyint(1) DEFAULT NULL,
  `CancellationNotifyRFXManagers` tinyint(1) DEFAULT NULL,
  `SONClosedPanelAssociated` tinyint(1) DEFAULT '0',
  `SONOpenPanelAssociated` tinyint(1) DEFAULT '0',
  `lastModifiedDBTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SONUUID`),
  UNIQUE KEY `ContractUUID` (`ContractUUID`),
  KEY `ParentSONUUID` (`ParentSONUUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE TABLE `Contract` (
  `ContractUUID` char(36) NOT NULL,
  `ID` int NOT NULL AUTO_INCREMENT,
  `Type` char(3) NOT NULL DEFAULT '',
  `AgencyUUID` char(36) DEFAULT NULL,
  `UserUUID` char(36) DEFAULT NULL,
  `UNSPSCCode` varchar(8) DEFAULT NULL,
  `Published` datetime DEFAULT NULL,
  `StartDate` date DEFAULT '0000-00-00',
  `EndDate` date DEFAULT '0000-00-00',
  `Value` decimal(16,2) DEFAULT NULL,
  `Description` text,
  `Status` varchar(255) NOT NULL DEFAULT '0',
  `CancellationReason` text,
  `ConsultancyFlag` tinyint(1) DEFAULT NULL,
  `UploadUUID` char(36) DEFAULT NULL,
  `TenderNumber` varchar(255) DEFAULT NULL,
  `CancellationDate` date DEFAULT NULL,
  `CancellationUserUUID` char(36) DEFAULT NULL,
  `lastUpdated` datetime DEFAULT NULL,
  `AgencyType` varchar(10) DEFAULT NULL,
  `AmendmentDate` datetime DEFAULT NULL,
  `InternalContractReference` varchar(255) DEFAULT '',
  `ContractAmendmentValue` decimal(16,2) DEFAULT NULL,
  `AmendmentValue` decimal(16,2) DEFAULT NULL,
  `AmendmentStartDate` date DEFAULT NULL,
  `Title` varchar(75) DEFAULT NULL,
  `UNSPSCCodeAdditional` varchar(8) DEFAULT NULL,
  `LTExempt` tinyint(1) DEFAULT NULL,
  `ExemptionCode` varchar(2) DEFAULT NULL,
  `LTReasonCode` varchar(2) DEFAULT NULL,
  `lastModifiedDBTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `applicablePublishDate_generated` date GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (ifnull(cast(`AmendmentDate` as date),_utf8mb4'') = _utf8mb4'') then cast(`Published` as date) else cast(`AmendmentDate` as date) end)) VIRTUAL,
  `applicableStartDate_generated` date GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (ifnull(cast(`AmendmentDate` as date),_utf8mb4'') = _utf8mb4'') then `StartDate` else `AmendmentStartDate` end)) VIRTUAL,
  `FY_generated` int GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (ifnull(cast(`AmendmentDate` as date),_utf8mb4'') = _utf8mb4'') then year((cast(`Published` as date) + interval 6 month)) else year((cast(`AmendmentDate` as date) + interval 6 month)) end)) VIRTUAL,
  `applicableValue_generated` decimal(16,2) GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (ifnull(cast(`AmendmentDate` as date),_utf8mb4'') = _utf8mb4'') then `Value` else `AmendmentValue` end)) VIRTUAL,
  `valueRange_generated` char(20) GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (`applicableValue_generated` is null) then NULL when (`applicableValue_generated` < 80000) then _utf8mb4'Below $80k' when (`applicableValue_generated` < 200000) then _utf8mb4'$80k to <$200k' when (`applicableValue_generated` < 1000000) then _utf8mb4'$200k to <$1m' when (`applicableValue_generated` < 4000000) then _utf8mb4'$1m to <$4m' when (`applicableValue_generated` < 20000000) then _utf8mb4'$4m to < $20m' else _utf8mb4'Above $20m' end)) VIRTUAL,
  `daysPublished_generated` int GENERATED ALWAYS AS ((case when (`Status` <> _latin1'published') then NULL when (ifnull(cast(`AmendmentDate` as date),_utf8mb4'') = _utf8mb4'') then (to_days(cast(`Published` as date)) - to_days(`StartDate`)) else (to_days(cast(`AmendmentDate` as date)) - to_days(`AmendmentStartDate`)) end)) VIRTUAL,
  PRIMARY KEY (`ContractUUID`),
  UNIQUE KEY `id` (`ID`),
  KEY `UploadUUID` (`UploadUUID`),
  KEY `Status` (`Status`),
  KEY `Published` (`Published`),
  KEY `StartDate` (`StartDate`),
  KEY `EndDate` (`EndDate`),
  KEY `AgencyUUID` (`AgencyUUID`),
  KEY `UserUUID` (`UserUUID`),
  KEY `UNSPSCCode` (`UNSPSCCode`),
  KEY `CancellationUserUUID` (`CancellationUserUUID`),
  KEY `TenderNumber` (`TenderNumber`),
  KEY `ConsultancyFlag` (`ConsultancyFlag`),
  KEY `PublishedDesc` (`Published`),
  KEY `IX_ContractUUID_Type_Status` (`ContractUUID`,`Type`,`Status`)
) ENGINE=InnoDB AUTO_INCREMENT=3870890 DEFAULT CHARSET=latin1;
;
CREATE TABLE `Agency` (
  `AgencyUUID` char(36) NOT NULL,
  `ParentAgencyUUID` char(36) DEFAULT NULL,
  `TreeNode` int DEFAULT '0',
  `Name` varchar(255) NOT NULL DEFAULT '',
  `Email` varchar(255) NOT NULL DEFAULT '',
  `PortfolioUUID` char(36) DEFAULT NULL,
  `ABN` varchar(25) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `StateUUID` varchar(36) DEFAULT NULL,
  `Postcode` varchar(4) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `DefaultLateHours` int DEFAULT NULL,
  `Suspended` tinyint(1) DEFAULT '0',
  `Elodgement` int DEFAULT '0',
  `HardCopyOrder` int DEFAULT '0',
  `ExpectedATMIssueDateEmail` int DEFAULT '0',
  `AgencyType` varchar(20) NOT NULL DEFAULT '',
  `HideFromPublic` tinyint DEFAULT '0',
  `AllowCNAmendmentViaBatch` tinyint DEFAULT '0',
  `AREXVersionLastUsed` varchar(36) DEFAULT '',
  `AREXVersionUUID` char(36) DEFAULT NULL,
  `AREXVersionList` varchar(100) DEFAULT NULL,
  `RFxPublicKey` text,
  `RFxPrivateKey` text,
  `RFxPassphraseHash` varchar(255) DEFAULT NULL,
  `RFxPassphraseChangedAt` datetime DEFAULT NULL,
  `RFxPassphraseChangedBy` varchar(255) DEFAULT NULL,
  `EnablePanelBuyerRequest` tinyint(1) DEFAULT '0',
  `RFxMasterPrivateKey` text,
  `MasterAESKey` text,
  `RFxMasterPublicKey` text,
  `lastModifiedDBTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`AgencyUUID`),
  KEY `ParentAgencyUUID` (`ParentAgencyUUID`),
  KEY `PortfolioUUID` (`PortfolioUUID`),
  KEY `TreeNode` (`TreeNode`),
  KEY `StateUUID` (`StateUUID`),
  KEY `AgencyType` (`AgencyType`),
  KEY `Suspended` (`Suspended`),
  KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE TABLE `ProcurementMethod` (
  `ProcurementCode` char(3) NOT NULL DEFAULT '',
  `ProcurementMethod` varchar(255) DEFAULT NULL,
  `SortOrder` int unsigned DEFAULT NULL,
  `ProcurementCodeV1` char(3) DEFAULT NULL COMMENT 'pre-1.4.2 codes',
  `Valid` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`ProcurementCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE TABLE `Category` (
  `Code` varchar(50) NOT NULL DEFAULT '',
  `Title` varchar(255) NOT NULL DEFAULT '',
  `valid` tinyint(1) DEFAULT '0',
  `type` varchar(8) DEFAULT NULL,
  `lastModifiedDBTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE TABLE `SONSupplier` (
  `SONSupplierUUID` char(36) NOT NULL,
  `SupplierUUID` char(36) DEFAULT NULL,
  `SONUUID` char(36) DEFAULT NULL,
  `Name` varchar(255) NOT NULL DEFAULT '',
  `ABNExempt` tinyint NOT NULL DEFAULT '0',
  `Address` varchar(255) DEFAULT NULL,
  `Suburb` varchar(255) NOT NULL DEFAULT '',
  `Postcode` varchar(255) DEFAULT NULL,
  `State` varchar(255) NOT NULL DEFAULT '',
  `Country` varchar(255) NOT NULL DEFAULT '',
  `ParentSONSupplierUUID` char(36) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  `SupplierStatus` varchar(10) DEFAULT NULL,
  `DSDInherited` tinyint(1) DEFAULT NULL,
  `DEDInherited` tinyint(1) DEFAULT NULL,
  `DeedStartDate` date DEFAULT NULL,
  `DeedEndDate` date DEFAULT NULL,
  `DeletedDate` date DEFAULT NULL,
  `lastModifiedDBTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SONSupplierUUID`),
  KEY `SONUUID` (`SONUUID`),
  KEY `SupplierUUID` (`SupplierUUID`),
  KEY `idx_SONSupplier_SONUUID_Status` (`SONUUID`,`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE TABLE `Supplier` (
  `SupplierUUID` char(36) NOT NULL,
  `Name` varchar(255) NOT NULL DEFAULT '',
  `ABN` varchar(36) NOT NULL DEFAULT '',
  `SMEStatus` tinyint DEFAULT NULL,
  `lastModifiedDBTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SupplierUUID`),
  UNIQUE KEY `ABN` (`ABN`),
  KEY `SMEStatus` (`SMEStatus`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
CREATE OR REPLACE VIEW `vwSONAdminListing` AS
SELECT	s.SONUUID, s.ParentSONUUID, c.AgencyUUID, a.name AS Agencyname, c.ContractUUID, c.ID AS ContractID,
c.Title AS SONTitle, c.`Value`, c.Description, c.LTExempt, c.ExemptionCode, c.LTReasonCode, s.PanelArrangement,
c.Published, c.StartDate, c.EndDate, c.`Status`,
c.UserUUID,
c.UNSPSCCode, c.UNSPSCCodeAdditional, cat.title as CategoryTitle,  pm.ProcurementMethod, -- s.Code AS ProcurementCode
CONCAT ('SON',c.ID, ' ', IFNULL(s.AgencyRefID, ''), ' ' , IFNULL(c.Title,''), ' ', IFNULL(c.Description,'') , ' ' , IFNULL(su.ABN,''), ' ' , IFNULL(ss.`Name`,'')) AS SearchField
FROM SON s 
INNER JOIN Contract c on s.ContractUUID = c.ContractUUID
INNER JOIN Agency a ON c.AgencyUUID = a.AgencyUUID
LEFT JOIN ProcurementMethod pm on s.ProcurementCode = pm.ProcurementCode
LEFT JOIN Category cat ON c.UnspscCode = cat.code
LEFT JOIN SONSupplier ss ON s.SONUUID= ss.SONUUID 
LEFT JOIN Supplier su ON ss.SupplierUUID = su.SupplierUUID
LEFT JOIN SONSupplier pss ON ss.ParentSonSupplierUuid = pss.SONSupplierUUID;
;
-------------------------------------------------------------------------------------------------
-- the bug:
-- These two queries return the same result in 8.0.21 but different results in 8.0.23, 27, 28

-- THIS QUERY IS OPTIMISED DIFFERENTLY AND RETURNS DIFFERENT RESULTS IN 8.0.21 FROM >=8.0.23
SELECT 
  `Extent1`.`contractid`, 
  `Extent1`.`sonuuid`, 
  `Extent1`.`parentsonuuid`, 
  `Extent1`.`procurementmethod`, 
  `Extent1`.`status`, 
  Concat('SON', `Extent1`.`contractid`) AS `C1`, 
  `Extent1`.`published`, 
  `Extent1`.`startdate`, 
  `Extent1`.`enddate`, 
  `Extent1`.`sontitle`, 
  `Extent1`.`categorytitle`, 
  `Extent1`.`unspsccode`, 
  `Extent1`.`agencyname`, 
  `Extent1`.`agencyuuid`, 
  `Extent1`.`contractuuid`, 
  `Extent1`.`ltexempt`, 
  `Extent1`.`ltreasoncode`, 
  `Extent1`.`exemptioncode` 
FROM 
  `vwSONAdminListing` AS `Extent1` 
WHERE 
  (
    (
      (
        (
          0 = (
            CASE WHEN (
              `Extent1`.`parentsonuuid` IS NOT NULL
            ) THEN (1) ELSE (0) end
          )
        ) 
        AND (
          (1 = 1) 
          OR (
            `Extent1`.`searchfield` LIKE NULL
          )
        )
      ) 
      AND (
        (
          CASE WHEN (1 != 1) THEN (
            CASE WHEN (0 = 1) THEN (
              CASE WHEN (
                (
                  'cd4b9201-e299-e0d3-1b09-c3f42790e52e' IS NOT NULL
                ) 
                AND (
                  `Extent1`.`agencyuuid` = 'cd4b9201-e299-e0d3-1b09-c3f42790e52e'
                )
              ) THEN (1) WHEN (
                NOT (
                  (
                    'cd4b9201-e299-e0d3-1b09-c3f42790e52e' IS NOT NULL
                  ) 
                  AND (
                    `Extent1`.`agencyuuid` = 'cd4b9201-e299-e0d3-1b09-c3f42790e52e'
                  )
                )
              ) THEN (0) end
            ) WHEN (
              (
                'add5d766-c32a-46eb-8d43-d40f0ecd51b8' IS NOT NULL
              ) 
              AND (
                `Extent1`.`useruuid` = 'add5d766-c32a-46eb-8d43-d40f0ecd51b8'
              )
            ) THEN (1) WHEN (
              NOT (
                (
                  'add5d766-c32a-46eb-8d43-d40f0ecd51b8' IS NOT NULL
                ) 
                AND (
                  `Extent1`.`useruuid` = 'add5d766-c32a-46eb-8d43-d40f0ecd51b8'
                )
              )
            ) THEN (0) end
          ) WHEN (
            NOT EXISTS(
              SELECT 
                1 AS `C1` 
              FROM 
                (
                  SELECT 
                    1 AS `X`
                ) AS `SingleRowTable1` 
              WHERE 
                1 = 0
            )
          ) THEN (1) WHEN (
            EXISTS(
              SELECT 
                1 AS `C1` 
              FROM 
                (
                  SELECT 
                    1 AS `X`
                ) AS `SingleRowTable2` 
              WHERE 
                1 = 0
            )
          ) THEN (0) end
        ) = 1
      )
    ) 
    AND (
      (
        (
          (
            ('Draft' LIKE '%draft%') 
            AND (
              (`Extent1`.`status` = 'draft') 
              OR (
                `Extent1`.`status` = 'incomplete'
              )
            )
          ) 
          OR (
            ('Draft' LIKE '%cancelled%') 
            AND (`Extent1`.`status` = 'cancelled')
          )
        ) 
        OR (
          (
            ('Draft' LIKE '%current%') 
            AND (`Extent1`.`status` = 'published')
          ) 
          AND (
            `Extent1`.`enddate` >= Timestamp('2022-07-24 12:50:27.820243')
          )
        )
      ) 
      OR (
        (
          ('Draft' LIKE '%closed%') 
          AND (`Extent1`.`status` = 'published')
        ) 
        AND (
          `Extent1`.`enddate` < Timestamp('2022-07-24 12:50:27.820243')
        )
      )
    )
  ) 
  -- these lines moved from the middle of the query to the end
  AND (
    (0 != 1) 
    OR (
      (`Extent1`.`panelarrangement`) = NULL
    )
  )
  --
  ;

              
-- THIS QUERY IS OPTIMISED THE SAME WAY IN 8.0.21 AND >=8.0.23
SELECT 
  `Extent1`.`contractid`, 
  `Extent1`.`sonuuid`, 
  `Extent1`.`parentsonuuid`, 
  `Extent1`.`procurementmethod`, 
  `Extent1`.`status`, 
  Concat('SON', `Extent1`.`contractid`) AS `C1`, 
  `Extent1`.`published`, 
  `Extent1`.`startdate`, 
  `Extent1`.`enddate`, 
  `Extent1`.`sontitle`, 
  `Extent1`.`categorytitle`, 
  `Extent1`.`unspsccode`, 
  `Extent1`.`agencyname`, 
  `Extent1`.`agencyuuid`, 
  `Extent1`.`contractuuid`, 
  `Extent1`.`ltexempt`, 
  `Extent1`.`ltreasoncode`, 
  `Extent1`.`exemptioncode` 
FROM 
  `vwSONAdminListing` AS `Extent1` 
WHERE 
  (
    (
      (
        (
          0 = (
            CASE WHEN (
              `Extent1`.`parentsonuuid` IS NOT NULL
            ) THEN (1) ELSE (0) end
          )
        ) 
        AND (
          (1 = 1) 
          OR (
            `Extent1`.`searchfield` LIKE NULL
          )
        )
      ) 
	  -- these lines moved from the end of the query to here
      AND (
        (0 != 1) 
        OR (
          (`Extent1`.`panelarrangement`) = NULL
        )
      ) 
	  -- 
      AND (
        (
          CASE WHEN (1 != 1) THEN (
            CASE WHEN (0 = 1) THEN (
              CASE WHEN (
                (
                  'cd4b9201-e299-e0d3-1b09-c3f42790e52e' IS NOT NULL
                ) 
                AND (
                  `Extent1`.`agencyuuid` = 'cd4b9201-e299-e0d3-1b09-c3f42790e52e'
                )
              ) THEN (1) WHEN (
                NOT (
                  (
                    'cd4b9201-e299-e0d3-1b09-c3f42790e52e' IS NOT NULL
                  ) 
                  AND (
                    `Extent1`.`agencyuuid` = 'cd4b9201-e299-e0d3-1b09-c3f42790e52e'
                  )
                )
              ) THEN (0) end
            ) WHEN (
              (
                'add5d766-c32a-46eb-8d43-d40f0ecd51b8' IS NOT NULL
              ) 
              AND (
                `Extent1`.`useruuid` = 'add5d766-c32a-46eb-8d43-d40f0ecd51b8'
              )
            ) THEN (1) WHEN (
              NOT (
                (
                  'add5d766-c32a-46eb-8d43-d40f0ecd51b8' IS NOT NULL
                ) 
                AND (
                  `Extent1`.`useruuid` = 'add5d766-c32a-46eb-8d43-d40f0ecd51b8'
                )
              )
            ) THEN (0) end
          ) WHEN (
            NOT EXISTS(
              SELECT 
                1 AS `C1` 
              FROM 
                (
                  SELECT 
                    1 AS `X`
                ) AS `SingleRowTable1` 
              WHERE 
                1 = 0
            )
          ) THEN (1) WHEN (
            EXISTS(
              SELECT 
                1 AS `C1` 
              FROM 
                (
                  SELECT 
                    1 AS `X`
                ) AS `SingleRowTable2` 
              WHERE 
                1 = 0
            )
          ) THEN (0) end
        ) = 1
      )
    ) 
    AND (
      (
        (
          (
            ('Draft' LIKE '%draft%') 
            AND (
              (`Extent1`.`status` = 'draft') 
              OR (
                `Extent1`.`status` = 'incomplete'
              )
            )
          ) 
          OR (
            ('Draft' LIKE '%cancelled%') 
            AND (`Extent1`.`status` = 'cancelled')
          )
        ) 
        OR (
          (
            ('Draft' LIKE '%current%') 
            AND (`Extent1`.`status` = 'published')
          ) 
          AND (
            `Extent1`.`enddate` >= Timestamp('2022-07-24 12:50:27.820243')
          )
        )
      ) 
      OR (
        (
          ('Draft' LIKE '%closed%') 
          AND (`Extent1`.`status` = 'published')
        ) 
        AND (
          `Extent1`.`enddate` < Timestamp('2022-07-24 12:50:27.820243')
        )
      )
    )
  );
[27 Jul 7:37] MySQL Verification Team
Hello Andrew lorien,

Thank you for the report and feedback.
Internal discussion confirmed that this issue has been fixed in 8.0.30 which is released recently. I request you to try and confirm on MySQL Server 8.0.30 but if you are still seeing the issue then request you to provide subset of the data(logical dump would be fine) to confirm the issue at our end. Thank you.

regards,
Umesh
[2 Aug 0:24] andrew lorien
Thank you
We've confirmed that this is fixed in 8.0.30
Closing this ticket.