In wallet loading, the NEED_RESCAN enum was only ever returned if the stored hash of a transaction did not match the calculated hash of the transaction. This would then trigger a rescan from genesis during loading, and then allow the wallet to be operational as if it were normal.
However, it seems incorrect to be treating such corruption as acceptable. That transaction would be inserted to mapWallet, but not be added to mapTxSpends or any of the conflict tracking or to TXO caching. Furthermore, the rescan doesn't actually fix the problem, the incorrect record would persist, which means that the wallet would be rescanning the entire chain every time it is loaded. The only situation this could occur is if the wallet actually experienced corruption, so it's reasonable to return CORRUPT instead and force the user to load a backup or otherwise un-corrupt the wallet before it can be used.
Implements the idea from #35501 (review)