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.

Add comment

New comments can no longer be posted on this entry.

Conferences

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

Upcoming

SCaLE 2023
Mar 9-12, 2023
Pasadena, CA, USA
Nordic PGDay 2023
Mar 21, 2023
Stockholm, Sweden
pgday.Paris 2023
Mar 23, 2023
Paris, France
PGCon 2023
May 30-Jun 2, 2023
Ottawa, Canada
PGConf.EU 2023
Dec 12-15, 2023
Prague, Czechia

Past

FOSDEM 2023
Feb 2-5, 2023
Brussels, Belgium
PGConf.EU 2022
Oct 25-28, 2022
Berlin, Germany
PGConf.NYC 2022
Sep 22-23, 2022
New York, USA
Swiss PGDay 2022
Jul 1, 2022
Rapperswil, Switzerland
pgCon 2022
May 24-27, 2022
Online, Online
More past conferences