pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Fwd: Bad Join moment - how is this happening?
7+ messages / 5 participants
[nested] [flat]
* Fwd: Bad Join moment - how is this happening?
@ 2003-07-30 20:07 Jamie Lawrence <postgres@jal.org>
2003-07-30 19:25 ` Re: Fwd: Bad Join moment - how is this happening? Josh Berkus <josh@agliodbs.com>
2003-07-30 19:36 ` Re: Fwd: Bad Join moment - how is this happening? Richard Huxton <dev@archonet.com>
2003-07-30 19:39 ` Re: Fwd: Bad Join moment - how is this happening? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 3 replies; 7+ messages in thread
From: Jamie Lawrence @ 2003-07-30 20:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
@ 2003-07-30 19:25 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-07-30 19:25 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
@ 2003-07-30 19:36 ` Richard Huxton <dev@archonet.com>
2003-07-30 19:38 ` Re: Fwd: Bad Join moment - how is this happening? Josh Berkus <josh@agliodbs.com>
2003-08-01 17:02 ` Re: Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
2 siblings, 2 replies; 7+ messages in thread
From: Richard Huxton @ 2003-07-30 19:36 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
2003-07-30 19:36 ` Re: Fwd: Bad Join moment - how is this happening? Richard Huxton <dev@archonet.com>
@ 2003-07-30 19:38 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-07-30 19:38 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Jamie Lawrence <postgres@jal.org>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
2003-07-30 19:36 ` Re: Fwd: Bad Join moment - how is this happening? Richard Huxton <dev@archonet.com>
@ 2003-08-01 17:02 ` Jamie Lawrence <postgres@jal.org>
1 sibling, 0 replies; 7+ messages in thread
From: Jamie Lawrence @ 2003-08-01 17:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
@ 2003-07-30 19:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2003-07-30 19:39 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: Bad Join moment - how is this happening?
@ 2003-07-31 09:30 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 7+ messages in thread
From: Christoph Haller @ 2003-07-31 09:30 UTC (permalink / raw)
To: postgres@jal.org; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-08-01 17:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-30 20:07 Fwd: Bad Join moment - how is this happening? Jamie Lawrence <postgres@jal.org>
2003-07-30 19:25 ` Josh Berkus <josh@agliodbs.com>
2003-07-30 19:36 ` Richard Huxton <dev@archonet.com>
2003-07-30 19:38 ` Josh Berkus <josh@agliodbs.com>
2003-08-01 17:02 ` Jamie Lawrence <postgres@jal.org>
2003-07-30 19:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-31 09:30 Re: Fwd: Bad Join moment - how is this happening? Christoph Haller <ch@rodos.fzk.de>
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