Today I discovered a strange feature of IFNULL, when the parameters have the same encoding (charset) and different collation.
So we do a little test:
CREATE TABLE `test` ( `name1` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , `name2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC;
We send a request
SELECT IFNULL(name1, name2) FROM test;
And in response:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'ifnull'
At first glance, everything is logical - the comparison is different and an error is returned, but if you think about it, the IFNULL operator should not pay attention to the comparison at all.
Began to bypass the problem, the first thing that came to mind
SELECT IFNULL(name1 COLLATE utf8_general_ci, name2) FROM test;
The request worked without errors, but longer compared to a simple sampling of one field for 5-10ms, which is comparable to the time of the request itself (~ 6-8ms, in my case it is about a dozen joins). Because The load on the database is large, and the request is basic, then the solution did not suit me.
')
I think further. “And what if you try the usual IF? No, obviously it will not work ... What if? I won't lose much time, it's better to check. ”
SELECT IF(name1 IS NOT NULL, name1, name2) FROM test;
What was my surprise when the request worked.
"How can it be. I probably made a mistake somewhere ... Recheck - it really works, but probably even longer. ”
“No, the request time has changed by 1ms, this is at the o_0 error level”
Another weird thing
ALTER TABLE `test` MODIFY COLUMN `name2` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL AFTER `name1`;
We send a request
SELECT IFNULL(name1, name2) FROM test;
"It worked? 0_o Right here I would have expected to see an error, but there is none! ”
In the manual about this behavior did not find anything. What is this bug or feature?
PS Checked on versions 5.0 and 5.1.