Repository: openaddresses/machine
Pull request #774 (2020-08-25 02:05:06)

Revert the slow query change
Reverts #756


Patch:
diff --git a/openaddr/ci/objects.py b/openaddr/ci/objects.py
index e6a1693..efee9aa 100644
--- a/openaddr/ci/objects.py
+++ b/openaddr/ci/objects.py
@@ -518,10 +518,13 @@ def read_completed_runs_to_date(db, starting_set_id):
         return None
 
     # Get IDs for latest successful source runs of any run in the requested set.
-    db.execute('''SELECT MAX(id), source_path
-                  FROM runs
-                  WHERE
-                    set_id = %s
+    db.execute('''SELECT MAX(id), source_path FROM runs
+                  WHERE source_path IN (
+                      -- Get all source paths for successful runs in this set.
+                      SELECT source_path FROM runs
+                      WHERE set_id = %s
+                    )
+                    -- Get only successful, merged runs.
                     AND status = true
                     AND (is_merged = true OR is_merged IS NULL)
                   GROUP BY source_path''',
@@ -530,10 +533,13 @@ def read_completed_runs_to_date(db, starting_set_id):
     run_path_ids = {path: run_id for (run_id, path) in db.fetchall()}
 
     # Get IDs for latest unsuccessful source runs of any run in the requested set.
-    db.execute('''SELECT MAX(id), source_path
-                  FROM runs
-                  WHERE
-                    set_id = %s
+    db.execute('''SELECT MAX(id), source_path FROM runs
+                  WHERE source_path IN (
+                      -- Get all source paths for failed runs in this set.
+                      SELECT source_path FROM runs
+                      WHERE set_id = %s
+                    )
+                    -- Get only unsuccessful, merged runs.
                     AND status = false
                     AND (is_merged = true OR is_merged IS NULL)
                   GROUP BY source_path''',


Test patch:
diff --git a/openaddr/tests/ci.py b/openaddr/tests/ci.py
index 65bffba..16dfc10 100644
--- a/openaddr/tests/ci.py
+++ b/openaddr/tests/ci.py
@@ -739,19 +739,25 @@ class TestObjects (unittest.TestCase):
             = exec1[1][:], exec2[1][:], exec3[1][:]
 
         self.assertEqual(e1_query,
-               '''SELECT MAX(id), source_path
-                  FROM runs
-                  WHERE
-                    set_id = %s
+               '''SELECT MAX(id), source_path FROM runs
+                  WHERE source_path IN (
+                      -- Get all source paths for successful runs in this set.
+                      SELECT source_path FROM runs
+                      WHERE set_id = %s
+                    )
+                    -- Get only successful, merged runs.
                     AND status = true
                     AND (is_merged = true OR is_merged IS NULL)
                   GROUP BY source_path''')
 
         self.assertEqual(e2_query,
-               '''SELECT MAX(id), source_path
-                  FROM runs
-                  WHERE
-                    set_id = %s
+               '''SELECT MAX(id), source_path FROM runs
+                  WHERE source_path IN (
+                      -- Get all source paths for failed runs in this set.
+                      SELECT source_path FROM runs
+                      WHERE set_id = %s
+                    )
+                    -- Get only unsuccessful, merged runs.
                     AND status = false
                     AND (is_merged = true OR is_merged IS NULL)
                   GROUP BY source_path''')