From: Richard Huxton <dev@archonet.com>
To: Jamie Lawrence <postgres@jal.org>
To: pgsql-sql@postgresql.org
Subject: Re: Fwd: Bad Join moment - how is this happening?
Date: Wed, 30 Jul 2003 20:36:22 +0100
Message-ID: <200307302036.22095.dev@archonet.com> (raw)
In-Reply-To: <20030730200732.GW1073@jal.clueinc.net>
References: <20030730200732.GW1073@jal.clueinc.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, postgres@jal.org
Subject: Re: Fwd: Bad Join moment - how is this happening?
In-Reply-To: <200307302036.22095.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox