Cannot resolve collation conflict between "Polish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator occurring in SELECT statement

Question

My query selects data from two databases on the same server.

The server collation is SQL_Latin1_General_CP1_CI_AS.

db1 collation is Polish_CI_AS

db2 collation was SQL_Latin1_General_CP1_CI_AS but I changed it to Polish_CI_AS using:

ALTER DATABASE [db2] COLLATE Polish_CI_AS;

Now when I run this script:

SELECT name, collation_name FROM sys.databases; 

I can see that both databases have the same collation Polish_CI_AS.

But I still receive below error:

Cannot resolve collation conflict between "Polish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator occurring in SELECT statement

In the below fragment of query:

CASE
    WHEN 1=1 THEN db1.colX
    WHEN 1=1 THEN db2.colY
END AS TEST

However, when I add "COLLATE Polish_CI_AS" like:

CASE
    WHEN 1=1 THEN db1.colX COLLATE Polish_CI_AS
    WHEN 1=1 THEN db2.colY
END AS TEST

or

CASE
    WHEN 1=1 THEN db1.colX
    WHEN 1=1 THEN db2.colY COLLATE Polish_CI_AS
END AS TEST

then it works.

I also noted that it could be any collation, e.g.:

CASE
    WHEN 1=1 THEN db1.colX
    WHEN 1=1 THEN db2.colY COLLATE ESTONIAN_100_BIN 
END AS TEST

and it still works.

Question: Why, having the same collation in both databases the query doesn't work but after adding any collation to it it does?

Answer 1

db2.colY will still have Polish collation (as shown in sys.columns). Changing the database collation doesn't affect that of pre-existing columns.

When you add COLLATE ESTONIAN_100_BIN you no longer have conflicting collations of the same precedence but are in the Explicit Label vs. Implicit Label case

the Explicit label ... takes precedence over the Implicit label