When Retrieval Succeeds and Fails: Rethinking Retrieval-Augmented Generation for LLMs