PostTags does not include deleted questions

Question

The PostTags table in SEDE does not contain deleted posts. For example, on Worldbuilding:

• Nondeleted posts: Total: 10845, In PostTags: 10845 (100.00%)
• Deleted posts: Total: 1410, In PostTags: 2 (0.14%)

This doesn't seem to make sense because:

• PostsWithDeleted does exist, to give us some info about deleted posts.
• The PostsWithDeleted.Tags is not sanitized, implying that it's acceptable and desirable to have the tags of deleted posts exposed.
• Note that every question in Posts and PostsWithDeleted has a non-null, non-empty Tags field, which I verified as a sanity check.

So basically, we do have access to information about the tags of deleted posts (via PostsWithDeleted.Tags), but, unlike non-deleted posts, we're stuck with no good way to process that information (as Tags is just a string of tag names enclosed in angle brackets).

PostTags should include deleted post tags to be consistent with PostsWithDeleted and allow consistent access to tag information. Alternatively, PostTagsWithDeleted could be added.

I primarily consider this a bug due to the inconsistency which does not seem reasonably intentional (why present the information in PostsWithDeleted.Tags without giving PostTags-style access to it?), and it's a usability issue. However, this could also be considered a feature request. For now I've tagged as both.

Answer 1

As annoying as this can be, it reflects the state of the internal database which also drops deleted posts from PostTags. That is to say, we're not doing extra work to drop deleted posts here (as is the case for some other tables in the public data dump). Contrast with PostLinks, which does include deleted posts (as does the internal table from which it is generated).

If you think about it a bit, it has to be this way: deleted posts can contain tags that no longer exist in the system, so a relational mapping to the Tags table would be incomplete at best.

If you need tag stats on deleted posts, parsing the Tags field is the way to go. LIKE wildcards work pretty well here in most cases.

Answer 2

Given that it's by design, here's a basis for a workaround:

SELECT
  Id PostId,
  value TagName
FROM
  PostsWithDeleted 
  CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(Tags,'><',','),'<',''),'>',''),',')
WHERE
  DeletionDate IS NOT NULL

Tag IDs can be included although, as Shog9 notes, deleted tags will have null IDs:

SELECT
  p.Id PostId,
  value TagName,
  t.Id TagId
FROM
  PostsWithDeleted p
  CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(Tags,'><',','),'<',''),'>',''),',')
  LEFT OUTER JOIN Tags t ON t.TagName = value
WHERE
  DeletionDate IS NOT NULL

The DeletionDate constraint can be removed to include all posts (you won't need to get anything from PostTags here, it's redundant to the info included above -- assuming tag names are unique).

On the bright side it seems reasonably fast, it does run on SO.