Thursday, October 12, 2006

careful with commas in your SQL statements

yesterday we had a bug that was something like:

select field1, field2, field3
field4, field5
from SomeTable
where field1 = 'someValue'

can you spot the bug?

the query runs just fine, but it didn't output the desired results

the problem is that it's missing a comma between field3 and field4, thus is like saying:

field1, field2, field3 as field4, field5

which, you can imagine; you are expecting certain data (and data type) on that field on you get something else and you mis a field

this syntax I believe is Ansi 92 SQL standard, so you can get the same problem on MSSQL, MySQL, Oracle, etc (our code ran fine in MySQL and the bug came up in Oracle because we had different data)

No comments: