The auto-white balance algorithms in most cameras handle some situations very poorly. As with metering, the camera can't really know how the scene is supposed to look; it can just guess. Or rather, not even guess, but apply a simple algorithm. The one used in Gimp is like this:
- discard the outliers — the far extremes — in each color channel
- stretches each color channel as far as possible to the edges
That's not very smart, but tends to work surprisingly well. The auto-WB algorithms in cameras are probably more sophisticated but I expect they use the same basic idea.
So, in some situations, the camera can get it really, really wrong.
- If the colors in the actual object are strongly shifted in one channel, that can introduce a cast in the opposite direction.
- For some reason, auto-WB generally works best under hotter color temperatures, and breaks down with the cooler temperatures of warm-colored * incandescent lighting.
- Fluorescent lights tend to have very odd, spiky spectrum, with very specific color peaks depending on the gasses used in the tube. This can create weird green and magenta casts.
In these cases, setting the white balance manually can help. However, they can be too strong, and produce casts that you didn't mean. (One can actually use that for artistic effect.) Setting the WB manually against a gray card (or something neutral in the scene, in a pinch) is ideal.
Another important use is when there's mixed lighting and you need one part of the scene to be right. This is a hard situation where it's impossible to do the right thing across the frame, and so it's nicer to be in control of the decision yourself rather than doing whatever the camera works out.
My camera (the Pentax K-7) happens to have one of the best auto-WB systems available. It's amazingly good. It may be because it has an actual-light-color sensor used in autofocusing — it's unclear whether this is used in WB or not, but it seems like it might be. Either way, auto-WB generally works so I well that I don't have to think about it.
However, sometimes it goes wrong. As I just answered in another question, this is one of the reasons I shoot JPEG + RAW. Then I can go back and fix anything that didn't come out right. Generally, I do this right in camera later.
* oh, that's confusing, I know — high color temperature is of course more blue and less yellow, whereas the opposite is used traditionally to describe the feeling of different hues. Red, orange, yellow are warm colors, but a low color temperature.