mysql select concat(charfield, format(doublefield,8)) gives error 1267

Question

which is ironic on two counts, 1) because concat(charfield, doublefield) works (it doesn't mind when one of the fields to be concatenated is numeric) and 2) because the mysql reference shows this: CONCAT(str1,str2,...) as the prototype for CONCAT and for FORMAT this: "FORMAT(X,D) Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string".

In desperation I tried select concat(symbol, cast(format(closeprice,8) as char)), which worked. So you could be thinking, "why is this guy wasting our time, he found something that works", which is true. But it doesn't make sense to me, and so I was wondering if anyone could clear it up?

Answer 1

1267 is illegal mix of collations. The collation (alphabetical sorting) of charfield and the collation of the string returned from FORMAT (probably your db server's default collation) are different.

Modify either the column's collation or the server default collation, or do the cast.