I replaced my water pump with a "new" (not rebuilt or re-manufactured) Murray brand pump from O'Reilly's. Replaced it in August of 2023 and have put 23,000 miles on it so far. Lifetime warranty and have no issues with it at all.
As for the warranty, I used O'reillys store brand fuel pumps in my old Ram, replaced it 3 times under warranty and never had a problem getting it replaced. The last time I replaced it, the pump was still good, but I had to replace my fuel tank (due to getting it punctured on the freeway by a piece of pipe that got kicked up by another car) and they just gave me a new one. Some stores are better than others on warranty replacements.