Bug #16904 CAST and CONVERT limitation
Submitted: 30 Jan 2006 16:26 Modified: 30 Jan 2006 17:17
Reporter: Nuno Soares Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.18 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[30 Jan 2006 16:26] Nuno Soares
Description:
How can we CAST or CONVERT a value into a data type that is not supported by those functions ?
Why is CAST and CONVERT LIMITED to such a few destination data types ?

How to repeat:
SELECT 
CAST(
   CASE
      WHEN col1 = 'XPTO' THEN 1
      ELSE 0
   END AS BIT) as myCol
FROM table1

If we create a view with the former select statement (removing the cast) and check the information schema for the datatype of 'myCol' it is of the data type bigint when it should by BIT.

SELECT CAST(col AS VARCHAR(100))
  FROM table1

Why can't we cast or convert to varchar !
[30 Jan 2006 17:18] Jorge del Conde
Hi!

I will set this report as a feature request to include all of our supported data-types in CAST/CONVERT.