Apple hashes all of your photos offline and then pinky promises to only check the hashes against the official on phone database when the user initiated an upload. The problem isn’t about wackos it’s about governments forcing Apple to do things with this new weapon
If Apple really is trying to sneak in a CSAM database on your phone with iCloud disabled, someone WILL catch it and raise so much hell we'll all hear it.
If Apple were going to lie about this process, they didn't need to announce it and go into so much detail at all. They could have just kept it quiet the way the current server side CSAM scanning is done by others already. The legal and market impacts of Apple lying would be severe.
I haven't seen a single person concerned about Apple scanning photos in iCloud. The problem is entirely that the scan is happening on your personal phone with apparently some janky implementation that in one week has already shown to have serious flaws.