Join Problem in Postgres 7.0.2

Khang Le (xudien@hotmail.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
rantwo 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