There appears to be a bug with moods that when a strange mood dwarf tries to get an item but it is removed by another job first (specifically hauling) that the dwarf will refuse to get a new type of that item, and then fail to complete his work and go insane.
Here is a specific explanation of what happened:
I have a carpenter who has a mood and takes a carpentry shop. He picks up a piece of wood or two, then sits in his shop only demanding metal bars and wood. I check his profile and notice he likes bronze, and since I don't have any bronze bars made yet I order my metalsmiths to smelt some. After my metalsmiths decide that taking a break and sleeping is their highest priority, I get some random dwarf to make the bars.
And this is where the interesting thing happens. I complete the bars, and the mood carpenter takes about three steps out of the shop, then walks back in and starts sulking again. I look back to the shop and noticed that the bars are tasked to be hauled to the bar/block stockpile. However, even when the bars get to the stockpile and I make some more bronze bars, the carpenter refuses to move at all and fails to make his item.
I'm pretty sure he failed because of those bars since that was all he was asking for other than wood and he didn't like any other metals. I'm not sure if the problem is specific to hauling and stockpiles or in general other jobs taking precedence over moods and completely knocking off their ability to finish the job. This would explain the problems I have where my mood dwarves commonly go insane when they are asking for rough color when I don't have enough bins and stockpile space for all the gems I've found.