Skip site navigation (1) Skip section navigation (2)

Possible SELECT bug in 7.0.2

From: Jean-Louis Leroy <jll(at)tangram-persistence(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Possible SELECT bug in 7.0.2
Date: 2000-12-28 17:32:01
Message-ID: m3elys78q6.fsf@enterprise.starfleet (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

While working on the next release of Tangram, I have encountered the
following problem in version 7.0.2. Given the following tables:

Springfield=> select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
 
Springfield=> select * from Person;
  id  | classid
------+---------
 1006 |       6
 2006 |       6
 3006 |       6
 4006 |       6
 5006 |       6
(5 rows)
 
Springfield=> select * from NP;
  id  | ia_ref | credit | name | firstname | ia_slot | is_ref | partner | age
------+--------+--------+------+-----------+---------+--------+---------+-----
 1006 |        |        |      | Bart      |         |        |         |
 2006 |        |        |      | Lisa      |         |        |         |
 3006 |        |        |      | Maggie    |         |        |         |
 5006 |        |        |      | Marge     |         |        |         |
 4006 |        |        |      | Homer     |         |        |         |
(5 rows)
 
Springfield=> select * from s_children;
 coll | item
------+------
 5006 | 3006
 5006 | 1006
 5006 | 2006
 4006 | 1006
 4006 | 3006
 4006 | 2006
(6 rows)

...I run the following two queries:

Springfield=> SELECT t4.firstName
        FROM Person t1, NP t2, s_children tl1001, Person t3, NP t4
        WHERE (tl1001.coll = t1.id AND tl1001.item = t3.id)
        AND t2.id = t1.id AND t4.id = t3.id;
 firstname
-----------
 Bart
 Bart
 Maggie
 Maggie
(4 rows)
 
Springfield=> SELECT t2.firstName, t4.firstName
        FROM Person t1, NP t2, s_children tl1001, Person t3, NP t4
        WHERE (tl1001.coll = t1.id AND tl1001.item = t3.id)
        AND t2.id = t1.id AND t4.id = t3.id;
 firstname | firstname
-----------+-----------
 Homer     | Bart
 Homer     | Lisa
 Homer     | Maggie
(3 rows)

Both result sets seem wrong; even more bizarre, adding a column in the
projection affects the result set.

In addition, Tangram's test suite runs flawlessly against Sybase,
Mysql, Oracle and Postgres 6.4.2.
-- 
Jean-Louis Leroy
http://users.skynet.be/jll

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-12-28 17:41:44
Subject: Re: Alpha tas() patch
Previous:From: Brent VernerDate: 2000-12-28 16:54:58
Subject: Re: Alpha tas() patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group