Data Explorer Query crashes with collation conflict error

Question

I have a website built off my Q & A posted in Stack Exchange sites such as; Ask Ubuntu, Stack Overflow, SE Security, etc.

Data Explorer is used to scrape ALL Stack Exchange Q & A I've posted and download the query results for filtering by a python script and publishing to GitHub Pages. There are 310 Questions and 2,181 Answers, of which 54 and 1,135, respectively, are worthy of publication.

Data Explorer has been working fine for a few years but today it crashed with error:

Line 11: Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in CASE operator occurring in GROUP BY statement column 4.

Here is the data explorer query, last changed on December 12, 2021.

How do I debug this error?

---

Problem fixed

A week after posting question the problem was fixed:

Many thanks for the answers posted below!

Answer 1

I've found the issue. Over the last few weeks the weekly refresh times got improved, a lot. From 6 to 8 hours we're now down to well under an hour.

One of the improvements was revealed to me by Aaron and involves pre-staging the PostHistory table. With that in mind I looked at your query to see if PostHistory was invovled. I then ran this query to see on which columns there were collation mismatches.

You can see both SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS are used. The former is the database default, the latter how most columns in the schema were created. Most is the critical point here. It looks like the tables and views and their columns that are involved in one way or the other with the new and much needed improvements now have a different collation, or to be precise: the database default collation. See also: Let the default collation of the SEDE databases match the dominant collation used for the nvarchar columns

To solve the issue with your specific query for now we have to sprinkle a few COLLATE hints in the query to tell SQL Server how to deal with these differences in collation.

In this query I've made these changes:

...
  coalesce(Users.DisplayName, PostHistory.UserDisplayName collate SQL_Latin1_General_CP1_CS_AS),
...

in both the projection and the group by of the UserName column inside the Editor's CTE.

One of the debug option is to run each select of the CTE separately, for example by running this:

select
        PostHistory.PostId as PostId,
        PostHistory.UserId as UserId,
        coalesce(Users.DisplayName, PostHistory.UserDisplayName /* collate SQL_Latin1_General_CP1_CS_AS */) as UserName,
        min(PostHistory.CreationDate) as FirstEditDate
      from
      (  select Id as PostId, ParentId, PostTypeId
         from Posts
         where OwnerUserId = 811) PostsByUser
        join PostHistory on PostHistory.PostId = PostsByUser.PostId
        left join Users on Users.Id = PostHistory.UserId
      where
        PostHistory.PostHistoryTypeId in (4,5)  -- edit body/title
        and (PostHistory.UserId != 811 or PostHistory.UserId is null)
      group by
        PostHistory.PostId,
        PostHistory.UserId,
        coalesce(Users.DisplayName, PostHistory.UserDisplayName /* collate SQL_Latin1_General_CP1_CS_AS */)

You'll get the error:

Line 1: Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in CASE operator occurring in GROUP BY statement column 4.

The line number indicator isn't very helpful here, but at least you can now have localized the issue to only this query.

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

Answer 2

I did intentionally avoid case-sensitive collations in some of the recent improvements I've been working on, in order to avoid forcing you to use COLLATE clauses when joining to the views I created.

But I perhaps over-reached, and I did not test cross-database joins against Stack Overflow specifically after the most recent changes, so that's on me.

For next week

I've added explicit collation to the two tables that are pre-staged (which means they get the database's collation unless otherwise specified) instead of using SELECT INTO (which means they inherit the source collation). This will resolve the issue for the next data dump this coming Sunday.

In the meantime

You can use an explicit COLLATE clause in a couple of places. The least intrusive way is like rene described, but you'll also want one here:

coalesce(WikiTags.TagName, 
   Posts.Tags COLLATE SQL_Latin1_General_CP1_CS_AS , 
  Parent.Tags COLLATE SQL_Latin1_General_CP1_CS_AS ) as [Tags],

I wrote an alternative version of your query that adds this in the right places using a variable, making the workaround easy to remove them once it is no longer needed (e.g. in about 6 days). It also avoids all the build-up of #Sites data from parsing an answer from this site by using the new dbo.sede_ineachdb procedure. It returned data for me (6,800+ posts) in about 9 seconds without caching, and for you in about 8 seconds, which demonstrates that most of the overhead is looping through each database, not the query itself. In any case, I don't know if I'd try it for the likes of Jon Skeet.

Asides

<sub>There was a long-standing suggestion to change the SEDE databases to have a case-sensitive collation, but that would cause worse problems. I'm not ready to apply status-declined yet, but we'd have to figure out a way to address all the existing queries that would suddenly break because they didn't use ThE riGHt cAsE for object and column names.

I will agree with others that this type of retrieval seems more like a job for the API than for SEDE. Find the posts you're after using SEDE, then get bodies and all that in a more scalable way. Or break the query into multiple chunks.

I noticed that magic formatting for [Tags] isn't very useful in cross-database queries, since they all link to the query context's site. This is something, too, that will be easier to massage outside of SEDE.</sub>