It’s not a bug, it’s a feature

So while poking around at a bug in one of our database views, I looked at another view that queried it and happened to find some code like this:

    SELECT col1
    FROM right_table R
    WHERE r.col1 IN (
        SELECT DISTINCT r.col1
        FROM wrong_table
    );

Where wrong_table doesn’t have col1. Now this code has been in production for months now, and no bugs were found from it and it all worked well. The query above will select all rows of right_table unless wrong_table is empty, in which case it selects no rows. This code gave no errors in production because the table that the view is based on already filters for the result, but that wasn’t ideal.

    SELECT col1
    FROM right_table r
    WHERE r.col IN (
        SELECT w.col
        FROM wrong_table w
    );