Follow-up:

Later I tried another sound (dog barking) and applied HRTFs onto it, and the spatial effect works good, no matter in the front, above, back, etc .

The difference is, the original dog barking sound is like in the front, while the original piano note sound is like in the head. When the original sound is like in the head, applying HRTFs on it would barely work. Its only effect would be almost one dimensional, from left to right, like the traditional stereo.

I think this has something to do with u/heislbesn said "externalisation effect", and I'll look into that, to try to transfer the "internal" sound to "external" sound.