The street also has that (well, a triangle "yield, no stop necessary" sign).

The thing is, in Europe, you have priorities in traffic - first the lights, when those are off/unavailable - the signs, and finally "the one on the right goes first".

The rationale behind this intersection is that you need all the vehicles obeying the same set of rules. So you can't have one car approaching the intersection obeying the signs (because no lights on that road), and another car obeying the traffic lights.

FWIW, police signalling manually > traffic light > traffic signs > "right before left"

In that case a flashing yellow right arrow would make more sense than a solid red light.

Don't quote me on this, but I think drivers are allowed to drive on a flashing yellow light without coming to a full stop, while this red light requires drivers to come to a stop.

Flashing yellow means yield, indeed. However, if the traffic lights are off the signs take precedence which in this case also just say yield. The red light with the green arrow is a hack in that it allows the intersection to work as intended, but the Stop part of it probably is not strictly necessary here.

A stop with an arrow is not sufficient as it puts the decision of when to turn on the driver of the car, instead of being dictated by the light as is the desired behavior...