Your "logical disconnect" is in thinking a Perception check is required to hear and be awakened by a gun shot going off next to your head. Why on earth would it require a Perception check to hear any loud or even normal level of noise within hearing range? In the games for which I DM, hearing any such noise is automatic, and a sleeping creature is nearly always awakened by a loud noise. So, while in the situation you describe, there would be no chance of you hearing and being awakened by the person sneaking into your room, the gunshot would result in your immediately regaining full consciousness, no check required. Does that clear things up?
You base your ruling on what it says for the Unconscious condition:-
An unconscious creature is incapacitated (see the condition), can’t move or speak, and is unaware of its surroundings
So you rule that,
since a sleeping creature is unconscious and unconscious creatures are 'unaware of their surroundings', that sleeping creatures cannot hear anything.
Is that your ruling? If it is, then sleeping creatures simply cannot hear the gun so the loud noise cannot awaken them because it cannot be heard by sleeping creatures.
If that is not your ruling, if you rule that sleeping creatures
can be awoken by sounds, that makes sense because if something wakes you up then you are no longer unconscious.
That leaves the question: how loud does the sound have to be to awaken a sleeper?
The DM can certainly rule that a noise is so loud that it will auto-wake a non-deaf sleeper.
The DM can certainly rule that a noise is so quiet that no sleeper could possibly hear it.
But, this being an adventure game and all, there will be plenty of situations where a noise
might awaken a sleeper, or it might not. It would depend on how quiet the noise (Stealth check for creature trying to be quiet) AND how sharp the hearing of the sleeper (Perception check).
It must also be admitted that creatures are less alert when they are asleep, all things being equal, than when they are awake. Therefore, sleeping creatures have disadvantage on their Perception checks to find out whether they are awoken by a noise.
In real life I have slept through some rackets in my time, but I have also been awoken by someone trying to be stealthy. My friend snuck into my room to borrow a book because I work nights and he didn't want to wake me. I was fast asleep. He tried to quietly pull the book from a pile (think Jenga) but that *slide thunk* woke me up even though it was quiet. He was annoyed because he thought he was stealthy enough to go through the whole book-borrowing process without waking me. I was annoyed because I thought I'd wake up as soon as anyone came into my room.
If this was 5E, his Stealth beat my Perception when he opened my door and came up the steps, but my Perception beat his Stealth when he tried to silently slide the book out. From a game perspective, everything is working fine.
What would
not be fine is if the DM ruled that sleeping creatures can
never be woken by someone simply
trying to be sneaky! Especially on the spurious grounds that sleeping creatures cannot hear, and the next second saying that creatures that cannot hear
can hear gunshots!
Let them roll. Give disadvantage to the sleeper, maybe even advantage to the sneaker too, but for Gygax's sake
let them roll!
Nothing is less fun than saying your PC is going to sleep and then the DM just announcing that you got killed in the night without any rolls at all.