A new exhibition shows how paparazzi photography inspires mixed feelings – and some truly great works of art. Alastair Sooke explains the allure.