A new exhibition shows how paparazzi photography inspires mixed feelings – and some truly great works of art.