Thread

1. Join Problem in Postgres 7.0.2

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-29T15:57:24Z

   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 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
   
   
   

2. Re: Join Problem in Postgres 7.0.2

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-29T16:36:15Z

   pgsql-bugs@postgresql.org writes:
   > 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
   
   Does EXPLAIN show that the failing query has a Merge Join directly
   underneath another Merge Join?  If so, this is a known bug.  It's
   fixed in 7.0.3.
   
   			regards, tom lane