e7b98d8286de2133b30617a7eb9c78cd355fd21e
braney
  Thu Aug 13 14:32:17 2026 -0700
geneReviews otto: survive a knownGene rebuild, and keep the bigBed in step with the tables, refs #38098

hg19 and hg18 take their gene coordinates from knownGene. The hg19 knownGene
rebuild on 2026-07-15 moved the ends of most genes by a few bases, and the
validation step compared chrom, chromStart, chromEnd and name exactly. That made
1325 of 1939 rows look new, far past the 10 percent ceiling, so the job has
failed every week since July 21 and the tables have been stuck at their June 30
version.

validateGeneReviews.sh now compares the overall span of each gene on each
chromosome and counts a gene as unchanged when the old and new spans overlap.
geneReviewsDetail still compares whole rows exactly. A zero-overlap result now
fails with a message rather than an awk divide-by-zero.

checkGeneReviews.sh validates all three assemblies before deciding, so one bad
assembly no longer hides the state of the others. hg18 had not been checked
since July.

buildGeneReviews.sh pointed /gbdb at the new bigBed before validation ran, so a
failed run left the browser image and the geneReviews tables six weeks apart.
The relink moves to checkGeneReviews.sh, after validation passes and the tables
are installed.

diff --git src/hg/utils/otto/geneReviews/validateGeneReviews.sh src/hg/utils/otto/geneReviews/validateGeneReviews.sh
index 6fbcce1835a..5b060479dcd 100755
--- src/hg/utils/otto/geneReviews/validateGeneReviews.sh
+++ src/hg/utils/otto/geneReviews/validateGeneReviews.sh
@@ -1,36 +1,61 @@
 #!/bin/sh -e
 
 db=$1
 tooMuch=0.1000   # how much change (either gain or loss) is too much
 
+tab=`printf '\t'`
+
+# Write one line per gene per chromosome: "gene|chrom", lowest start, highest end.
+function geneSpans() {
+hgsql -N $db -e \
+    "select name, chrom, min(chromStart), max(chromEnd) from $1 group by name, chrom" \
+    | awk -F'\t' '{OFS="\t"; print $1"|"$2, $3, $4}' | sort -t "$tab" -k1,1
+}
+
 for i in `cat ../geneReviews.tables`
 do
+    f=$i"New"
     if  test $i == "geneReviews"
     then
-    fields='chrom, chromStart, chromEnd, name'
+        # hg19 and hg18 take their coordinates from knownGene, so a knownGene
+        # rebuild moves the ends of most genes by a few bases.  Comparing
+        # coordinates exactly then counts nearly every gene as changed and the
+        # run fails even though the data is fine.  Compare the overall span of
+        # each gene on each chromosome instead, and call a gene unchanged when
+        # its old and new spans overlap.
+        geneSpans $i > $i.out
+        geneSpans $f > $f.out
+        oldCount=`cat $i.out | wc -l`
+        newCount=`cat $f.out | wc -l`
+        # join gives gene|chrom, oldStart, oldEnd, newStart, newEnd
+        common=`join -t "$tab" $i.out $f.out | awk -F'\t' '$2 < $5 && $4 < $3' | wc -l`
+        onlyOld=$((oldCount - common))
+        onlyNew=$((newCount - common))
     else
-        fields='*'
-    fi
-
-    echo "select $fields from $i" |  hgsql $db | tail -n +2 | sort > $i.out
-    f=$i"New"
-    echo "select $fields from $f" |hgsql $db | tail -n +2 | sort > $f.out
+        echo "select * from $i" |  hgsql $db | tail -n +2 | sort > $i.out
+        echo "select * from $f" |hgsql $db | tail -n +2 | sort > $f.out
         oldCount=`cat $i.out | wc -l`
         newCount=`cat $f.out | wc -l`
         common=`join -t '\001'  $i.out $f.out | wc -l`
         onlyOld=`join -t '\001' -v 1 $i.out $f.out | wc -l`
         onlyNew=`join -t '\001' -v 2 $i.out $f.out | wc -l`
+    fi
     echo $i $newCount "-" $onlyNew "=" $common "=" $oldCount "-" $onlyOld
     rm $i.out $f.out
 done > newGeneReviews$db.stats
 
 cat newGeneReviews$db.stats | awk -v db=$db -v tooMuch=$tooMuch '
 {
+    if ($6 == 0)
+	{
+	print "validate on " db "." $1 " failed: no rows in common";
+	exit 1
+	}
     if (($4/$6 > tooMuch) || ($10/$6 > tooMuch))
 	{
 	print "validate on " db "." $1 " failed:" $4,$6,$4/$6,$10,$6,$10/$6;
 	exit 1
 	}
 }'
 
 exit 0