Majora's Mask and Mario 3D World both scale up beautifully with their timeless character model and texture designs (and really well implemented lighting given the complexities of the scenes at hand).
New Super Mario Bros. 2 is an example of when more resolution isn't better - all you get is a horribly upscaled 240p image of 2D assets with a comically out of place high res Mario model - a model which looked better in 240p when it was consistent with the background elements.
Yes, but the games themselves would've been designed for the original hardware. Sometimes games include much higher quality assets despite the platform not being able to take advantage of them, which is why sometimes you get miracles like Final Fantasy Type-0 and Kingdom Hearts: Birth By Sleep on PSP looking way better in emulators. But most of the time, the assets are going to be downsized only to what's necessary to make the game work. At some point, it doesn't matter how much processing power you throw at a game, the source assets will be the limiting factor. The more advanced the original platform, the better the assets will be on average and the higher the ceiling will be for image/rendering quality in future emulators.
This. A lot of the time games targeting modest hardware are designed for its limitations, so in the case of DS games the 3D has been made to look great at 192p.
The games which have higher quality assets look great when upscaled, but otherwise I higher resolution often exposes low poly character models and textures designed to be pin-sharp at 192p. Both of which are compounded when you consider the flat lighting in many a DS game.
As mentioned there are games which do include assets that sing at higher resolutions. In that case a higher resolution can bring out extra detail, but again it depends on whether the end image looks consistent or not. I think a lot of later Wii games made a good fit for this though.
I'd even argue that with 2D content, pixel doubling isn't always a good thing either, since it accentuates each pixel. With 1:1 pixel mapping the gaps between pixels help smooth out pixel art, whereas with pixel doubling, 4 much smaller pixels are making up one individual pixel, so there's no gap between pixels. This is why pixel doubled content (e.g. play a PSP game on Vita) often looks lower res than the original even though it theoretically should look the same.