When does Hartree-Fock fundamentally fail?
Restricted HF (RHF) fails anytime you have partial occupancy due to (near) degeneracy of the HOMO. For example, $\ce{H2}$ dissociates to a mix of $\ce{2H}$ and $\ce{H- + H+}$ and the dissociation energy is way off. Similarly, singlet $\ce{O2}$ is not well described by RHF if you can get it to converge at all.

The solution is to use more than one MO wavefunction (determinant) and this is sometimes referred to as "non-dynamical" (rather than "strong") correlation.

Unrestricted HF (UHF) can usually treat these systems qualitatively correct.