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 | 62006 | 63006 | 64006 | 65006 | 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 | 30065006 | 10065006 | 20064006 | 10064006 | 30064006 | 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
-----------BartBartMaggieMaggie
(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 | BartHomer | LisaHomer | 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