After biting the bullet and purchasing a code signing certificate, I can now answer my own question with all certainty:
Short answer: No, the warnings don't go on OS X - although they do in web browsers.
Long(er) answer: Technically certificates from Comodo or any other certificate authority that is trusted do work on Mac and you can absolutely sign .apps with them, however how useful this is another matter. I suppose that for more advanced users it is still beneficial in that you can verify whether or not an application has been tampered with, but Apple still won't see it as being from a trusted developer - and that's where the confusion starts.
The warnings that I mentioned in my original question originate from Gatekeeper. Now, if I would have known that I wouldn't have needed to start this question but I guess if I didn't know other people won't either. Anyway Gatekeeper is something that Apple have designed to keep Mac's safe and to avoid unwanted, malicious software from entering the system. It does this by restricting what apps can be executed based upon their origin, and by default in OS X Mountain Lion and later Gatekeeper only allows the execution of Apps from the Mac App Store and trusted developers.
To become a trusted developer however, it takes more than just signing code with any old certificate. Trusted developers have Developer ID's, given to them by Apple, and their code signing certificate contains this Developer ID; again the only certificates to be able to hold a Developer ID are those issued by Apple.
So, when people tell you that Apple only trust themselves, that's not true. They trust certificates issued by most certificate authorities just not in the same way as Windows and signing you app does not mean you're a trusted developer. For that, you have to pay Apple $99/year.
In all fairness, you do get a lot for that $99 and it does make some sense for it to be this way. It's frustrating for developers, but safer for users! Users can bypass Gatekeeper for individual apps, so if you're only planning on releasing one app for OS X or something then perhaps providing instructions on how to this might be a better option for you.
PS: After a lot of emails to the Comodo support team, I finally received this statement from them to confirm all of this:
While our certificates work perfectly fine with signing .app files, it
does not give you an Apple Developer ID to designate that you are an
Identified Developer by Apple, as only Apple provides developer IDs
for Mac and iOS applications.
Best Answer
When you sign an app using a code signing certificate, you are establishing an end date for that app (in terms of it having an unexpired cert).
You will not be affected directly (except hopefully for getting warnings that you are using an expired cert) but your users will notice when the expiration passes and they will not get a warning beforehand. One day it will break, precisely when their clock passes the date of expiration. It will still be signed of course, so you may want to prepare your customers to expect that the trust the OS extends to your your app will expire. They can choose to keep running the app by disabling the checks or stripping the code signing. You might also to supply users with newer versions of the same app that are re-signed should they want he same exact binary but simply extend the signing for a different time range.