A new MySQL gotcha

At least it's new for me, and it's not on the list. Bugged a colleague of mine today, and we were both very surprised for a while until we figured it out. Ok, it's not very complex, but the deal was that:

SELECT CAST(1 as CHAR)

works, but

SELECT CAST (1 as CHAR)

does not (syntax error!)

Now, CAST is already horribly limited in MySQL (who came up with the idea that you shouldn't be able to cast to all available types if it's reasonable to do the conversion?! Like from int to text...), but this is just too much. Since when does whitespace before a parenthesis matter in SQL?! Sheesh...

Yet another reason to prefer PostgreSQL...


Comments

Whitespace before parenthesis shouldn't matter, but MySQL allows reserved words to be table and field names, and the way it does that is by requiring no white space between the function name and its opening left paren.

Posted on Apr 1, 2010 at 00:46 by Joshua Kugler.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
PGCon
May 17-21, 2016
Ottawa, Canada
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGDay UK 2016
Jul 05, 2016
London, UK
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia

Past

Stockholm PUG 2016/2
Apr 26, 2016
Stockholm, Sweden
PGConf.US
Apr 18-20, 2016
New York, USA
pgDay Paris
Mar 31, 2016
Paris, France
Nordic PGDay
Mar 17, 2016
Helsinki, Finland
ConFoo
Feb 24-26, 2016
Montreal, Canada
More past conferences