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

28 Dec 2000 18:32:01 +0100
Jean-Louis Leroy <jll@tangram-persistence.org>
Possible SELECT bug in 7.0.2
Jean-Louis Leroy <jll@tangram-persistence.org> writes:
> 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.

Seems fishy, but there's not enough info here to diagnose the problem.

Does EXPLAIN show nested mergejoin plans for the failing queries?
If so, that's a known bug that is fixed in 7.0.3.  If not, I'd like
to see enough info to reproduce the problem --- a pg_dump database
dump and a procedure for reproducing the error after reload would
do nicely ...

			regards, tom lane

Sat, 30 Dec 2000 00:33:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Possible SELECT bug in 7.0.2 