> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1200-to-1299/e1271.md).

# Error 1271: Illegal mix of collations for operation

| Error Code | SQLSTATE | Error                            | Description                                  |
| ---------- | -------- | -------------------------------- | -------------------------------------------- |
| 1271       | HY000    | ER\_CANT\_AGGREGATE\_NCOLLATIONS | Illegal mix of collations for operation '%s' |

## Possible Causes and Solutions

The server returns this error when an operation combines strings with collations that it can't reconcile, and the operation has more than three operands, or is a `UNION`. Each string has a collation and a [coercibility](/docs/server/reference/sql-functions/secondary-functions/information-functions/coercibility.md). When two operands have different collations at the same coercibility level, for example two columns, the server can't choose one of them:

```sql
SELECT a FROM c1 UNION SELECT b FROM c1;
ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'

SELECT a FROM c1 WHERE a IN (b, c, d);
ERROR 1271 (HY000): Illegal mix of collations for operation 'in'
```

With two or three operands, the server names the collations, and the error code differs:

* [Error 1267](/docs/server/reference/error-codes/mariadb-error-codes-1200-to-1299/e1267.md), for two operands: `Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='`
* [Error 1270](/docs/server/reference/error-codes/mariadb-error-codes-1200-to-1299/e1270.md), for three operands: `Illegal mix of collations (utf8mb4_general_ci,IMPLICIT), (utf8mb4_unicode_ci,IMPLICIT), (utf8mb4_bin,IMPLICIT) for operation 'in'`

Not every mix fails. A string literal adapts to the column it's compared with, and a column in a character set that the other operand's character set contains, such as `latin1` compared with `utf8mb4`, is converted.

### New Tables After an Upgrade

In MariaDB 11.8 and later, [character\_set\_collations](/docs/server/server-management/variables-and-modes/server-system-variables.md#character_set_collations) makes `utf8mb4_uca1400_ai_ci` the default collation of `utf8mb4` and the other Unicode character sets. A column defined with `CHARACTER SET utf8mb4` and no `COLLATE` clause gets that collation, even in a table whose default collation is `utf8mb4_general_ci`. Comparing it with a column of an older table then fails:

```sql
CREATE TABLE c2 (a VARCHAR(10) CHARACTER SET utf8mb4);
SELECT COUNT(*) FROM c1 JOIN c2 ON c1.a = c2.a;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_uca1400_ai_ci,IMPLICIT) for operation '='
```

### Diagnosing Collations

To find the collation of each column, query [INFORMATION\_SCHEMA.COLUMNS](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-columns-table.md):

```sql
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND COLLATION_NAME IS NOT NULL;
```

For an expression, use the [COLLATION()](/docs/server/reference/sql-functions/secondary-functions/information-functions/collation.md) and [COERCIBILITY()](/docs/server/reference/sql-functions/secondary-functions/information-functions/coercibility.md) functions.

### Resolving Collation Conflicts

* For a permanent fix, give the columns the same collation, for example with `ALTER TABLE c2 MODIFY a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`. When you create new tables, name the collation explicitly, or set `character_set_collations` to match your existing tables.
* For a single query, give one operand an explicit collation. An explicit `COLLATE` clause has priority over the column's collation:

  ```sql
  SELECT * FROM c1 WHERE a = b COLLATE utf8mb4_unicode_ci;
  ```

For more about collations, see [Character Sets and Collations Overview](/docs/server/reference/data-types/string-data-types/character-sets/character-set-and-collation-overview.md).

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}