This is an interesting and perennial topic with opinions, as noted above, divided. I'm definitely on the side of the CP fans and have posted some photos previously:
New Zealand Lake Wanaka Circular Polarisation
Those images from New Zealand rely on the 90 degree effect, as explained by Steaphany, and were shot when the Sun had just risen, again maximising the effect.
In my view, such filters can be very useful indeed, and I don't subscribe to Colin's view that altering exposure can lead to the same or similar effects. One application that has not been discussed is the use in reducing glare or reflections on water. The matter came up on my recent trip to Northern Cyprus and I illustrated the point to my companions with the following two shots. (I make no pretence to artistic merit in these.)
This first shot has no polarisation filter. The sea is very much as the eye sees it - more or less opaque.
With this shot the CP filter is in use. Now details under the surface of the sea have appeared. The effect is independent of the position of the sun because the glare is from the sea. I think this latter shot has more interest than the former and I don't think that changing exposures would make any difference.
Thus, to me polarisation filters are an essential part of my kit.
Cheers
David