blob: 227a7fa48838f26e478b1ae09953ebd82db45420 [file] [log] [blame]
-- Copyright 2019 The Chromium Authors. All Rights Reserved.
-- Use of this source code is governed by a BSD-style
-- license that can be found in the LICENSE file or at
-- Update all IssueSnapshot rows that incorrectly have their period_end
-- set to the maximum value 4294967295. For all affected rows, this
-- script update them to the period_end time of the rows with same period_start time;
-- if such rows don't exist, update period_end to be the same as period_start.
-- Bug:
CREATE TABLE IssueSnapshotsToUpdate (id INT, issue_id INT, period_start INT UNSIGNED, update_time INT UNSIGNED);
INSERT INTO IssueSnapshotsToUpdate (id, issue_id, period_start, update_time)
-- Get ids that needs update and append with correct period_end.
(SELECT, IssueSnapshot.issue_id, IssueSnapshot.period_start, IssueSnapshot.period_end
FROM IssueSnapshot INNER JOIN (
-- Get correct period_end to update.
SELECT, IssueSnapshot.issue_id, IssueSnapshot.period_start, MIN(IssueSnapshot.period_end)
AS update_time FROM IssueSnapshot
-- Get duplicate rows by filtering out the correct rows.
SELECT id, issue_id, period_start, period_end FROM IssueSnapshot
WHERE period_end = 4294967295
-- Get ids of the correct rows.
SELECT id FROM IssueSnapshot
-- Get correct rows for each issue_id that should have max period_end.
SELECT issue_id, MAX(period_start) AS maxStart
FROM IssueSnapshot
WHERE period_end = 4294967295
GROUP BY issue_id) AS MaxISTable
ON IssueSnapshot.issue_id = MaxISTable.issue_id
AND IssueSnapshot.period_start = MaxISTable.maxStart)
) AS NeedsUpdate
ON NeedsUpdate.issue_id = IssueSnapshot.issue_id
AND NeedsUpdate.period_start = IssueSnapshot.period_start
GROUP BY NeedsUpdate.issue_id, IssueSnapshot.period_start
) AS ToUpdate
ON IssueSnapshot.issue_id = ToUpdate.issue_id
AND IssueSnapshot.period_start = ToUpdate.period_start
AND IssueSnapshot.period_end = ToUpdate.update_time
UPDATE IssueSnapshot INNER JOIN IssueSnapshotsToUpdate
ON =
SET IssueSnapshot.period_end = CASE WHEN IssueSnapshotsToUpdate.update_time = 4294967295
THEN IssueSnapshotsToUpdate.period_start ELSE IssueSnapshotsToUpdate.update_time END;
DROP TABLE IssueSnapshotsToUpdate;