Collation Change Planning & Fixing

[skipowder99]

We recently decided to change from the default collation of UTF8_BINARY to UTF8_LCASE and there are many issues.  We attempted to cascade the collation defaults to all the Catalogs, Schemas, Tables, Views and Columns, but we still seem to have expression errors or data type mismatch errors.  Anyone has some advice on how to get all our notebooks and views going again with out explicit collation commands ?

[szymon_dybczak]

Hi @skipowder99 ,

According to docs,  ALTER CATALOG ... DEFAULT COLLATION only affects newly created schemas, and ALTER SCHEMA ... DEFAULT COLLATION only affects newly created objects. Likewise, changing a table’s default collation only affects new STRING columns; existing columns retain their original collation until altered individually.

Make sure that your objects have expected collation. Another thing that it's worth checking is session collation.
Queries use the session collation, not the catalog or schema collation. The system session default remains UTF8_BINARY. 

So, that can cause the mismatch you experienced. Your target objects have UTF8_LCASE collation, but still queries that are loading data to those objects use  UTF8_BINARY collation.

If my answer was helpful, please consider marking it as accepted solution to the thread.

[balajij8]

Hi skipowder99,

When you set a DEFAULT COLLATION at the catalog or schema level, it is forward looking. It does not retroactively apply to existing tables, columns or views. It leaves you with a split environment the newly created objects default to UTF8_LCASE, while the existing ones are still with UTF8_BINARY.

The expression and data type mismatch errors you are seeing occur because the engine enforces collation rules during query planning. If you join an older UTF8_BINARY column with a newer UTF8_LCASE column or compare an existing table column to a string literal (which now inherits the new schema default), it throws an error.

Views are particularly stubborn here as they bake in the implicit collation of their underlying columns at the exact moment they are created.

You need to systematically update the existing metadata to get your environment running again.

1. Recompiling Views - As views lock in the collation at creation, they won't automatically inherit the new schema default. You must recreate them provided the underlying tables have already been fixed.

2. Updating Base Table Columns - For the base tables, you have two options depending on your compute version. You can alter the column collations in place in serverless warehouse.

ALTER TABLE my_schema.my_table 
ALTER COLUMN my_string_column TYPE STRING COLLATE UTF8_LCASE;

If that feature isn't available on the runtime, you can recreate the tables using a CTAS operation. Since your schema default is now UTF8_LCASE, the new table will automatically pick it up.

3. Execution Order - You should audit and execute these changes in a strict dependency order to avoid recursive errors. You can find the lagging columns by querying the information schema. Apply the fixes bottom-up from there.

• Update or recreate base tables first.

• Recreate the views that query those base tables directly.

• Recreate downstream views (views querying views) moving from the leaf nodes up to the root.