The majority of these discussions are starting in the wrong place, I think. Instead of asking whether this specific feature is good or bad, we should start by asking whether Apple should be doing anything to try to stop the sexual exploitation of children.
If the answer is "no, it's not Apple's job." then of course these plans are terrible.
If the answer is "yes, Apple should try to use it's immense power and wealth to try to stop a terrible evil" - then the obvious follow up question is "How?".
They could do whatever ever other company is doing and just looking at all of your data, but Apple didn't want to do that. So they came up with a system that they felt was a good compromise between privacy and trying to stop the spread of CSAM. If you want to quibble about the implementation details, that's fine. But if your position is that they shouldn't be looking at your data at all, then you have to go back to the first question and say "Apple should not be trying to stop the exploitation of children."
Now you might say, "Yes, but 'what about the children?' has been used to justify terrible things." Granted. But... "what about the children?" is a very important question to ask and shouldn't be used as a cliche to dismiss Apple's attempt to make the world a better place in this area.