[thelist] mySQL tip

[Brooking, John]

List,

   I've just spent 3 lunch hours solving this bug, so I thought I'd
share the knowledge.

<tip author="John Brooking" type="mySQL column alias caution">
When selecting columns from mySQL (and other RDBMS'), you can assign
them aliases using the "AS alias" syntax after the column name.
(Actually, the "AS" keyword is usually optional, but I like to include
it for readability.) This is useful for giving real names to calculated
fields. But be careful how you name the alias. Some RDBMS' will give an
error if you attempt to give an alias which is the same name as an
actual column. Others let you do it, but the results may not be what you
expect.

For example, consider the mySQL query:

   SELECT Name, DATE_FORMAT( LastActivity, '%m/%d/%Y') AS LastActivity
   FROM Addresses
   ORDER BY LastActivity DESC

Notice that "LastActivity" is both a column name, and the alias for the
formatted form of that column. Which one will mySQL use for the ORDER
BY? If you assumed the raw column value, as I did, you would be wrong.
It apparently uses the result of the calculation. (This makes sense when
you think about it, because the calculated value is part of the output
and the raw value is not.) So I think I'm showing the records by
descending date, but in reality, records with the date '12/10/1999'
appear prior to '01/01/2004', because is sorting on the calculated
expression, a string in which the month comes first. So it will list
December 31st through the 1st for all years, then November for all
years, etc. Doh!

Best practice: Avoid column aliases which are the same as field names,
as well as keywords. (I shoulda known better!)
</tip>

By the way, in response to wondering what were the website ideas I
referred to a few days ago, I don't have a problem sharing them, I just
haven't had time. And now my lunch is gone for today, so I don't know
when I will, but I'll try soon.

- John
-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.