In fairy tales, most notably all those children's stories we grew up with, magic has a particular form that I've never seen emulated in any roleplaying game's magic system even close to satisfactorily. It's easiest to explain with examples.
In the filk song "Snow Magic" (which is what made me think of this), one of the protagonists is a wolf-witch. Wolf is her native form, but she knows enough magic to be able to transform herself into a human form some of the time, in which form she is able to live a tranquil life with a human man with whom she has fallen in love (and for whom she wove the changing spell in the first place). But there are several limits, and inevitably, by the end of the song, they come into play. She can't stay a human past moonrise (so she's kind of an anti-werewolf), she can't leave the woods, and most importantly, if she kills any human the spell is ended forever.
A lot of the more familiar fairy tales, due to Disney's influence, share a common theme for this characteristic trait, the one thing that can break the spell: a gesture of love, often a kiss. The frog prince, Sleeping Beauty, the little mermaid, even in Swan Lake it's something similar. But in the pool of fairy tales in general, including the well-known tales of Hans Christian Andersen and the Brothers Grimm, and even more so hearkening back to the original myths of the fae, there are other examples of some almost-impossible task which would break a spell, which can be strange and arbitrary, and have no obvious connection to the spell itself. (I'm having a harder time coming up with really compelling examples right now than I expected, though. I'll start noting them as they occur to me and add them in comments to this post.)
In roleplaying games these kinds of spells only ever exist as something NPCs do. I would really like to see a magic system that allowed a player to come up with some kind of elaborate spell which had these kinds of arbitrary limitations, without it being so eminently abusable that even the discretion of your more restrained players was enough to keep things in balance. Are there any games out there which attempt to reflect these kinds of spells?