That reminds me another older horror game, which fits in this thread perfectly:

Realms of the Haunting (also available on GOG).

Back when I played it, the beginning level was very suspenseful, and I was actually terrified to go further in the game, opening any doors, etc.

Then you get hold of some weapons... and the rest of the game is pretty much just a generic shooter where you shoot at monsters. The suspense was pretty much lost when you could kill the baddies by just shooting at them.

I think I had a bit similar experience with Undying, it also seemed quite suspenseful at start, but then became more like a generic shooter. However, I think this contrast was even higher in Realms of the Haunting.

Evil Genius: Great premise, got me into it. Made me buy it. But then you're whacked over the head with ever-escalating heat management and body disposal unfun.
Zoo Tycoon: A micro-manager/chart-reader's wet dream. Totally not fun actually to try playing once you get past the start-up.
Legend of Keepers: Well-crafted to get started, quite polished to go through. Until you realize you win or lose by RNG that does not have sufficient mitigation to cope with (unlike good "roguelikes" like FTL, Slay, or Monster). Numerous games in this genre get this: either not enough mitigation/control to make it good enough, or a play goes too long. Convoy is another one. As was Renowned Explorers.
Starbound: mentioned above. Terraria: Same. And then pausing issues!
Block'hood and Urbek: They both start fun but rapidly devolve into "perfection required". Urbek stays longer (long enough I got my money's worth), but even there eventually "keep rebuilding over stuff to unlock new upgrades" became too precise and the numbers grew too much so it became repetitive.
Ni No Kuni Remaster: Almost all the content they added in the remaster made the game too long and just frustrating grind (on top an already pretty grind-heavy game).
Final Fantasy 8: First disc is great! And then there is more and the game mechanics and story don't support it, and the mini-games get broken too.
Final Fantasy 10-2: I truly love this game, and it probably doesn't belong on the list... but after a while you realize the enemies are bland packets of hit points with few to no abilities, and you get leveled enough that it's just "hold attack to win". Before that point, the game is stellar. After that, it becomes merely "good enough to keep playing".
Age of Wonders 3: Eventually missions become long slogs unless you use rush tactics.
Far Cry 2: "What do you mean there's another whole region down here? That should have opened up end-game, not another entire map!"

Strong disagrees with Windwaker (which was the last good Zelda, probably ever) and HZD above. :) You can say that again! Realm Grinder is a perfect example here. It "requires constant attention and clicking after a while" instead of "you can click and distribute things and otherwise leave it running".

Xenogears

Strong disagree with that. With the possible exception of Majora's Mask and the Oracle games (particularly Seasons), I could argue that Link to the Past was the last good Zelda game.

Even then, I find that I can't really enjoy LttP that much if I try to play the game as intended; it's one game where I actually need glitches to really enjoy. With Zelda 2, I don't have that issue. Zelda 1 I haven't played in a while, but for me I think the situation is that I don't enjoy the first quest, but I do enjoy the second.

Ultima: Underworld 2

I like it. I find it difficult without being impossible. But the entire underworld felt so much better in the first game, than the second. Mainly due to the developers seemingly having no idea what to do for world creation. The dungeons didn't feel like dungeons anymore except when you first begin. It trails off into other worlds that just felt like a fetch quest with a different view perspective.


Another game very similar;

Arx Fatalis

Pretty much does the same thing, except a lot quicker. You feel like the dungeons are alright. But then the puzzles inflicted upon this poor game were what killed it. It goes from logical to some degree, immediately to puzzles. You find yourself asking "Why would anyone make a puzzle???". The realism goes down the toilet, because nobody would make puzzles.
It would be like exploring the Egyptian pyramids for the first time and Oh look a puzzle to unlock a goblet, required to fetch a key, to a room that will mystify you with ancient treasure.
....as opposed to a tomb with no puzzles at all and a bunch of neat nooks and crannies to explore. The logic of the locales just dies.

I could also add a whole bunch of NES games here that start of rather fun, but end up 'Nintendo hard' after not too long and just become frustrating. A few include:
* Solomon's Key (Great puzzle game. But then later levels become run/jump/timing. And weird meta-puzzles.)
* Mighty Bomb Jack (Similar as previous, though more actiony, less puzzly to start. Same progression though.)
* Wrecking Crew (Also same description...)
* Solstice (Once again...)

Hm, pattern? These aren't games that "start" 'Nintendo hard' and are just no good the whole way through (like Simpsons: Bart vs Space Aliens), or are 'Nintendo hard' and you can just persevere and are actually good games (like Ninja Gaiden), but are ones that fit this thread.

Maybe I'm a cur, but I feel that applies to the entire Xeno___ series, since the Xenoblade series goes in this baffling single player MMO direction.

There is one game that aggressively comes to mind and that is Baldur's Gate: Dark Alliance.

Great beginning, the city is great with the music and the quest givers, best/most pleasant looking sewers in video games history, Tony Jay voice acting the beholder, and of course the "wonderment" of building a new character.

After Chapter 1 is done, get ready for bland hack n slash against hordes of enemies in environments that overstay their welcome with no NPCs besides the shopkeepers. Hope you like watching your numbers go up.

Lionheart. First chapter is one of the best RPG pieces of it's time but later 75% of the game is boring linear hack & slash.

Original Rise of the Triad springs to mind. Incredible shareware episode then in the retail game there's 4 episodes that get worse as new villains are added (enforcers, robots, monks)

The only one I can think of at the moment that I've played would probably be Spyro Enter the Dragonfly for PS2. One of the first PS2 games I played when I got my first one as a kid and was super stoked. Love the first three for PS1. Started up the game, had fun exploring the main hub area, a bit choppy, but didn't mind. Went to the first world. Waiting for the loading screen to finish.

Waiting.
Waiting.
Still waiting.
Still..... waiting.
Ok, the level loaded. That was.... a long load time. Play through the level, it's nice. Reach the end. Go through the portal. Wait for loading screen to finish.
Waiting.
Waitng.
Still waiting.
.....
Hmmmm. This.... may be an issue.

And yeah, every time you enter a world/portal, literally the longest load times. It was... unbearable.

Zelda: Twilight Princess

A game I actually put 40+ hours into just to see where it went. But the entire game felt like one big endless slog to get to one puzzle to the next. It really felt more like work than a gaming experience. The characters were hollow and felt meant for someone to ignore. I thought in the beginning it could be a good game for young people. However, the part that I could not ignore, was how the game made me explicitly ignore and just not care about any other character in the game, save the main player character. It occured to me the game felt like narcissism in puzzle game form. I think I will pass on any other Zelda title when this sort of vapid game deaign is so popular. It speaks volumes about the people that made it and perhaps the people that get excited to play it.

For context one could look at the film The Hobbit(live action). How rushed everything felt to just move 3 hours with a bulldozer and all the characters just felt indifferent toward eachother, because not even great actors could make it believable as it was portrayed.

Sometimes I feel like, in Zelda games starting with Link's Awakening, it can be a slog to get from one dungeon to the next, as the dungeons really are the fun part.

(Then again, Majora's Mask, which has only 4 dungeons, is actually my favorite 3D Zelda. That doesn't mean the game is without its issues; in particular, it starts slow and confined, and there's a bunch of cutscenes and even a timewall before your first opportunity to save.)

Kingdoms of Amalur starts out fun, every time. But the game gets repetitive around the 2/3rd mark. I finally finished it the last time I played it, but damn. That last 3rd of the game is such a slog.