luby liao ([EMAIL PROTECTED]) 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
