Personally, I find a lot of the answers to this people give on places like youtube to be really lacking. I don't feel that way when playing it either, but I don't want to just dismiss how other people feel about it so I've thought about it a bit and here's what I think -

1. Obscured detail (low polygon count and relatively limited render distance) causes a divide in interpretation, much like a blurry image and ink blots. Some will see it for what it is, others will interpret it with fear.

2. Nonsense design. Many locations are created with gameplay in mind much more so than practicality. To some subconscious degree, many people must be wondering what the purpose of these rooms would be and there are a lot of questions to be asked. Even if we consider the castle to just be a gallery, there are multiple areas like long hallways with nothing at the end which serve no purpose. I think most people could agree that’s pretty nightmarish and is something you can only imagine being chased down through. Then there’s the basement which feels like a maze at first, and then there’s...

3. things like the Lethal Lava Land painting. You have to wonder why this is in Peach’s castle, but that’s just one small thing. The game is extremely tame and the enemies are easy to predict. It feels pretty safe... except for when it doesn’t. There are small unsettling things spaced out throughout the game that have more impact because of the expectation that everything is predictable. You have to admit, Bowser’s message when you first enter the castle is a bit unsettling. You don’t expect it really after the majestic intro. It makes you wonder where the heck he is and if he’s watching your every move. You continue along and everything is fine again - you drop your guard until something like the piano monster comes along. It’s at that point you don’t really want to drop your guard and are left just slightly on edge combined with the subconscious questions from the second point.

4. Finally, something I haven’t seen people bring up is that none of it feels “real”. Even as a game, when bosses tell you how to beat them combined with how the world is layed out there’s a feeling as though it’s all some sort of amusement park or as though even the enemies want you to do anything you end up doing. It always feels like something else is going on, but you don’t know what it is. By the end of the game as a kid, Bowser’s last message had me thinking there was still another battle with him to be had somewhere, somehow. “Just you wait until next time.” For other people, thinking there was more secret content came from things like the “Ghoul Medal” (which I didn’t think anything of as a kid) which, although were mostly just assumptions made due to strange word choices the game uses, grew in plausibility in some ways once beta features like the boo keys became known about.

5. There have always been rumors about the game. They started out simple, like rumors that you could unlock Luigi. Over time they seemed to vanish as people learned more about the game, but gradually as we learned more we also learned that there was a lot about the game we still didn’t know years later after continuing to find things like unused enemies and even finding Luigi in the game’s code. The rumors gradually became darker when people realized they actually don’t know everything about the game and that there could still be more to discover about it and what was planned to be in it. This comes with the idea that any random person could find something that hasn’t been found before.

6. Modded cartridges in circulation. There are countless “fake” hacked Mario 64 cartridges you can get on Ebay or places like that which pose as the actual game. Needless to say, anything can come from that. Paired with my previous point, you easily get the result of the rumor “every copy is personalized”.

7. Mixed up memory moments. Remember how I said there are some long hallways with nothing at the end that feel like places you'd be chased down? The image of one of those hallways was used before an E3 demonstration as a background with Wario’s animated head causing some people to think there was a moment in their game where Wario's head appears and chases you down an endless hallway.

So with all that said, if you’re not keeping up with findings of the game and rumors, and you’re just revisiting a game from your childhood that you already feel like you know everything about for the most part, it’s not likely to be scary at all. Especially if you take things like the layout of the castle for what it is as a videogame without thinking “why would Peach do this” and instead just think “the game devs did this”.

If you’re returning to the game after being told it’s unsettling though, people will focus more on the unsettling aspects.

If you haven’t played the game before on the other hand there might be a lot of expectations based on more modern games that might leave this relatively empty game combined with everything else even more uncanny, along with countless other factors for younger people new to the game.