relation comparison in WHERE clause gives wrong result
Posted: Mon Oct 06, 2008 2:49 pm
Using the same example as in my "join on RVA" post, also today:
var EXAM_MARK base relation { StudentId CHAR, CourseId CHAR, Mark INTEGER } key {ALL BUT Mark};
EXAM_MARK := relation { tuple { StudentId 'S1', CourseId 'C1', Mark 85 }, tuple { StudentId 'S1', CourseId 'C2', Mark 49 }, tuple { StudentId 'S2', CourseId 'C1', Mark 49 }, tuple { StudentId 'S3', CourseId 'C3', Mark 66 }, tuple { StudentId 'S4', CourseId 'C1', Mark 93 } };
the following query gave the wrong result, as indicated:
WITH EXAM_MARK{StudentId} as ns,
ns RENAME (StudentId as s1) as ns1,
ns RENAME (StudentId as s2) as ns2,
(ns1 JOIN ns2) as s1s2:
s1s2 where s1<>s2 and (((EXAM_MARK where s1 = StudentId){CourseId})>=
((EXAM_MARK where s2 = StudentId){CourseId}))
RELATION {s1 CHARACTER, s2 CHARACTER} {
TUPLE {s1 "S1", s2 "S2"},
TUPLE {s1 "S1", s2 "S3"},
TUPLE {s1 "S1", s2 "S4"},
TUPLE {s1 "S2", s2 "S1"},
TUPLE {s1 "S2", s2 "S3"},
TUPLE {s1 "S2", s2 "S4"},
TUPLE {s1 "S3", s2 "S1"},
TUPLE {s1 "S3", s2 "S2"},
TUPLE {s1 "S3", s2 "S4"},
TUPLE {s1 "S4", s2 "S1"},
TUPLE {s1 "S4", s2 "S2"},
TUPLE {s1 "S4", s2 "S3"}
}
Note carefully that TUPLE { S1 "S1", s2 "S3" } (the second in the above list) should not be present, as evidenced when I extract the relation comparison from the WHERE clause and evaluate it with "S1" and "S3" in place of the attribute names s1 and s2:
(((EXAM_MARK where 'S1' = StudentId){CourseId})>=
((EXAM_MARK where 'S3' = StudentId){CourseId}))
false
Hugh
var EXAM_MARK base relation { StudentId CHAR, CourseId CHAR, Mark INTEGER } key {ALL BUT Mark};
EXAM_MARK := relation { tuple { StudentId 'S1', CourseId 'C1', Mark 85 }, tuple { StudentId 'S1', CourseId 'C2', Mark 49 }, tuple { StudentId 'S2', CourseId 'C1', Mark 49 }, tuple { StudentId 'S3', CourseId 'C3', Mark 66 }, tuple { StudentId 'S4', CourseId 'C1', Mark 93 } };
the following query gave the wrong result, as indicated:
WITH EXAM_MARK{StudentId} as ns,
ns RENAME (StudentId as s1) as ns1,
ns RENAME (StudentId as s2) as ns2,
(ns1 JOIN ns2) as s1s2:
s1s2 where s1<>s2 and (((EXAM_MARK where s1 = StudentId){CourseId})>=
((EXAM_MARK where s2 = StudentId){CourseId}))
RELATION {s1 CHARACTER, s2 CHARACTER} {
TUPLE {s1 "S1", s2 "S2"},
TUPLE {s1 "S1", s2 "S3"},
TUPLE {s1 "S1", s2 "S4"},
TUPLE {s1 "S2", s2 "S1"},
TUPLE {s1 "S2", s2 "S3"},
TUPLE {s1 "S2", s2 "S4"},
TUPLE {s1 "S3", s2 "S1"},
TUPLE {s1 "S3", s2 "S2"},
TUPLE {s1 "S3", s2 "S4"},
TUPLE {s1 "S4", s2 "S1"},
TUPLE {s1 "S4", s2 "S2"},
TUPLE {s1 "S4", s2 "S3"}
}
Note carefully that TUPLE { S1 "S1", s2 "S3" } (the second in the above list) should not be present, as evidenced when I extract the relation comparison from the WHERE clause and evaluate it with "S1" and "S3" in place of the attribute names s1 and s2:
(((EXAM_MARK where 'S1' = StudentId){CourseId})>=
((EXAM_MARK where 'S3' = StudentId){CourseId}))
false
Hugh