Bizarre Question Data

Question

Here's one for those who like a toasted brain sandwich.

I was running some stats using [data.SE] and noticed a few strange entries in the DB, so I wrote another query to single these out.

https://data.stackexchange.com/stackoverflow/query/75337/bizarre-question-stats-by-tag

It looks for questions that have an accepted answer value and an accepted answer count of zero.

I would have expected this to return 0 row(s) in all cases, however running this query on the android tag I get 3 results.

At first I thought there was a definitive logic behind it based on question merging. Upon closer inspection I noticed something a little more curious:

• #3428187 was merged and has no closed date
• #5215141 was merged but this has a closed date
• #2228827 not merged, no close date and has a creation date in a different format than the others

I tried to decipher what the story behind these was by looking at them on SO but my sleuthing deerstalker had been stolen by a klepto-maniacal lolcat while I was buried in SQL.

Can anybody shed some light on what I'm missing other than a tatty old hat and the fish I was going to cook for supper?

Answer 1

I started with your query and removed the tag selection and added joins for the post answer and posthistory.

That query looks like this:

SELECT distinct p.id as[Post Link]
, p.AcceptedAnswerId as [Accepted Answer Id]
, ap.id as [Post Link]
, ph.CreationDate as [Merged on]
FROM Posts p
left outer join posts ap on ap.id = p.AcceptedAnswerId
left outer join posthistory ph 
  on ph.postid = p.id and ph.PostHistoryTypeId = 37
WHERE p.PostTypeId = 1
AND p.AnswerCount = 0
AND p.AcceptedAnswerId IS NOT NULL

The result is a table with a non-null value in Accepted Post Link, and/or

For the dump of August, 17th 2014 this gives:

• 103 questions with an answercount of 0 and an non-null acceptedanswerid

• 40 of those questions have no accepted answer. This is caused by the fact that the answer is deleted while it is still accepted, as pointed out by ughoavgfhw

  Example from Can initialPlaybackTime do not preform animation?:

• 67 of those questions have a merge record in the PostHistory table, which means that two posts have been merged

  • 63 of those got the accepted answerid transferred from the merged post
  • 4 questions are merged with posts that point to an now deleted answer that is no longer accepted but probably was accepted when the posts were merged. I can't say for sure because the unaccept events are not stored in SEDE.

  an example is:
  Post Link | Answer | merged on |
--------------------------------------------------------
  `` Can you shorten the following query? | 3078899 | 2010-06-23 |

  where post 3078899 is this deleted answer:

Looking at the Post Timeline reveals that it was accepted on Jun, 15th 2010 and unaccepted (twice?) on Jun, 22nd/23nd