Forum OpenACS Q&A: Postgres 7.1 beta 1 problems

Collapse
Posted by Grzegorz Mucha on
Hello!
While I know that this isn't the luckiest forum to ask this question, I want to share a reflection.
I had installed PG 7.1 beta1 from CVS, and tried to play with OUTER JOIN. While it accepts the syntax without any squeaks, it simply doesn't work (it works just like an INNER JOIN). Having tried the UNION way of doing outer join and the SQL one(t1 NATURAL LEFT OUTER JOIN t2.....) - the first one returns good set of tuples, the second one does an inner join(even the query plans are the same.

Anyone encountered this, too?

Collapse
Posted by Don Baccus on
Post your query and table contents,  please.  The version I downloaded
about three weeks ago did outer joins just fine for my test cases, so
if it isn't working for you either you've found a test case that
breaks it, or the current sources are totally broken altogether.
Collapse
Posted by Don Baccus on
I suspect that though you've built PG 7.1, you must be accidently running an older install of PG 7.0 (which did accept the syntax but didn't implement the outer joins). If you installed PG 7.0 earlier from RPMs, your PG 7.1 install will go elsewhere by default and if you didn't change your PG* environment variables, you'll still actually run the PG 7.0 install.

I updated PG 7.1 from CVS and rebuilt it to make sure that outer joins haven't broken in the last few weeks. Here are the results of some tests:

pgtest=# select * from one;
 i | j 
---+---
 1 | 2
 2 | 3
 4 | 4
(3 rows)

pgtest=# select * from two;
 i | k 
---+---
 2 | 2
 3 | 2
 4 | 2
 5 | 5
(4 rows)

pgtest=# select * from one left join two using (i);
 i | j | k 
---+---+---
 1 | 2 |  
 2 | 3 | 2
 4 | 4 | 2
(3 rows)

pgtest=# select * from one right join two using (i);
 i | j | k 
---+---+---
 2 | 3 | 2
 3 |   | 2
 4 | 4 | 2
 5 |   | 5
(4 rows)

pgtest=# select * from one full join two using (i);
 i | j | k 
---+---+---
 1 | 2 |  
 2 | 3 | 2
 3 |   | 2
 4 | 4 | 2
 5 |   | 5
(5 rows)

pgtest=# 
Collapse
Posted by Grzegorz Mucha on
seems I must have grabbed an unluckily chosen PG version. Today I updated my CVS and compiled and the joins are working fine.
Collapse
Posted by Grzegorz Mucha on
The outer joins in views are working, too, though not in more complicated queries(the ec_recommendations_cats_view is clearly too complicated and still generates errors).
Collapse
Posted by Don Baccus on
You should submit an example of a failing view with outer join to the
Postgres developers, so they can fix it before the beta release if
possible.  It may not be, as this release doesn't include the
from-the-ground-up redesign of the query tree (i.e. parser/semantic
analyzer output) that's planned for 7.2, and some complex queries just
won't map to the current internal representation.