I dunno, seems way too difficult to do for it to be intentional. This would assume that they predicted players could make it up there, but ONLY by climbing the ledge and ONLY at that one teeny tiny point. Why include the teeny tiny point condition? A novice programmer could code Mario to die if he climbed the ledge with fewer than 120 stars at all. All you'd have to do is have the game check for the following conditions:
-Mario is above a certain altitude, slightly below the lowest ledge of the castle roof but above any that the player is supposed to be able to reach without 120 stars
-Mario does not have 120 stars
-Mario grabs a ledge, the climbing animation ends, whatever
If all are true, Mario dies.
But why even bother doing that at all? You could just put an invisible ceiling below the lowest part of the roof that disappears when Mario has 120 stars. Persistent players who understood game design might get a mixed message that getting to the castle roof is actually impossible and not ever try using the cannon, but it wouldn't be as bad as if the intention were to kill Mario for making it up there on jumping skill alone.
I don't know everything about how Mario 64 works, but looking at the video, here's my guess: Mario's climb animation goes just far enough to push him through the invisible wall, but there's more of the floor collision on the other side, so he doesn't fall. However, the only time you're supposed to be outside of the boundaries of the map is when you're falling off the world, which results in instant death. Mario doesn't display a death animation or even lose any health because there's no call for it--if you're falling forever, all you get is the cut to black, and the game spits Mario back out where it would had he died any other way.
So that's what I think, going by the logic of game developers and how the world and the player character interact with each other, and the only preventative measure to making it to the roof was to make the castle really tall. If anyone ever does some more digging to verify anything about why the death occurs, it'd be cool to see if I were close.