As I said, this isn't a Camera Problem, but a problem with the Upvector/Lookat.
So whatever rig you build, you can run into the same issues.
I think the main Problem is that the object rotation is not replaced by the rotation calculated by constraint which happens somewhere in the background, then added as marko said to the standard rotations which for some reason may get altered by the transform and the resulting rotations which leads to wrong calculation of the overall result´and so on. This is also the reason why the transform handle won't reflect the actual rotation when aligned to object.
The error is in the system. It is unreliable and unpredictable. If something shows up in a software shouldn't it be fixed instead of forcing the users to build a workaround? After all this works in literally any other 3d software.
I mean the solution can not be “It doesn't work, so I don't use it”, can it?
I started building a rig, but the time required to build a suitable rig that behaves like a standard target camera like in max, maya, softimage or even blender can better be used to animate the camera in, well, not houdini.