Nowadays, exactly since QGIS version 3.16, there is another possibility: use the overlay functions of QGIS expressions, either with Geometry Generator or Geometry by expression. Use this expression:
Screenshot: red= schools, white=towers; the black lines (arrows) ...
The >0 distance returned by the query is actually correct.
Let's remember that when using the type geometry, distances are computed using a straight line while with the type geography, the great circle line is used.
So, your data is in geometry and you compute the closest point using a straight line (between the start and end point of the line). The ...
If you segmentize (or densify) your line then it will work better.
ST_Segmentize(ST_MakeLine(ST_MakePoint (- 86.26725, 34.32582) , ST_MakePoint (- 86.14040, 34.39281)),.01) AS line
, ST_MakePoint (- 86.19219 , 34.36546) AS point
st_distance(point::geography, line::geography) AS i
, st_distance(st_closestpoint(line, point)::...