Score_to_hide_post becomes 0 when reviewables each have only one flag

Contribute Bug

AI-generated summary

The issue arises when score_to_hide_post drops to 0 due to the Jobs::ReviewablePriorities job setting priority_high to 0 when the percentile query returns no results. This happens when reviewables have only one flag, causing the HAVING COUNT(*) >= 2 condition to exclude all entries, resulting in no data for the percentile calculation. Since the job triggers only when reviewable_count >= 15, but the percentile subquery returns nothing, high becomes 0.0, leading to score_to_hide_post = 0. This breaks the hiding mechanism, as the threshold collapses to zero. The root cause is a mismatch between the job’s trigger condition and the data filtering logic, which prevents proper score computation when most reviewables have minimal flags. copymonopoly explains the SQL and logic flow, highlighting the need for a fix to ensure scores are computed correctly even with low-flag reviewables.

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.