Join Problem in Postgres 7.0.2

Khang Le (xudien(at)hotmail(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Join Problem in Postgres 7.0.2

Long Description
Database: Postgres 7.0.2
Platform: Red Hat Linux release 6.0 (Hedwig)
Kernel 2.2.5-15smp on an i586
Date: 11/29/2000 EST

To whom it may concern,

I get into a situation, in which I think it is a bug in either psql or the database. I have experienced that when I ran two queries in
psql, which are equivalent, I received two different results

First SQL
select q.question, a.answer
from cnfrm_qstn cq, question q, answer a
where cq.schedule_id = 1 and cq.question_id = q.question_id
and cq.question_id = a.question_id;

Second SQL
select q.question, a.answer
from cnfrm_qstn cq, question q, answer a
where cq.schedule_id = 1 and cq.question_id = q.question_id
and q.question_id = a.question_id;

The different is the last condition. In other database, their
results are the same. In Postgres 7.0.2, their results are different.

Thanks,

Khang

PS: The DDL and SQL as well as results in in Example Code, please review.

Sample Code
--------------------- Postgres 7.0.2 DDL -----------------------

DROP SEQUENCE question_seq;
DROP TABLE QUESTION;
CREATE SEQUENCE question_seq;
CREATE TABLE QUESTION (
QUESTION_ID INT4 DEFAULT nextval('question_seq'),
QUESTION VARCHAR(64) NOT NULL,
GROUP_ID VARCHAR(12) NOT NULL,
CONSTRAINT question_pk PRIMARY KEY(QUESTION_ID)
);

DROP TABLE ANSWER;
CREATE TABLE ANSWER (
QUESTION_ID INT4 NOT NULL,
ANSWER_ID VARCHAR(12) NOT NULL,
ANSWER VARCHAR(32) NOT NULL,
CONSTRAINT answer_pk PRIMARY KEY(QUESTION_ID,ANSWER_ID)
);

DROP TABLE CNFRM_QSTN;
CREATE TABLE CNFRM_QSTN (
SCHEDULE_ID INT4 NOT NULL,
QUESTION_ID INT4 NOT NULL,
CONSTRAINT cnfrm_qstn_pk PRIMARY KEY(SCHEDULE_ID,QUESTION_ID)
);

INSERT INTO QUESTION (QUESTION,GROUP_ID)
VALUES ('How Many Heartbeat Do You Measure Per Minute?','wap');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (1,1,'Below 60');

INSERT INTO ACTION(QUESTION_ID,ANSWER_ID,ACTION)
VALUES (1,1,'ALERT');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (1,2,'From 61 To 70');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (1,3,'From 71 To 80');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (1,4,'Above 81');

INSERT INTO QUESTION (QUESTION,GROUP_ID)
VALUES ('How Much Cholesterol Is In Your Blood?','wap');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (2,1,'Low');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (2,2,'Normal');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (2,3,'High');

INSERT INTO QUESTION (QUESTION,GROUP_ID)
VALUES ('Have You Ever Had Heart Attack?','wap');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (3,1,'Yes');

INSERT INTO ANSWER(QUESTION_ID,ANSWER_ID,ANSWER)
VALUES (3,2,'No');

INSERT INTO CNFRM_QSTN(SCHEDULE_ID,QUESTION_ID) VALUES (1,1);

INSERT INTO CNFRM_QSTN(SCHEDULE_ID,QUESTION_ID) VALUES (1,2);

INSERT INTO CNFRM_QSTN(SCHEDULE_ID,QUESTION_ID) VALUES (1,3);

-------------------- Postgres 7.0.2 Bug in joining --------------

testdb=> select q.question, a.answer
testdb-> from cnfrm_qstn cq, question q, answer a
testdb-> where cq.schedule_id = 1 and cq.question_id = q.question_id
testdb-> and cq.question_id = a.question_id;
question | answer
-----------------------------------------------+---------------
How Many Heartbeat Do You Measure Per Minute? | Below 60
How Many Heartbeat Do You Measure Per Minute? | From 61 To 70
How Many Heartbeat Do You Measure Per Minute? | From 71 To 80
How Many Heartbeat Do You Measure Per Minute? | Above 81
How Much Cholesterol Is In Your Blood? | Low
How Much Cholesterol Is In Your Blood? | Normal
How Much Cholesterol Is In Your Blood? | High
Have You Ever Had Heart Attack? | Yes
Have You Ever Had Heart Attack? | No
(9 rows)

testdb=> select q.question, a.answer
testdb-> from cnfrm_qstn cq, question q, answer a
testdb-> where cq.schedule_id = 1 and cq.question_id = q.question_id
testdb-> and q.question_id = a.question_id;
question | answer
-----------------------------------------------+---------------
How Many Heartbeat Do You Measure Per Minute? | Below 60
How Many Heartbeat Do You Measure Per Minute? | From 61 To 70
How Many Heartbeat Do You Measure Per Minute? | From 71 To 80
How Many Heartbeat Do You Measure Per Minute? | Above 81
(4 rows)

No file was uploaded with this report