2

The SEDE is currently reporting the exact same results from Posts and PostsWithDeleted. For example,

When I include the execution plan for the Posts query, it references the PostWithDeleted table:

enter image description here

Can this be fixed? Am I doing something wrong? Am I misinterpreting what PostWithDeleted is supposed to contain?

3

The field owneruserid is sanitized for deleted posts so it is expected if you run a query filtering on owneruserid that the results are the same.

It would be a bug if they were different.

You're misinterpreting the Database schema documentation.

You find in Posts all non-deleted posts. PostsWithDeleted includes deleted posts while sharing the same columns with Posts but with only a few fields populated which are marked with a 1 below.

(emphasis mine)

The OwnerUserid doesn't have that 1.

screenshot of creationdate, deletiondate and score having an superscript 1 while owneruseid does not have that 1

Trying to make more clear what the functional difference is between those two tables I have update the text to:

You find in Posts all non-deleted posts. PostsWithDeleted includes rows with deleted posts while sharing the same columns with Posts but for deleted posts only a few fields populated which are marked with a 1 below.

That the execution plan shows the PostsWithDeleted table is caused by the fact that the Posts table is really a view over the PostsWithDeleted table. The query engine has no Posts table it can use to project that result from.

That view is created in the sp_Refresh_Database.sql near the end:

Declare @viewSql nvarchar(200) = 'Create View [Posts] As Select * From [PostsWithDeleted] Where DeletionDate Is Null';
Exec @dbSql @viewSql;

That specific stored procedure is run on each weekly refresh.

  • So there's no way of identifying someone's deleted posts... that seems strange. Any reason why OwnerUserId wasn't kept? – Werner Jul 8 at 17:42
  • 1
    @Werner it isn't kept as you can only find those deleted posts if you know the id, or are the owner and have > 10K (in which case isdeleted:1 will work in search). Only mods can find deleted posts for a specific user. So what is available in SEDE mimics what is available on the sites for every user. Before that SEDE gets more data I expect the functionality on the sites to find deleted posts need to be expanded. Or you become a mod, or an SE employee... – rene Jul 8 at 18:58

You must log in to answer this question.

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