Drupal: 'Views2 Returns Zero Results but SQL Is Fine!' Problem

This has happened couple times, already, so I may as well blog about it. If you use Views in Drupal, you already know that it shows you the final SQL generated. Very useful feature, but there's a rare edge condition when the SQL returns results (if you run it from an SQL client), but Views shows zero elements. Extremely frustrating and confusing.

How can this happen?