3

https://data.stackexchange.com/stackoverflow/s/1323/ownerdisplayname shows that many posts have a OwnerDisplayName field that is different from the user's current display name (which is what the SO web site actually shows). The same is true for LastEditorDisplayName. What is the reason why this discrepancy has not been corrected?

1
6

There are a few of these "de-normalized" (they have a second functional purpose here) fields in the SEDE schema.

Completing what you've already found here is the list of table and their fields where de-normalized data is stored:

  • Posts(OwnerDisplayName, LastEditorDisplayName)
  • Comments(UserDisplayName)
  • PostHistory(UserDisplayName)
  • PostHistory(Text) for PostHistoryTypeId with values (10,11,12,13,14,15,19,20,35)

The Text field in PostHistory holds a JSON with this content

{"Voters": [ {"Id":811, "DisplayName": "Shog9"}, { ... } , ... ]}

It is important to recognize that these fields are filled with data that was valid at that moment in time and is not updated afterwards under normal system operation.

Sometimes this is needed to facilitate showing a username on post when a questions gets migrated and the post owner doesn't have an account on the target site. This timeline shows an example of such case. Same is true for when a user deletes their user profile or when they request to have their name removed from the post.

Other case are for situations where doing extra queries to fetch up to date data was considered to be too expensive (in milliseconds) to guarantee a good performance.

For SEDE these principles or conditions might not apply but the SEDE schema is strongly based on the actual production database and as SEDE is often used to research certain aspects of the site it is important it stays close to the source to prevent that it becomes more of a guessing game then an evidence based discovery.

And having this "incorrect" data allows us to do, similar to what Ilmari did in his query, a list of "voters" (close, re-open, etc) that changed their displayname. This query shows that, sorted by most often changed and showing all known names and their occurrence in time.

;with voters as
(
Select users.UserId
    , users.UserDisplayName
    , CreationDate
From PostHistory
cross apply openjson(Text, '$.Voters') 
       with ( UserId int '$.Id'
            , UserDisplayName nvarchar(150) '$.DisplayName') users
where posthistorytypeid in (10,11,12,13,14,15,19,20,35) 
and Text is not null 
and len(text) > 0 
and users.userid <> -1
)

select UserId [User Link]
     , userdisplayname [Former names]
     , [First seen]
     , [Last Seen]
from (
  select userid
       , userdisplayname 
       , min(creationdate) [First Seen]
       , max(creationdate) [Last Seen]
  from voters
  group by userid, userdisplayname 
  having count(*) > 1 
)  data
order by (select count(*) 
          from (
            select distinct 
                   userid
                 , userdisplayname 
            from voters 
            where userid = data.userid) ord_data 
         )  desc
       , userid 
       , [last seen] desc

When run today you'll find a remarkable collection at the start but going down a bit we also appreciate:

query result showing user Lightness Races in Orbit with name Lightness Races BY-SA 3.0 and Non-Stop Time Travel

Keep in mind SEDE is updated once a week on Sunday.
Use the educational SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

1
  • Yes, and as such it absolutely has its uses, even if it may not look like it. Please don't ever change it. – Mast Aug 15 '20 at 19:17
0

The Stack Exchange Data Explorer is not live but is only updated monthly (if that). Thus, changes in the intervening time will not be reflected.

1
  • I've updated the query to make this a bit more clear: there is a discrepancy even in what should be the same old database version. – PleaseStand May 8 '11 at 23:50

You must log in to answer this question.

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