AFAIK when you enable the detect duplicate, The behavior will be in the following way.
Touch UI : when you try to upload and then try to Create Version/Replace the asset which is already present. It will populate a popup notating same.
Classic UI : It will end up throwing an error with out asking such as Touch UI popup as shown below.
I guess Adobe has concentrated only on the Touch UI implementation.

When you upload the asset to aem it will create a lot properties for the DAM Assets, one of the property is dam:sha1
, The detect duplicate logic will work based on this property value data.
And to get the list of duplicate assets paths you can use a XPATH query
//element(*, dam:Asset)[(jcr:content/metadata/@dam:sha1 =
'ff546dd8055fcfca53471dfa6fe0494c4198e965')]
Where in the "ff546dd8055fcfca53471dfa6fe0494c4198e965" value is of my image dam:sha1
property value. find the below screenshot for reference.