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