WL#529: Update CAST to support all standard SQL options

Affects: Server-4.1   —   Status: Assigned   —   Priority: Low

Updated by Trudy Pelzer 2005-07-25:

MySQL will update the CAST function to support all
cast operations required by the SQL Standard.

Rationale:
Our current CAST capabilities do not permit users
to cast values between all similar data types, as
required by standard SQL and supported by other
DBMSs. Since implicit CASTs support a greater variety
of cast possibilities, the explicit CAST support 
should be augmented to match. 


[ Comment added by Peter Gulutzan 2003-06-01 ] 
 
In fact, we want to support any CAST which is reasonable, 
according to SQL standard feature ID F201 "CAST function". 
 
Any number can be cast to another number. 
 
Any data type can be cast to CHAR/VARCHAR/TEXT. 
 
The size is specifiable. For example: 
 
CREATE TABLE t (s1 CHAR(3)) 
INSERT INTO t VALUES ('xxx') 
SELECT CAST(s1 AS CHAR(4)) FROM t 
 
The result of the above CAST should be a four-character value. 
 
At the Budapest conference, Monty explained that this would 
be in version 4.1.2, and that the priority should be = 80. 


The orginal HLD said the following:

Add conversion to Item_date_typecast::val_str() to format the 
argument to an ANSI SQL DATE string "YYYY-MM-DD" 
 
Example usage: 
 
SELECT (CAST "1993-3-3 12:12:32" AS DATE); 
-> "1993-03-03" 
 
SELECT (CAST 19930303 AS DATE) 
-> "1993-03-03" 
 
Implementation suggestion: 
 
You can use item->getdate() to efficently get the date from an item. 
 
Should return NULL for wrong dates (for which get_date return 1) 
 
After this is done, the same should be done to DATETIME and TIME. 

The above has been done.
-- Trudy Pelzer 2005-07-29
 
[ Comment added by Peter Gulutzan 2003-06-01 ] 
 
In fact, we want to support any CAST which is reasonable, 
according to SQL standard feature ID F201 "CAST function". 
 
Any number can be cast to another number. 
 
Any data type can be cast to CHAR/VARCHAR/TEXT. 
 
The size is specifiable. For example: 
 
CREATE TABLE t (s1 CHAR(3)) 
INSERT INTO t VALUES ('xxx') 
SELECT CAST(s1 AS CHAR(4)) FROM t 
 
The result of the above CAST should be a four-character value. 
 
At the Budapest conference, Monty explained that this would 
be in version 4.1.2, and that the priority should be = 80. 

Further work required
---------------------

The following chart shows the standard SQL possibilities for casting 
between data types. A Y in the intersection of a <source> value and 
<target> means that CAST must be supported, an N means that a CAST
is not possible and an M means that a CAST may be possible.

                <target>
<source>    EN AN VC FC D T TS YM DT CL BL
        EN  Y  Y  Y  Y  N N N  M  M  Y  N
        AN  Y  Y  Y  Y  N N N  N  N  Y  N
         C  Y  Y  Y  Y  Y Y Y  Y  Y  Y  N
         D  N  N  Y  Y  Y N Y  N  N  Y  N
         T  N  N  Y  Y  N Y Y  N  N  Y  N
        TS  N  N  Y  Y  Y Y Y  N  N  Y  N
        YM  M  N  Y  Y  N N N  Y  N  Y  N
        DT  M  N  Y  Y  N N N  N  Y  Y  N
        BL  N  N  N  N  N N N  N  N  N  Y

Where:
EN = Exact Numeric (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DEC, NUMERIC)
AN = Approximate Numeric (REAL, FLOAT, DOUBLE PECISION)
C = Character (CHAR, VARCHAR, CLOB/TEXT)
FC = CHAR
VC = VARCHAR
CL = CLOB/TEXT
D = DATE
T = TIME
TS = DATETIME/TIMESTAMP
YM = Year-Month Interval
DT = Day-Time Interval
BL = BLOB