What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 133 Stack Exchange communities.

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.

http://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?

share|improve this question
4  
"...but has a creation date in a different format than the others" - That's actually just my inability to count, sorry about that. –  Tim Stone Jul 14 '12 at 0:53
    
@TimStone +1 for honesty ;) –  Merlin Jul 14 '12 at 0:57
    
The third question has had its accepted answer deleted, but it is still marked as accepted. –  ughoavgfhw Jul 14 '12 at 1:07

1 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?: enter image description here

    • 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: enter image description here

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

share|improve this answer

You must log in to answer this question.

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