Join Problem in Postgres 7.0.2
От | pgsql-bugs@postgresql.org |
---|---|
Тема | Join Problem in Postgres 7.0.2 |
Дата | |
Msg-id | 200011291557.eATFvOO28683@hub.org обсуждение исходный текст |
Ответы |
Re: Join Problem in Postgres 7.0.2
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: