Project import generated by Copybara.
GitOrigin-RevId: d9e9e3fb4e31372ec1fb43b178994ca78fa8fe70
diff --git a/tools/backfill-update-duplicate-issue-snapshots.sql b/tools/backfill-update-duplicate-issue-snapshots.sql
new file mode 100644
index 0000000..227a7fa
--- /dev/null
+++ b/tools/backfill-update-duplicate-issue-snapshots.sql
@@ -0,0 +1,52 @@
+-- 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
+-- https://developers.google.com/open-source/licenses/bsd
+
+-- 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: crbug.com/monorail/6020
+
+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 ToUpdate.id, IssueSnapshot.issue_id, IssueSnapshot.period_start, IssueSnapshot.period_end
+ FROM IssueSnapshot INNER JOIN (
+ -- Get correct period_end to update.
+ SELECT NeedsUpdate.id, IssueSnapshot.issue_id, IssueSnapshot.period_start, MIN(IssueSnapshot.period_end)
+ AS update_time FROM IssueSnapshot
+ INNER JOIN (
+ -- Get duplicate rows by filtering out the correct rows.
+ SELECT id, issue_id, period_start, period_end FROM IssueSnapshot
+ WHERE period_end = 4294967295
+ AND id NOT IN (
+ -- Get ids of the correct rows.
+ SELECT id FROM IssueSnapshot
+ INNER JOIN (
+ -- 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 IssueSnapshot.id = IssueSnapshotsToUpdate.id
+SET IssueSnapshot.period_end = CASE WHEN IssueSnapshotsToUpdate.update_time = 4294967295
+ THEN IssueSnapshotsToUpdate.period_start ELSE IssueSnapshotsToUpdate.update_time END;
+
+DROP TABLE IssueSnapshotsToUpdate;