Score_to_hide_post becomes 0 when reviewables each have only one flag

Bug
1

Summary

score_to_hide_post can be driven to 0 because the background job Jobs::ReviewablePriorities writes priority_#{priorities[:high]} as 0 when the percentile query returns no rows. The job is triggered when reviewable_count >= 15, but the percentile calculation only includes reviewables that meet HAVING COUNT(*) >= :target_count (where target_count is typically 2). If most reviewables have only one flag, the percentile subquery returns nothing → high becomes 0 → score_to_hide_post = ((high * ratio) * scale).truncate(2) evaluates to 0. This causes the hide threshold to collapse to zero and produces incorrect hiding behavior.

Where this comes from (relevant code / facts)

  • score_to_hide_post is computed via:
score_to_hide_post = ((high.to_f * ratio) * scale).truncate(2)
  • high is read from plugin store:
PluginStore.get("reviewables", "priority_#{priorities[:high]}")
  • That plugin store entry is written by Jobs::ReviewablePriorities (the system job).
    The job runs when:
reviewable_count = Reviewable.approved.where("score > ?", min_priority_threshold).count
return if reviewable_count < self.class.min_reviewables

where self.class.min_reviewables is 15.

  • The job computes high using SQL:
SELECT COALESCE(PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score), 0.0) AS medium,
       COALESCE(PERCENTILE_DISC(0.85) WITHIN GROUP (ORDER BY score), 0.0) AS high
FROM (
  SELECT r.score
  FROM reviewables AS r
  INNER JOIN reviewable_scores AS rs ON rs.reviewable_id = r.id
  WHERE r.score > :min_priority AND r.status = 1
  GROUP BY r.id
  HAVING COUNT(*) >= :target_count
) AS x

with :target_count usually 2.

Root cause

There are two separate thresholds that together create a gap:

  1. The job is triggered when there are at least min_reviewables (15) reviewables above min_priority_threshold — this is a coarse count ignoring the :target_count requirement.
  2. But the percentile calculation that produces high only includes reviewables that have COUNT(*) >= :target_count (i.e., at least 2 reviewable_scores). If many reviewables each have only one flag, the subquery yields no rows and the percentile returns the fallback 0.0.

So the job can run (because there are ≥ 15 reviewables by the coarse count) but the percentile aggregation has no qualifying rows (because none meet the HAVING), causing high to be 0 and then priority_high to be written as 0. That feeds into score_to_hide_post and collapses it.

Impact

  • score_to_hide_post becomes 0, which may incorrectly cause posts to be considered hidden or otherwise break logic that depends on a reasonable hide threshold.
  • This occurs on sites where many reviewables exist but each only has a single flag/reviewer, which is not uncommon on smaller/moderate communities.

Suggested fixes (options)

  1. Ensure the percentile query returns enough rows before writing
  • After running the percentile query, check whether the percentile value is 0 and whether the subquery returned any rows.
    If no rows were returned, do not overwrite the existing priority_high; instead, skip writing, keep the previous value, or fall back to a configured default.
  • This is the safest and least invasive approach.
  1. Adjust the job trigger to account for target_count
  • Modify the job pre-check so that it only runs when the number of reviewables meeting HAVING COUNT(*) >= :target_count is at least min_reviewables.
    In other words, count reviewables grouped by id that satisfy COUNT(*) >= target_count and only proceed if that count ≥ min_reviewables.
  1. Allow administrators to manually set score_to_hide_post or priority_high
  • Provide an option in the admin interface to directly input or adjust score_to_hide_post or priority_high.
  • This way, even if the percentile query produces unexpected results (e.g., due to too few samples), the system can use a reasonable threshold specified by the administrator, preventing errors caused by automatic calculations.