Loop-hole where non-existant column isn't properly evaluated by Zer0n3

We stumbled upon an odd issue which I repeatedly tested and confirmed on SQL 2000, 2005 and 2008.

It seems you can accidentally trick the evaluator in creating a left join with no predicate which results in returning all rows instead of returning the proper error "Invalid column name ''."

Here is an easy to test example:

select colid from sysobjects;

We know this will fail since the column 'colid' doesn't exist in sysobjects, this is how the evaluator should work.

Now try this:

select colid from syscolumns where colid in (select colid from sysobjects) ;

This should fail but it actually doesn't evaluate correctly even though the column 'colid' doesn't exist in the sysobjects table. The result from running the above query is that ALL rows from 'syscolumns' are returned. In the execution plan you will find a 'Nested Loops' with a warning about 'No join predicate'.

Results by returning *all* the rows from the syscolumns table.

I used sysobjects and syscolumns in this example for ease of testing on any system but we actually encountered this with one of our application databases in our QA environment.

The only way to duplicate this is where the column referenced in the where clause is named identically to the column used in the sub-select (but doesn't actually exist in the sub table) "select * from tableA where COLUMN1234 in (select COLUMN1234 from tableB)"