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