Bug #536: the sql 'except' generates erroneous result

luby liao (liao@sandiego.edu) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
the sql 'except' generates erroneous result

Long Description
A student of mine, Sean Canaday, worked on prob 6.17 of M Kifer's
Dabatabase book (AW, p175):

6.17 Consider the following schema:

Transcript(StudId, CrsCode, Semester, Grade)
Teaching(ProfId, CrsCode, Semester)
Professor(ProfId, ProfName, Dept)

Write the following query in SQL: Find all student Ids who had taken a
course from each professor in the MUS department .

His solution is:

select distinct t.studid from stranscript t where not exists(
(select p.id from s_professor p where p.deptid = 'MUS')
except
(select te.profid from s_teaching te, stranscript t1 where
t1.crscode=te.crscode and t1.semester = te.sem and t1.studid=t.studid))

This generates incorrect result in postgres (7.1.2 on RedHat Linux
7.0), but correct result in oracle when tested against the following data. Specifically, the query returned empty set in postgres, but the student with id 100 in oracle.

/* --------------------------------------------------------
phpPgAdmin 2.3 DB Dump
http://www.greatbridge.org/project/phppgadmin/
Host: localhost:5432
Database : cs145
Table structure for table "s_professor"
2001-11-12 14:12:17
-------------------------------------------------------- */

CREATE TABLE "s_professor" (
"id" int4,
"name" varchar(20),
"deptid" varchar(10),
"age" int4,
"salary" int4
);

/* --------------------------------------------------------
Dumping data for table "s_professor"
-------------------------------------------------------- */
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(1, 'Luby Liao', 'CS', 50, 50000);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(2, 'Adrian Jones', 'MGT', 23, 40000);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(3, 'David Jones', 'EE', 45, 88888);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(4, 'Qi Chen', 'MAT', 99, 838888);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(5, 'Mary Doe', 'CS', 10, 83);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(6, 'as hole', 'PSY', 58, 99999);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(7, 'Dr. Glick', 'CS', 39, 110000);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(8, 'Other Person', 'MAT', 30, 110);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(9, 'musteacher 1', 'MUS', 49, 10000);
INSERT INTO "s_professor" ("id", "name", "deptid", "age", "salary") VALUES(10, 'musteacher 2', 'MUS', 50, 10001);

/* --------------------------------------------------------
phpPgAdmin 2.3 DB Dump
http://www.greatbridge.org/project/phppgadmin/
Host: localhost:5432
Database : cs145
Table structure for table "s_student"
2001-11-12 14:12:17
-------------------------------------------------------- */

CREATE TABLE "s_student" (
"st_id" int2 NOT NULL,
"last" varchar(20),
"first" varchar(20),
"age" int4,
CONSTRAINT "s_student_pkey" PRIMARY KEY ("st_id")
);

/* --------------------------------------------------------
Dumping data for table "s_student"
-------------------------------------------------------- */
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(100, 'Canaday', 'Sean', 21);
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(101, 'Mex', 'Ori', 28);
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(102, 'G', 'Rahul', 10);
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(103, 'Important', 'Not', 21);
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(104, 'Dover', 'Ben', 10);
INSERT INTO "s_student" ("st_id", "last", "first", "age") VALUES(106, 'Hugankis', 'Amanda', 21);

/* --------------------------------------------------------
phpPgAdmin 2.3 DB Dump
http://www.greatbridge.org/project/phppgadmin/
Host: localhost:5432
Database : cs145
Table structure for table "s_teaching"
2001-11-12 14:12:15
-------------------------------------------------------- */

CREATE TABLE "s_teaching" (
"profid" int4,
"crscode" varchar(10),
"sem" varchar(6)
);

/* --------------------------------------------------------
Dumping data for table "s_teaching"
-------------------------------------------------------- */
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(7, 'mat123', 'F1997');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(8, 'mat123', 'S1997');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(2, 'mat123', 'F1998');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(5, 'mat123', 'S1998');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(1, 'mat123', 'F1997');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(9, 'mus1', 'F1998');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(10, 'mus1', 'S1998');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(9, 'mus2', 'F1998');
INSERT INTO "s_teaching" ("profid", "crscode", "sem") VALUES(10, 'mus2', 'S1998');

/* --------------------------------------------------------
phpPgAdmin 2.3 DB Dump
http://www.greatbridge.org/project/phppgadmin/
Host: localhost:5432
Database : cs145
Table structure for table "stranscript"
2001-11-12 14:12:16
-------------------------------------------------------- */

CREATE TABLE "stranscript" (
"studid" int4,
"crscode" varchar(20),
"semester" varchar(20),
"grade" varchar(20)
);

/* --------------------------------------------------------
Dumping data for table "stranscript"
-------------------------------------------------------- */
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'cs123', 'F1999', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'cs51', 'F1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'cs50', 'S1997', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'cs52', 'S1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(101, 'cs1476', 'S1999', 'D');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(102, 'cs146', 'S1999', 'F');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(103, 'cs145', 'S1999', 'F');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(101, 'cs145', 's1999', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(101, 'cs52', 'S1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(101, 'cs305', 'F1997', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(102, 'cs305', 'F1997', 'C');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(103, 'cs305', 'F1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(103, 'mat123', 'F1998', 'C');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(102, 'mat123', 'F1998', 'C');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(104, 'cs6', 'F1999', 'F');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'mat123', 'S1998', 'B');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(104, 'mat123', 'S1998', 'B');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(104, 'cs305', 'S1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'mus1', 'F1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(100, 'mus2', 'S1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(101, 'mus2', 'S1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(103, 'mus1', 'F1998', 'A');
INSERT INTO "stranscript" ("studid", "crscode", "semester", "grade") VALUES(104, 'mus2', 'S1998', 'A');

Sample Code

No file was uploaded with this report