15

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.

1
  • 11
    Programming questions are off-topic on Meta Stack Overflow... ah, whatever. Dec 7, 2013 at 17:34

1 Answer 1

20

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.
5
  • 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, 2013 at 17:13
  • 2
    Hmm, perhaps we should annotate the type in the schema for nullable columns...
    – Tim Stone
    Dec 7, 2013 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... Dec 7, 2013 at 17:50
  • 1
    Shouldn't that be made a NOT NULL field (possible along with DEFAULT 0)? Dec 9, 2013 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, 2019 at 19:28

You must log in to answer this question.

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