From: Jamie Lawrence <postgres@jal.org>
To: pgsql-sql@postgresql.org
Subject: Fwd: Bad Join moment - how is this happening?
Date: Wed, 30 Jul 2003 15:07:32 -0500
Message-ID: <20030730200732.GW1073@jal.clueinc.net> (raw)
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.
General issue: I'm getting cartesean products instead of left joins, and
I feel like a moron.
I have two tables (more actually, but the result will fix all of them):
dlm=# \d documents
Table "public.documents"
Column | Type | Modifiers
---------------+-----------------------------+-----------------------------------------------------------
id | integer | not null default nextval('public.documents_id_seq'::text)
projects_id | text |
doc_num | text |
description | text |
date | timestamp without time zone |
createdate | timestamp without time zone | default now()
moddate | timestamp without time zone |
people_id | integer |
parent | integer |
document_type | integer |
state | integer |
machines_id | integer |
phases_id | integer |
Indexes: documents_id_pkey primary key btree (id),
documents_parent_seq btree (parent),
documents_people_id_seq btree (people_id),
documents_projects_id btree (projects_id)
Foreign Key constraints: phases_exists FOREIGN KEY (phases_id) REFERENCES phases(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
parent_exists FOREIGN KEY (parent) REFERENCES documents(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
machine_exists FOREIGN KEY (machines_id) REFERENCES machines(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
people_exists FOREIGN KEY (people_id) REFERENCES people(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
project_exists FOREIGN KEY (projects_id) REFERENCES projects(id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: documents_timestamp_tr
dlm=# \d d_addenda
Table "public.d_addenda"
Column | Type | Modifiers
-------------------+---------+-----------------------------------------------------------
id | integer | not null default nextval('public.d_addenda_id_seq'::text)
documents_id | integer |
item_num | text |
drawing_reference | text |
Indexes: d_addenda_id_pkey primary key btree (id),
d_addenda_documents_id_idx btree (documents_id)
Foreign Key constraints: documents_exists FOREIGN KEY (documents_id) REFERENCES documents(id) ON UPDATE NO ACTION ON DELETE NO ACTION
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.
An example:
dlm=# select * from documents;
id | projects_id | doc_num | description | date | createdate | moddate | people_id | parent | document_type | state | machines_id | phases_id
----+-------------+---------+----------------------------+---------------------------+----------------------------+----------------------------+-----------+--------+---------------+-------+-------------+-----------
10 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | |
2 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | |
(2 rows)
dlm=# select * from d_addenda;
id | documents_id | item_num | drawing_reference
----+--------------+----------+-------------------
7 | 10 | 2 | none
2 | 2 | 1 | foo
(2 rows)
dlm=# select * from addenda;
id | oid | projects_id | doc_num | description | date | createdate | moddate | people_id | parent | document_type | state | machines_id | phases_id | item_num | drawing_reference
----+--------+-------------+---------+----------------------------+---------------------------+----------------------------+----------------------------+-----------+--------+---------------+-------+-------------+-----------+----------+-------------------
10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 2 | none
10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 1 | foo
2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 2 | none
2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 1 | foo
10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 2 | none
10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 1 | foo
2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 2 | none
2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 1 | foo
(8 rows)
I'm sure I'm being an idiot, but does anyone have a fix?
Thanks.
-j
--
Jamie Lawrence jal@jal.org
It it ain't broke, let me have a shot at it.
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: postgres@jal.org
Subject: Re: Fwd: Bad Join moment - how is this happening?
In-Reply-To: <20030730200732.GW1073@jal.clueinc.net>
* 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