Tests improvements: Better check for errors

What would you like to be added or enhanced

In many tests we're using require.Error(t, err) which only checks if the error happened without checking if the error is actually expected.

We should add more detailed error check, either by using require.ErrorIs or require.ErrorContains assertions.

Why is this needed

When expecting an error it is necessary to ensure the returned error is what was expected. It may turn out that in some cases the error checked by the test is not what it should be.