fix: bug in SQL

evgeniy-scherbina · evgeniy-scherbina · commit d7b4ec41dc71 · 2025-03-21T09:52:14.000-04:00
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
@@ -3986,6 +3986,59 @@ func TestGetPresetsBackoff(t *testing.T) {
 			require.Equal(t, int32(2), backoff.NumFailed)
 		}
 	})
+
+	t.Run("check LastBuildAt timestamp", func(t *testing.T) {
+		t.Parallel()
+
+		db, _ := dbtestutil.NewDB(t)
+		ctx := testutil.Context(t, testutil.WaitShort)
+		dbgen.Organization(t, db, database.Organization{
+			ID: orgID,
+		})
+		dbgen.User(t, db, database.User{
+			ID: userID,
+		})
+		lookbackPeriod := time.Hour
+
+		tmpl1 := createTemplate(db)
+		tmpl1V1 := createTmplVersion(db, tmpl1, tmpl1.ActiveVersionID, &tmplVersionOpts{
+			DesiredInstances: 5,
+		})
+		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
+			successfulJob: false,
+			createdAt:     now.Add(-4 * time.Minute),
+		})
+		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
+			successfulJob: false,
+			createdAt:     now.Add(-0 * time.Minute),
+		})
+		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
+			successfulJob: false,
+			createdAt:     now.Add(-3 * time.Minute),
+		})
+		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
+			successfulJob: false,
+			createdAt:     now.Add(-1 * time.Minute),
+		})
+		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
+			successfulJob: false,
+			createdAt:     now.Add(-2 * time.Minute),
+		})
+
+		backoffs, err := db.GetPresetsBackoff(ctx, now.Add(-lookbackPeriod))
+		require.NoError(t, err)
+
+		require.Len(t, backoffs, 1)
+		{
+			backoff := backoffs[0]
+			require.Equal(t, backoff.TemplateVersionID, tmpl1.ActiveVersionID)
+			require.Equal(t, backoff.PresetID, tmpl1V1.preset.ID)
+			require.Equal(t, database.ProvisionerJobStatusFailed, backoff.LatestBuildStatus)
+			require.Equal(t, int32(5), backoff.NumFailed)
+			// make sure LastBuildAt is equal to latest failed build timestamp
+			require.Equal(t, 0, now.Compare(backoff.LastBuildAt.(time.Time)))
+		}
+	})
 }
 
 func requireUsersMatch(t testing.TB, expected []database.User, found []database.GetUsersRow, msg string) {
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/prebuilds.sql b/coderd/database/queries/prebuilds.sql
@@ -88,12 +88,12 @@ SELECT tsb.template_version_id,
 	   tsb.preset_id,
 	   tsb.job_status::provisioner_job_status AS latest_build_status,
 	   COALESCE(fc.num_failed, 0)::int        AS num_failed,
-	   tsb.created_at::timestamptz            AS last_build_at
+	   MAX(tsb.created_at::timestamptz)       AS last_build_at
 FROM time_sorted_builds tsb
 		 LEFT JOIN failed_count fc ON fc.preset_id = tsb.preset_id
 WHERE tsb.rn <= tsb.desired_instances -- Fetch the last N builds, where N is the number of desired instances; if any fail, we backoff
   AND tsb.job_status = 'failed'::provisioner_job_status
-GROUP BY tsb.template_version_id, tsb.preset_id, tsb.job_status, tsb.created_at, fc.num_failed;
+GROUP BY tsb.template_version_id, tsb.preset_id, tsb.job_status, fc.num_failed;
 
 -- name: ClaimPrebuild :one
 UPDATE workspaces w
