Thread: Fwd: Bad Join moment - how is this happening?
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
withouttime 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_seqbtree (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
NOACTION, machine_exists FOREIGN KEY (machines_id) REFERENCES machines(id) ON UPDATE NO ACTION
ONDELETE NO ACTION, people_exists FOREIGN KEY (people_id) REFERENCES people(id) ON UPDATE NO
ACTIONON DELETE NO ACTION, project_exists FOREIGN KEY (projects_id) REFERENCES projects(id) ON
UPDATENO ACTION ON DELETE NO ACTION
Triggers: documents_timestamp_tr
dlm=# \d d_addenda Table "public.d_addenda" Column | Type |
Modifiers
-------------------+---------+-----------------------------------------------------------id | integer |
notnull 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
DELETENO 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-3012: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-3012:22:48.094521 | | | | | | | 2 | none10 |
183371| 1 | | this is a test description | | 2003-07-30 12:22:48.094521 |
2003-07-3012: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-2719: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-2719:03:01.30362 | | | 1 | 1 | | | 1 | foo10 |
183371| 1 | | this is a test description | | 2003-07-30 12:22:48.094521 |
2003-07-3012:22:48.094521 | | | | | | | 2 | none10 |
183371| 1 | | this is a test description | | 2003-07-30 12:22:48.094521 |
2003-07-3012: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-2719: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-2719: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.
Jamie, > General issue: I'm getting cartesean products instead of left joins, and > I feel like a moron. No, this is extremely bizarre. I can't think of an explanation for the results you're getting, except that the view definition isn't what you think it is. Can you do a \d addenda and post the results? -- -Josh BerkusAglio Database SolutionsSan Francisco
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
Richard, > 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. Aha! You're right. > I think you can turn this "feature" off in the config file in 7.3.x (haven't > checked this though) Nope, it's a 7.4 feature to turn it off in .conf. Look under the "compatibility" section (the last section) in 7.4's postgresql.conf. -- -Josh BerkusAglio Database SolutionsSan Francisco
On Wed, 30 Jul 2003, Jamie Lawrence wrote: > 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 think you're being bitten by from clause addition. You're referencing documents in the select list rather than d so it's probably adding an unconstrained join to documents.
> 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;
>
Doing this you should have got
NOTICE: Adding missing FROM-clause entry for table "d_addenda"
NOTICE: Adding missing FROM-clause entry for table "documents"
So it's pretty useful to write psql's output to a file when creating
relations
and check for NOTICE messages.
exec 3>/tmp/psql.out;$PGSQLD/bin/psql <your options here> 1>&3 2>&3
NOTICE messages appear to be sent to stderr.
As the previous posters already made clear
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 , documents where d_addenda.documents_id = documents.id;
resp.
create or replace view addenda as
select d.id, d.oid, d.projects_id, d.doc_num, d.description, d.date,
d.createdate, d.moddate, d.people_id, d.parent, d.document_type, d.state,
d.machines_id, d.phases_id,
a.item_num, a.drawing_reference
from d_addenda as a, documents as d where a.documents_id = d.id;
should match your intentions.
Regards, Christoph
Sorry to be slow on responses... Thanks to everyone who replies. On Wed, 30 Jul 2003, Richard Huxton wrote: > 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) I think you nailed it first. You appear to be in London, which I haven't made it to in ~15 years, and mailing it is probably a bad idea. Ever make it to the NYC area? > > 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) This was exactly it. Thanks for the help, this was really making me crazy. -j -- Jamie Lawrence jal@jal.org anger, bargaining, depression, and, finally, acceptance