What you're describing is common for people with ADHD

There are tons of theories as to what actually causes ADHD but it is widely accepted that it's related to our brains either being structured slightly different or that our brain chemistry is different.

In my personal experience this has some interesting effects, such as the ones you mentioned. I can pop straight caffeine pills and go right to sleep yet if I take sleep aids I tend to feel more alert.

I'm not a medical professional so I won't even begin to theorize why this happens, I just know that I have experienced this phenomenon my entire life, interestingly enough though Nyquil and Zquil work incredibly well, as does melatonin.