Let’s suppose we have a simple posts table and a view, corresponding to the posts of the current week (let’s keep values hardcoded, for the sake of simplicity):

create table posts (
  id integer not null auto_increment,
  title varchar(128) not null,
  content text not null,
  submit_time datetime not null,
  primary key (id),
  key (submit_time)
);

create view posts_this_week as
  select *
    from posts
    where submit_time >= '2007-10-21 00:00:00'
      and submit_time < '2007-10-28 00:00:00';

So, say, you want to add an extra field to the posts table:

alter table posts
  add last_edit_time datetime not null;

You expect the field to be fetched by view as well, right? Wrong. In MySQL, the list of the fields to be fetched by a view is made up on view definition, rather than once the view used.

Often, such behaviour can lead to wrong assumptions (much due to classical explanation of views as query aliases), such as: server being out-of-sync when using replication, thus the table not containing the new field. So, when designing your application, keep in mind, that views, which should contain all fields from the table, have to be redifened every time table structure is altered.

One of MySQL’s restriction when using views is that they cannot refer to local variables. So, for example, this statement will produce an error:

create view thematical_books as
select title
     , author
  from books
 where subject = @book_subject

A workaround for this limitation is quite simple: to use a function which returns variable value. For example:

create function book_subject()
returns varchar(64) as
return @book_subject;

create view thematical_books as
select title
     , author
  from books
 where subject = book_subject()

After reading one more post on SitePoint forums about selecting not aggregated values of columns, which were not specified in the GROUP BY clause, I decided it is worth to highlight in my blog what is wrong with queries that make use of this approach.

At the start, I’d like to mention such queries are allowed only in MySQL, which is known for its unstrict behaviour. Other database servers with produce errors in such case.

One of the most common reasoning for such queries, for example:

select user_id
     , post_time
  from posts
group
    by user_id

is the wrong assumption that query will return the most recent entry for ungrouped columns (latest post time for each user in this case).

As MySQL manual says, returned value for ungrouped column can be any value from the group it belongs to. So the query above, is free to return time of user’s any post.

A solution for such cases is to use an aggregate function:

select user_id
     , max(post_time)
  from posts
group
    by user_id

This will guarant that returned post time for each group is the latest within the group.

Although, MySQL manual states that in case you are sure that values within a group are constant, using columns in SELECT (or HAVING), but not in GROUP BY can give performance increase by avoiding unnecessary grouping, I highly suggest to keep away from this technique as it is not standard (supported by MySQL only), and it makes behaviour of queries unclear, increasing possibility of queries working not as expected.