MySQL: CAST requires no space before parenthesis?

Question

In MySQL 5.1, why won't this SQL work correctly?

SELECT CAST (20091023 as date);

[I've just figured out the answer to this question myself-- and I'll answer it myself below-- but the behavior was so odd that I wanted to capture it as a StackOverflow Q&A pair so others won't waste time on the same problem.]

Answer 1

This code works: (note the space is removed after "CAST")

SELECT CAST(20091023 as date);

Turns out MySQL requires that a specific list of built-in functions have no spaces between the function name and the parenthesis. See this page in the MySQL Manual for more info about why this behavior exists.

The following list of functions have the same restriction in MySQL 5.1 (there are many more in previous versions):

ADDDATE      
BIT_AND      
BIT_OR       
BIT_XOR      
CAST         
COUNT        
CURDATE      
CURTIME      
DATE_ADD     
DATE_SUB     
EXTRACT      
GROUP_CONCAT 
MAX          
MID          
MIN          
NOW          
POSITION     
SESSION_USER 
STD          
STDDEV       
STDDEV_POP   
STDDEV_SAMP  
SUBDATE      
SUBSTR       
SUBSTRING    
SUM          
SYSDATE      
SYSTEM_USER  
TRIM         
VARIANCE     
VAR_POP      
VAR_SAMP

Hope this answer helps you avoid wasting the same amount of time I did on this! :-)