Does this MySQL Query have any hidden gotchas?

Question

Hello, I hope this isn't a terribly obtuse question. I notice that MySQL will let me refer back to the "Mnth" field in my GROUP and ORDER. This is not the case in every db engine I've worked with. Can anyone confim that this is an OK approach?

SELECT DATE_FORMAT(FROM_UNIXTIME(`swauditlogs`.`dateline`),'%Y-%m') AS Mnth, Count(`swauditlogs`.`ticketid`) AS Count
FROM swauditlogs LEFT JOIN swtickets ON swauditlogs.ticketid = swtickets.ticketid
WHERE swauditlogs.actionmsg Like 'Ticket status changed from:%to: Closed'
GROUP BY Mnth
ORDER BY Mnth DESC

Answer 1

I don't know about any MySQL internals, but I used a similar query in our production system, and until today the query hasn't crashed it yet. So this is no MySQL core hacker answer, just my observation.

Oh, I forgot: WARRANTY DISCLAIMER: Just because I say that I see no gotchas, doesn't mean there are none ;-)

Answer 2

It's OK. From http://dev.mysql.com/doc/refman/5.0/en/select.html:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

I don't like that LIKE clause though. Isn't there another column you can test instead of having to perform a LIKE on a string?

Answer 3

That's acceptable in MySql:

see here: http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html

Note that if you are trying to follow standard SQL, you can't use expressions in GROUP BY clauses. You can work around this limitation by using an alias for the expression...

...implying that you can use expressions with MySql.

Answer 4

IMHO, the query looks okay for MySQL.

Standard SQL disallows references to column aliases in a WHERE clause but you can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html