When can Self Resonance of a capacitor be a bad thing in bypass and decoupling applications?
An ideal capacitor has an impedance that falls with increasing frequency, which is good for decoupling high-frequency noise.

However, real capacitors have some amount of parasitic inductance, which appears in series with the capacitance, forming a series-resonant circuit.

Such a circuit has a minimum impedance at its resonant frequency, and at frequencies higher than that, the impedance starts rising again, which is less useful for decoupling.

That's why it's sometimes useful to use a number of different capacitors to decouple wide-bandwidth applications; each one provides the low impedance required for a particular band of frequencies.

But beware of strange cross-resonant effects! Sometimes the capacitance of one capacitor will interact with the inductance of another capacitor to create a parallel-resonant circuit, which has a very high impdeance at its resonant frequency. Verify your implementation with a wide-band network analyzer.