This is a difficult area, all of the solutions I tried here have drawbacks which could be considered bugs. From testing the patch I don't find either current or new behavior very intuitive, although the behavior in master is worse in the case of an exactly 90degree rotated horizon.

A difference between this patch and master is this patch maintains the tilted horizon, the code in master rotates into a horizontal horizon. A simple example to show this is

An issue with the more intuitive options is they maintain tilted horizon, causing the user to be "stuck" in a state the orbit mode can't control very usefully.

Unless users have a strong preference for different behavior, we could close T98338, document it as a known limitation of turntable rotation.
If new behavior is preferred I think it should still be possible to rotate out of a Z-aligned view with a horizontal horizon.

I have tested the differential version and think that this isn't quite how I imagined the solution to look like.

I think you think too complicated about this ;-)
In the differential version the behevior of the viewport is different to the master version even though the viewport hasn't been rolled yet.
Thus issues like where orbiting out of this top view gives a horizontal horizon. With this patch it keeps the 90degree horizon. arise.
The thing is that the turntable implementation in the master version is almost there just a small fix is needed when the viewport is rolled.
Unfortunately I am not able to implement the code myself but hope this visual representation helps to clarifiy what I mean:

Visual representation:

In the visual representation above the two rings represent the path the viewport camera takes when it is rotated left/right and up/down.
To see how the viewport camera moves follow these steps:
Go to the Master version setups.
First frame one of the suzannes(monkey head) using the period key.
Split your viewport into two such that you have two identical viewports.
Enable lock camera to view in one of the viewports and go into the camera view of the camera that belongs to the suzanne you framed.
Now you can orbit your camera with Numpad 2,4,6,8 and see how the camera behaves in the other viewport.

Hopefully you can now see the problem and solution.
Blender only rolls one of the axis while keeping the other fixed, thus causing gimbal lock among other problems.

If you have understood everything thus far you can see my proposed solution in the Blender file.
Basically my solution is just to roll the other axis with the viewport.
It is important to stress that this fix will not change viewport behavior in any way to the existing behavior as long as the viewport isn't rolled.

"If new behavior is preferred I think it should still be possible to rotate out of a Z-aligned view with a horizontal horizon."
Rejoice cause this fix does exactly that!

So in the end the problem is probably much easier than you have thought and due to the fact that such a minor change is needed to fix this I would definitely consider this to be a bug or developer oversight.

It is difficult to understand the intention just describing Does my new comment with the visual representation .blend file suffice or should I elaborate further?

Actually I thought of another way of getting the behavior of my proposed solutions:
Instead of rolling the viewport camera by e.g. 15 degrees, we roll the entire view by -15 degrees and keep the camera roll constant.
So basically we completely replace the current camera roll.
That would ensure that the viewport behavior for everything else stays the same.

But since I don't know whether or not this is even possible to implement or could lead to bugs further down the road I would prefer we try fixing the exisitng camera roll.
So this comment really is just to make aware that there is another possible way.

In D15028#405023, @Germano Cavalcante (mano-wii) wrote:

It is difficult to understand the intention just describing.
But seeing the .blend file, I don't know what I got wrong but the patch proposes exactly that.
(Wait for build update)

I think that intention is
The ability for turntable navigation to follow the viewport y-axis instead of the global z-axis when viewport rolling is used.

As a result of a discussion it was decided to make a better explanation of a problem from user perspective.

Link to the post