I am answering my own question here:
This problem occurs because when called in Application_OnStart, MapPath incorrectly includes the context of the page that caused the application to startup. If the first ASP page to be run when the application isn't yet started is not in the root of the virtual root then MapPath gets confused and adds the path to the called ASP page to the path it returns.
So for example if the page that started the app was in "c:\inetpub\wwwroot\testapp\folder1\folder2\test.asp" then MapPath would incorrectly add "\folder1\folder2" into the middle of the path and return "c:\inetpub\wwwroot\testapp\folder1\folder2\xsl\transform.xsl"
If your website only has files in the root folder or doesn't use MapPath in global.asa then you will never notice this little oddity. I suspect there are lots of ASP Classic sites out there that fail to startup properly sometimes because of this, but their owners just do a quick iisreset, not knowing what quite went wrong.
The result of this is that you can't reliably use MapPath in global.asa if you have a website that has ASP files anywhere other than just the root folder.
If it is a one-off website then the easiest solution is to just hard code any paths you use in global.asa.
If you sell a product to other people based on ASP Classic then hard coding the paths is not an option. You either have to move all usage of MapPath out of the application startup or deal with the issue by writing paths into your ASP files as part of the installer.