The algorithms never work 100% right. That means there will be always humans in the loop, sorting through false positives etc. which means probably very private pictures here in this context.
And to the end user it does not matter, whether this happens on the server or on their device. The message is: your pictures are not private with apple.
If that's too abstract, it matters in that it's a foothold for further scanning of data on your device. Without this system, if a state wanted to pressure Apple to create spyware and add it to their operating system to look for something else, that would have been easy to refuse. With this system, the spyware is already there, and despite Apple's protests to the contrary, it's just a matter of a few tweaks to repurpose it.
The concept of scanning for CSAM during the upload process, or the concept of making the user's device do it? Apple could do the former on their own servers and essentially nobody would be upset.