This is working as expected.
Efficient, and correct sorting of 3d transparency in real time rendering is still an unsolved problem. There have been a number of attempts to fix this or mitigate it, but they all come with a fairly significant performance hit.
The way any single model is rendered is each polygon of a model is rendered in the order they exist in the model’s definition. In the case of opaque or cutout each polygon writes to the depth buffer. Each new polygon drawn will test against the depth buffer and skip those pixels that are occluded.
With transparent stuff (both fade and transparent) the polygons test against the depth buffer but do not write to the depth buffer as doing so may prevent other transparent objects from rendering behind it, but it also means sorting in model can be wrong. Just enabling depth write won’t entirely fix it either. Anything that gets rendered out of order with opaque stuff just gets overwritten by the polygons that draw later, but with transparent stuff it’s blended over what’s already rendered.
Besides, what you want isn’t actually correctly sorted transparency. You want it to look like it does when opaque, but transparent. With correctly sorted transparency when it starts to fade you’d still see “inside” the model as it fades out.
There are three ways around this.
One is a depth write pre-pass which renders all of the model’s geometry to the depth buffer once before rendering the transparency. There are basic examples of in Unity’s documentation here. I haven’t seen someone post a version of the standard shader with this technique, though one might exist on the asset store someplace.
Note: Having transparent objects write to the depth buffer can have other consequences, like other transparent objects not rendering behind it!
Another option is to render your character opaque to an offscreen buffer and composite it back into the scene as a sprite. This is complicated and kind of slow, but does give a lot more control.
Another is to pre-sort the polygons in your model. This one is harder as most 3d modelling programs don’t easily expose a way to reorder the polygons, and there’s no guarantee on export or import the order will stay constant. If your model needs to animate you’re also guaranteed that the order won’t be correct for anything but a single pose. It’s still not perfect either in the best case as any polygons that intersect will still be wrong and you can only pre-sort for one direction. So really the only option is the above depth write pre-pass.
As for why the “Transparent” setting for the standard shader still renders something, this is also working as expected. This is the specular. Something like clear optical glass is almost perfectly transparent, but will still have specular highlights. With Unity’s standard shader even with a smoothness of zero there’s still a little bit of very diffuse specular gloss which is what you’re seeing. This is why the “Fade” setting exists.