Collation Weirdness with UNION in VIEW

[Andy Theuninck]

Server, database, and connection collation is set to utf_general_ci. The underlying table has collation utf_general_ci on all character columns.

My view looks like this:

CREATE VIEW someView AS

SELECT description FROM table

UNION ALL

SELECT CAST(price AS char(10) AS description FROM table

The CREATE VIEW statement *succeeds*, but any operation on the view fails with an illegal mix of collations for operation UNION error. This includes SHOW FULL COLUMNS and SHOW CREATE VIEW. Running mysqldump on the view crashes.

If I do this:

CREATE VIEW anotherView AS
SELECT CAST(price AS char(10) AS description FROM table

And then run SHOW FULL COLUMNS on the resulting view collation is in fact utf_general_ci. And if I manually specify the collation with my union view, that will work:

CREATE VIEW someView AS

SELECT description FROM table

UNION ALL

SELECT CAST(price AS char(10) COLLATE utf_general_ci AS description FROM table

How exactly does CAST determine what collation to use if not the server/database/connection default? The documentation says it will use the default character set (which is utf8) but is not clear on collation. Why in the world would the exact same CAST result in a different collation when part of a UNION? Is there any way to manually get at the view definition and see what's going on when SHOW CREATE VIEW and mysqldump don't work? EXPLAIN does not seem to work with CREATE VIEW.

I do not see adding an explicit COLLATE statement as a solution. What I want to do is use the local default. What's not clear is how "default" works in this context.