Interestingly, this sequence works as one would expect in Oracle (9i):
create table foo ( iid int not null )
insert into foo values(1)
select * from foo
returns: 1 :)
select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where 1=1
returns: 1 1 1 :)
select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=1) = 0
returns 3 empty columns :)
select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=1) = 1
returns: 1 1 1 :)
select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=d1.iid) =0
returns 3 empty columns :)
select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=d1.iid) =1
returns: 1 1 1 :)
From this, one might conclude:
Sql Server isn't really ready for prime time
Microsoft still hasn't figured out how to build a properly behaving SQL engine
Oracle appears to have gotten it right (I'm not really an Oracle person, we just happen to be using it here)
If one needs the database to behave correctly, do not use Sql Server (?)