Unique Value validation rule does not show the indicated message
You wouldn't believe this but it was the theme. We had forgotten to include the markup that contains the flash messages sent by core to the user so basically it any flash message (including the validation errors) wouldn't show up.

The reason why other validation errors showed up was the Client-side Validation module that causes some certain form of validation violations to be detected in the client and displays them via it's own device.