You make a good point, and DBD::Mock is pretty cool. However, the difficulty is that DBD::Mock just does whatever you tell it to do.
When I was developing the code for Class::DBI's single-instance functionality, I found some tests that failed because there was a mistake in their assumptions about primary keys. If the test suite had been written to use DBD::Mock, it would have just returned whatever it was told to, and this bug would not have been found by the tests. In other words, the failure had to do with the interaction between multiple methods, not with the failure of a single method. It was only because the test did a real query on real data that had been created earlier in the test that I was able to see the problem.