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
