From: Richard Huxton <dev@archonet.com>
To: Jamie Lawrence <postgres@jal.org>, pgsql-sql@postgresql.org
Subject: Re: Fwd: Bad Join moment - how is this happening?
Date: Wed, 30 Jul 2003 20:36:22 +0100
User-Agent: KMail/1.5
References: <20030730200732.GW1073@jal.clueinc.net>
In-Reply-To: <20030730200732.GW1073@jal.clueinc.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200307302036.22095.dev@archonet.com>

On Wednesday 30 July 2003 21:07, Jamie Lawrence wrote:
> I fully admit that I've been staring at this too long, and simply don't
> understand what is wrong. Apologies aside, any kind sql hackers who care
> to look this over will earn my undying gratitude, and a beer in the bar
> of your choice, should we ever meet.

I'll take that beer (assuming I'm right)

> General issue: I'm getting cartesean products instead of left joins, and
> I feel like a moron.

Nope - it's a subtle one.

> I have a view:
>
> create or replace view addenda as
> select
>         documents.id,
>         documents.oid,
>         documents.projects_id,
>         documents.doc_num,
>         documents.description,
>         documents.date,
>         documents.createdate,
>         documents.moddate,
>         documents.people_id,
>         documents.parent,
>         documents.document_type,
>         documents.state,
>         documents.machines_id,
>         documents.phases_id,
>
>         d_addenda.item_num,
>         d_addenda.drawing_reference
>
> from
>         d_addenda as a, documents as d
>                 where a.documents_id =  d.id;
>
>
> I appear to be getting a cartesean product when I select against the view
> 'addenda', when I want a left inner join. That is, I want documents
> records matched to addenda records only when there is a record in
> d_addenda  with a documents_id that matches the id field in documents.

I think this is the "adding a table into the FROM" feature of PG. You're 
referring to documents.xxx in the select and d.id in the FROM. PG tries to 
help out by adding the table into the FROM for you - hence cartesian join.

I think you can turn this "feature" off in the config file in 7.3.x (haven't 
checked this though)

-- 
  Richard Huxton
  Archonet Ltd