Bug #536: the sql 'except' generates erroneous result - Mailing list pgsql-bugs

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
inpostgres, 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