The WAV format has support for Loop Points, which are metadata encoded in the file that defines start and end positions for loops, and whether to loop forward, backward, or pingpong (back and forth continuously).
This “Detect From WAV” option lets the WAV importer read and process these loop points if they exist. So it depends on the WAV file you have and whether it contains loop points.
The other modes let you define manually what loop type you want, in case it’s not included in the WAV file’s loop points, or you don’t want to respect those loop points (they’re not so well documented and there’s not a lot of software that lets you set/change them). So you can specify e.g. Forward looping and the start and end positions manually.
This is handled here in the importer source code: https://github.com/godotengine/godot/blob/ea47e03b360d0aeee10c1f7ef798f5750c2b1aa8/editor/import/resource_importer_wav.cpp#L263-L294
Here’s some reference on the encoding of loop points: https://sites.google.com/site/musicgapi/technical-documents/wav-file-format#smpl
And this open source software (among others) can let you see and change loop points: https://loopauditioneer.sourceforge.io/