MySQL Bugs: #103223: improve ifnull() documentation, refer to mixing collations

Bug #103223	improve ifnull() documentation, refer to mixing collations
Submitted:	6 Apr 2021 12:28	Modified:	18 Mar 2022 22:05
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.22,8.0.23	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	bug, collatiions, documentation, IFNULL()

Description:
I bumped into bug#102500.

In my case I was using IFNULL() and when I checked the documentation at https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#function_ifnull there is no mention of what to expect if the 2 expressions are strings.

How to repeat:
I used this simple script with dbdeployer against a single instance of 8.0.20 - .22. Only .22 failed.

#!/bin/sh

./use -vvv <<"EOF"

select @@version;

DROP DATABASE IF EXISTS test;

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

use test

CREATE TABLE `table1` (
  `column1` int unsigned NOT NULL,
  `column2` varchar(64) CHARACTER SET ascii NOT NULL,
  `column3` varchar(64) CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`column1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW CREATE TABLE table1\G

SELECT LOWER(IFNULL(MAX(column3), SUBSTRING_INDEX(@@hostname, '.', 1))) AS cluster_alias FROM test.table1 WHERE column1 = 1;

INSERT INTO table1 VALUES(1, 'column2', 'column3');

SELECT LOWER(IFNULL(MAX(column3), SUBSTRING_INDEX(@@hostname, '.', 1))) AS cluster_alias FROM test.table1 WHERE column1 = 1;
EOF

The error it gave was:

SHOW CREATE TABLE table1
--------------

*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `column1` int unsigned NOT NULL,
  `column2` varchar(64) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `column3` varchar(64) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  PRIMARY KEY (`column1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

--------------
SELECT LOWER(IFNULL(MAX(column3), SUBSTRING_INDEX(@@hostname, '.', 1))) AS cluster_alias FROM test.table1 WHERE column1 = 1
--------------

ERROR 1267 (HY000) at line 19: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation 'ifnull'

Suggested fix:
Given the documentation of ISNULL() does not mention strings or collation differences it would be good to update it to provide an expectation of any things to be aware of.  Even if in the case above bug#102500 indicates the error is a bug in 8.0.22/23 so the behaviour with "mixed collations" should work it's good to clarify expectations.

If there are other cases where it won't work pointing them out in this doc or perhaps pointing to some documentation related to comparing and mixing collations would be helpful.

Hello Simon,

Thank you for the documentation enhancement request.

regards,
Umesh

This is actually a problem for all CASE-derived functions, not just IFNULL.

Earlier, the determination of the aggregated collation ignored const values:
if the const value only contained characters from the same repertoire as the second string, the character set and collation of that string was chosen as the resulting properties of the IFNULL function.

However, with system variables like @@hostname no longer being const values, we
cannot inspect the contents during resolving and instead treat such values
equivalent to a column expression of the same collation.
This means we can now issue the "Illegal mix of collations" also when system variables are involved.

The workaround to this is to cast the system variable reference to a character string with the same character set as the column reference.

See also BUG#104387.

Fixed in mysqldoc rev 72360.

Closed.