MySQL Bugs: #5980: NULL requires a characterset in a union

Bug #5980	NULL requires a characterset in a union
Submitted:	8 Oct 2004 12:17	Modified:	1 Apr 2005 23:05
Reporter:	Victor Vagin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3b-beta-nt	OS:	Windows (Win2K)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
NULL requires a characterset in a union

How to repeat:
mysql> (select repeat(_utf8'+',3) as h) union (select NULL);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'

mysql> (select repeat(_utf8'+',3) as h) union (select cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET utf8));
+------+
| h    |
+------+
| +++  |
| NULL |
+------+
2 rows in set (0.00 sec)

Suggested fix:
I want the first query to work as the second

Virefied with 5.0.2-alpha-debug-log and 4.1.6-gamma-debug-log.
OS: Windows, Linux

The problem is that NULL in MySQL has its own type.
If you run "CREATE TABLE t1 SELECT NULL" you'll get a CHAR(0)
column. As a result, NULL has its own character set and collation.

I told to PeterG. This is what he thinks:
"select concat(_utf8 'a', cast(NULL as char(0) character set utf8))" works.
it's too bad that the user has to go to extra trouble, but there is no
standard for this, the standard simply wouldn't allow you to say
"SELECT NULL". I think it is "not a bug". i suppose it can be considered a 
feature request, but victor would have to have a very good reason to ask for 
such a feature. we are within the rules now, and there is a workaround for the 
problem.

I dare not to agree with PeterG, and will fix this bug shortly
together with another problem:

SELECT IFNULL(NULL, _utf8'string')

produces the same error now.

We're getting the same error; singly the below INSERT SELECT UNION SELECT fails, but the otherwise identical INSERT SELECT; INSERT SELECT fails.

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'

But weirdly, we can't get the Swedish references out of MySQL.  We're now changed the server to use UTF and have no reference to Swedish anywhere, yet it keeps running back to latin1_swedish_ci.  Here are our settings (and the tables and columns and databases have been converted to):

character sets dir   	 /usr/share/mysql/charsets/  	 /usr/share/mysql/charsets/
collation connection 	utf8_general_ci 	utf8_general_ci
collation database 	utf8_general_ci 	utf8_general_ci
collation server 	utf8_general_ci 	utf8_general_ci

But we're still getting errors about latin1_swedish_ci.  Is this hardcoded?

insert into addresses (address_line_1, address_line_2, address_line_3, town, postcode, state_id, country_code_ISO3166)
select  NULL, NULL, nu_st, suburb, postc, state_id, C.country_code_ISO3166 
FROM ORACLE_COPY.enqdet E
JOIN AX.states S ON E.state 	  = S.state_name
JOIN AX.countries C ON E.country  = C.country_name
UNION DISTINCT
SELECT floor, building, reg_off_add, Suburb, post_co, S.state_id, C.country_code_ISO3166
FROM ORACLE_COPY.co_name CN
JOIN AX.states S ON CN.state = S.state_abbrev
JOIN AX.countries C ON "Austria" = C.country_name;

Amendment: We're getting the same error; singly the below INSERT SELECT UNION SELECT fails,
but the otherwise identical INSERT SELECT; INSERT SELECT fails^H^H^H^Hworks.

Our system guys were able to fix this!  They recomplied MySQL 4.1.7 "with only the UTF character set and collations."  The precompiled binaries are configured for Swedish collation and even with the rc.d reconfigured have a tendency to run back to it, thus this sort of problems.  Since recompiling we've had no problems.

It seems MySQL will keep the new Swedish defaults, so here's our post with the fix to http://bugs.mysql.com/bug.php?id=6653&edit=2.  An RPM is available with the fix already made.

---

Sergei Golubchik wrote:
> "Anything mixing strings with different collations." is not the query that
> worked before the upgrade, as in 4.0 you cannot "mix strings with different
> collations".
It was version 4.1.2 which changed the default collation to Swedish, 
at which point different collations became an issue.

> Also, "mixing strings with different collations" won't be fixed 
> by recompiling the server
But when our system guys recompiled as described, 
it did fix this and the Swedish NULL bug (#5980).

In case your other customers would like this fix, 
here's the description of the how-to from systems:

"They have to recompile MySQL from the source code.
Here are the parameters that I passed to the configure script
--prefix=/usr/local/mysql-4.1.7 --with-mysqld-user=mysql --with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=none --with-csv-storage-engine --with-isam --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static

Otherwise the defaults were charset latin1 collation latin1_swedish
Install no other charsets to avoid the NULL bug.
These will work with the 4.1 series and should work with series 5.0.
The remaining parameters are installation defaults.

We have a ready-to-run binary in Redhat RPM format containing this fix.
If you'd like this in RPM e-mail josh@innurve.com"

We have the same problem but with isnull function not with union. When table is created with utf8 character and connection & database character is latin1 everything goes fine except isnull(string_field,""); We got:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'ifnull'

Errm, ifnull ofcourse.

Version of engine: 4.1.7.

Fixed in 4.1.11 and 5.0.4

Note in 4.1.11 changelog that the coercibility
of NULL has been changed.