While researching my theory that showing comments costs votes, I noticed that there are surprisingly few posts with zero comments. Here's what the distribution looks like:

Count vs. number of comments

That graph was generated using the following SQL:

select CommentCount, count(*) 
from Posts 
where CommentCount < 30
group by CommentCount
order by CommentCount

The huge distribution disconnect makes me think comments are not being counted correctly.

  • 11
    Programming questions are off-topic on Meta Stack Overflow... ah, whatever. – BoltClock's a Unicorn Dec 7 '13 at 17:34

Most posts have NULL in the CommentCount field

The CommentCount < 30 clause explicitly filters out rows where CommentCount is NULL. (This is because NULL represents the absence of data so logical operators involving NULL always return false.) Thankfully, SQL flavors offer ways to treat NULL as something else (usually zero):

select isnull(CommentCount, 0), count(*) 
from Posts 
group by isnull(CommentCount, 0)
order by isnull(CommentCount, 0)

This results in a more likely looking distribution:

Count vs. number of comments (corrected)

What does NULL mean in CommentCount?

By comparing the posts with 0 comments to those with NULL, it seems that the row is created with a null CommentCount field. As comments are added, the field is updated to reflect the number of comments on the post. When comments are removed, the field is decremented. When all comments are removed the field becomes 0. So:

  • NULL—No comments have ever been added to the post.
  • 0—Comments have been added to the post, but they've all been removed.
  • 6
    FWIW: you'll run into the same thing with AnswerCount. One of those little pitfalls that can send you waaay too far down a wrong path unless you regularly sanity-check your results by running queries that rely on normalized data. – Shog9 Dec 7 '13 at 17:13
  • 2
    Hmm, perhaps we should annotate the type in the schema for nullable columns... – Tim Stone Dec 7 '13 at 17:44
  • 8
    It's a "count" field @Tim. It would make more sense if it were defaulted to 0 by SE; though maybe the nuance's that Jon is describing are used for some purpose... – ben is uǝq backwards Dec 7 '13 at 17:50
  • 1
    Shouldn't that be made a NOT NULL field (possible along with DEFAULT 0)? – Bernhard Barker Dec 9 '13 at 3:53
  • See also: What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?, Why shouldn't we allow NULLs?, or NULL or NOT NULL by default? from our DBA.SE site. That's how it was designed, with both NULL and 0. – Rob May 3 '19 at 19:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .