14

Over the weekend I had a major mishap in my understanding what the Votes table in the Stack Exchange Data Explorer actually contains.

For the last 6 to 8 years I assumed the schema only contains data for not deleted posts with a few exceptions, PostsWithDeleted and PostLinks. So when Jon Ericson shared a query that joined Votes and PostsWitdDeleted I told him that couldn't be correct. I convinced myself being right because a slightly adapted query by me showed way lower numbers. I did expect those to be zero, so I checked a few and all seemed to be related to roomba deletion, so I blamed the roomba script, put it to rest and called it a day.

On a revisit today I ran this query and that taught me that there are way more votes for deleted posts still in the votes table than can be explained by a roomba issue:

enter image description here

The conclusion so far: the votes for deleted posts are somewhat in the Votes table after handchecking a good 30 posts.

What is the story of these Votes for deleted posts? Is this meant to be this way or is there something broken in the sanitizing script?

1
  • 1
    I'm going to look into this - it's on my to do list for hopefully today.
    – Taryn
    Oct 7, 2020 at 11:16

2 Answers 2

5

I checked the code that we use for the SEDE export and even double checked with Craver to make sure the logic was correct.

Votes for deleted posts are included in the Votes table.

The only votes we exclude from the Votes table are those that have a deletion date and have a VoteTypeId for a follow, everything else is fair game for querying. The specific WHERE clause is:

WHERE  DeletionDate IS NULL  
  AND  VoteTypeId <> 21

We do not exclude votes on posts that have been deleted.

2
  • 2
    This clears my misconception nicely. Now I can revisit all those posts where I said: That is not in the public schema ... ;)
    – rene
    Oct 7, 2020 at 18:50
  • 5
    Sweet, I created more work for you and not me. :P
    – Taryn
    Oct 7, 2020 at 18:50
10

I've checked a couple of dozen posts on Chess Stack Exchange, and I can confirm almost all votes are there right now, even for deleted posts. The few exceptions I found are when an author deletes their own post (apparently, that doesn't count as a delete vote), when a moderator converts an answer to a comment (idem) and when a question deletion deletes the corresponding answers. None of them have up- or downvotes.

However, this has always (or at least a long, long time) been the case for spam and rude/abusive votes (yes, votes, not flags; they're stored as votes in the database). One of my earliest projects on Stack Exchange was the Revision Downloader, for downloading potentially spam or rude/abusive posts. It was based off of an existing SEDE query (no idea which one, and apparently I didn't know much about attribution back then) which queries votes/flags on deleted posts. That query is still correct; when I run it now for Chess.SE it yields 68 results, which is exactly the number I get when I search for deleted:1 locked:1 migrated:0.

1
  • 1
    I was hand checking against SO. I didn't think of asking a mod of a smaller site and SEDE skills. Glad you could confirm it.
    – rene
    Oct 5, 2020 at 20:03

You must log in to answer this question.

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