Details
Critical Description
We tried upgrading current 10.4.22 to the next version and discovered that an existing VIEW used in JOIN takes more than 45mins to return the results. But in 10.4.22 we're getting the results within 8 seconds for 25000 records. We discovered the same behavior with recent 10.4.31 and 10.11.5 too.
Below is the VIEW we're using:
CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS |
SELECT
|
H1.`id` AS `historyId`, H1.`uId` AS `uId`, H1.`jobTitleId` AS `jobTitleId`, H1.`joinedDate` AS `joinedDate`, |
H1.`eDate` AS `eDate`, H1.`eventId` AS `eventId`, HE.`name` AS `eventName` |
FROM ( |
(`History` H1
|
LEFT JOIN `HistoryEvent` HE ON (HE.`id` = H1.`eventId`) |
) JOIN ( |
SELECT SH1.`uId` AS `uId`, MAX(SH1.`id`) AS `id` |
FROM ( |
(
|
SELECT `id`, `uId`, IFNULL(`eDate`, '1970-01-01') AS `eDate` |
FROM `History` |
) SH1
|
JOIN ( |
SELECT TMP.`uId` AS `uId`, MAX(TMP.`eDate`) AS `eDate` |
FROM ( |
SELECT `uId`, IFNULL(`eDate`, '1970-01-01') AS `eDate` |
FROM `History` |
) TMP
|
WHERE TMP.`eDate` <= curdate() |
GROUP BY TMP.`uId` |
) SH2 ON (SH1.`uId` = SH2.`uId` and SH1.`eDate` = SH2.`eDate`) |
)
|
GROUP BY SH1.`uId` |
) H2 ON (H1.`uId` = H2.`uId` and H1.`id` = H2.`id`) |
);
|
This has become a blocker for our application.
Attachments
Issue Links
- is caused by
-
MDEV-27510 Query returns wrong result when using split optimization
-
- Closed
-