pgsql-bugs@postgresql.org writes:
> the sql 'except' generates erroneous result
Yeah, anything with nested intersect/except is likely to give the wrong
answers in 7.1.* :-(. This seems to be a variant of that problem.
In 7.2beta I get the expected answer:
regression=# select distinct t.studid from stranscript t where not exists(
regression(# (select p.id from s_professor p where p.deptid = 'MUS')
regression(# except
regression(# (select te.profid from s_teaching te, stranscript t1 where
regression(# t1.crscode=te.crscode and t1.semester = te.sem and t1.studid=t.studid));
studid
--------
100
(1 row)
regression=#
regards, tom lane