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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 4.1.3b-beta-nt | OS: | Windows (Win2K) |
Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Oct 2004 12:17]
Victor Vagin
[8 Oct 2004 13:48]
MySQL Verification Team
Virefied with 5.0.2-alpha-debug-log and 4.1.6-gamma-debug-log. OS: Windows, Linux
[18 Oct 2004 14:46]
Alexander Barkov
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.
[2 Nov 2004 5:14]
Alexander Barkov
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.
[18 Nov 2004 3:26]
B Jones
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;
[18 Nov 2004 3:27]
B Jones
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.
[21 Nov 2004 22:52]
B Jones
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.
[24 Nov 2004 22:59]
B Jones
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"
[8 Dec 2004 15:56]
Krzysztof Krzyżaniak
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'
[8 Dec 2004 15:58]
Krzysztof Krzyżaniak
Errm, ifnull ofcourse. Version of engine: 4.1.7.
[31 Mar 2005 5:19]
Alexander Barkov
Fixed in 4.1.11 and 5.0.4
[1 Apr 2005 23:05]
Paul DuBois
Note in 4.1.11 changelog that the coercibility of NULL has been changed.