Two small gripes about MySQL

rhesa on 2007-03-30T02:01:15

  1. You can use an aliased column in the GROUP BY clause, but not in the WHERE clause:
    SELECT func(foo) as alias FROM baz GROUP BY alias
    works, but
    SELECT func(foo) as alias FROM baz WHERE alias='quux'  (*)
    doesn't. You have to write
    SELECT func(foo) as alias FROM baz WHERE func(foo)='quux'


  2. Modifying a column: which phrase will do what I want? ALTER COLUMN, CHANGE COLUMN, or MODIFY COLUMN? I can never remember and have to look them up.



yeah, mysql is annoying ...

duff on 2007-03-30T04:13:54

I believe your example can be written thusly though:

SELECT func(foo) as alias FROM baz HAVING alias='quux'

Re:yeah, mysql is annoying ...

fansipans on 2007-03-30T12:30:47

Which is still horrible because HAVING operates after a GROUP BY operation ... in fact I wonder if HAVING without a GROUP BY would give errors with other databases (SQLite 2.8.17 gives an error for example)