pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRIGHT JOIN is only supported with mergejoinable join conditions
8+ messages / 3 participants
[nested] [flat]
* RIGHT JOIN is only supported with mergejoinable join conditions
@ 2001-11-07 16:16 Nick Fankhauser <nickf@ontko.com>
2001-11-07 16:49 ` Re: RIGHT JOIN is only supported with mergejoinable join Josh Berkus <josh@agliodbs.com>
2001-11-07 17:38 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 19:06 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
2001-11-11 19:37 ` Re: [SQL] RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 8+ messages in thread
From: Nick Fankhauser @ 2001-11-07 16:16 UTC (permalink / raw)
To: pgsql-sql
Hi-
I'm on version 7.1, and I'm getting this error when attempting to select
from a view:
RIGHT JOIN is only supported with mergejoinable join conditions
I don't understand what this error is telling me...
The script I have used to create the view is below pasted in below.
Essentially, I have a main table which I want to see every row from. I also
have two separate lookup tables that I want to get a description field from
*if* there is a matching code in a corresponding nullable field in the main
table. I tried pasting this into MSAccess, and it works fine there. (I know
this doesn't necessarily mean it is valid SQL <grin>.)
My questions are:
1)Have I done something wrong here, or am I hitting a limitation of
PostgreSQL?
2)In either case, how could I re-write this to make it work with PostgreSQL?
Thanks!
-Nick
create view demo as
select
case_data.case_id,
case_disposition_code.case_disp_global_desc,
local_case_type.global_case_type_desc
from
local_case_type
right join
(
case_disposition_code
right join
case_data
on
case_disposition_code.case_disp_local_code =
case_data.case_disp_local_code
)
on
(
local_case_type.court_id =
case_data.court_id
)
and
(
local_case_type.local_case_subtype_code =
case_data.local_case_type_code
)
and
(
local_case_type.local_case_subtype_code =
case_data.local_case_subtype_code
);
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
@ 2001-11-07 16:49 ` Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-11-07 16:49 UTC (permalink / raw)
To: nickf@ontko.com; pgsql-sql
Nick,
> RIGHT JOIN is only supported with mergejoinable join conditions
Woof! Talk about destruction testing. You have ... let's see ... a
three-column right join on two right-joined tables. If you ahve
uncovered a bug, I wouldn't be surprised.
However, are you sure you want RIGHT OUTER JOINS and not LEFT? Try
re-organizing the query as LEFT JOINS, and see if it works.
create view demo as
select
case_data.case_id,
case_disposition_code.case_disp_global_desc,
local_case_type.global_case_type_desc
from
( case_data
left join
case_disposition_code
on
case_data.case_disp_local_code =
case_disposition_code.case_disp_local_code
)
LEFT JOIN local_case_type ON
((
local_case_type.court_id =
case_data.court_id
)
and
(
local_case_type.local_case_subtype_code =
case_data.local_case_type_code
)
and
(
local_case_type.local_case_subtype_code =
case_data.local_case_subtype_code
));
If that doesn't work, try making the case_data and case_disposition_code
join into a subselect.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
@ 2001-11-07 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 18:29 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
3 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-11-07 17:38 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-sql
"Nick Fankhauser" <nickf@ontko.com> writes:
> I'm on version 7.1, and I'm getting this error when attempting to select
> from a view:
> RIGHT JOIN is only supported with mergejoinable join conditions
What are the datatypes of the columns you're using?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
2001-11-07 17:38 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-07 18:29 ` Nick Fankhauser <nickf@ontko.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nick Fankhauser @ 2001-11-07 18:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
They are all varchar.
-Nick
> > RIGHT JOIN is only supported with mergejoinable join conditions
>
> What are the datatypes of the columns you're using?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
@ 2001-11-07 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 22:22 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
3 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-11-07 19:06 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-sql
"Nick Fankhauser" <nickf@ontko.com> writes:
> and
> (
> local_case_type.local_case_subtype_code =
> case_data.local_case_type_code
> )
Did you actually mean to match local_case_subtype_code against
local_case_type_code, or is that a typo?
I believe you have uncovered a planner bug, but the bug may be triggered
by the partial overlap of this join condition with the next one.
Assuming that it's a typo, you may find that you avoid the problem by
fixing the typo.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
2001-11-07 19:06 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-07 22:22 ` Nick Fankhauser <nickf@ontko.com>
2001-11-07 22:48 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Nick Fankhauser @ 2001-11-07 22:22 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom, Josh:
Thanks for the ideas! Tom's idea was the easiest to test, so I tried it
first, and it worked! As you surmised, there was a typo, so I removed the
extra "sub".
I agree that this still may be a bug. These tables have been migrated
forward from an older postgresql version & hence have no primary or foreign
key constraints that might tip off the planner about my typo - as far as the
database knows, these are just two varchar fields in separate tables. Your
thought about the overlap causing the problem seems likely since this seems
to be a valid query, even with the typo.
At any rate, my immediate problem is solved & I'm a happy camper!
Thanks.
-Nick
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Wednesday, November 07, 2001 2:06 PM
> To: nickf@ontko.com
> Cc: PGSQL-SQL
> Subject: Re: [SQL] RIGHT JOIN is only supported with mergejoinable join
> conditions
>
>
> "Nick Fankhauser" <nickf@ontko.com> writes:
> > and
> > (
> > local_case_type.local_case_subtype_code =
> > case_data.local_case_type_code
> > )
>
> Did you actually mean to match local_case_subtype_code against
> local_case_type_code, or is that a typo?
>
> I believe you have uncovered a planner bug, but the bug may be triggered
> by the partial overlap of this join condition with the next one.
> Assuming that it's a typo, you may find that you avoid the problem by
> fixing the typo.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
2001-11-07 19:06 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 22:22 ` Re: RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
@ 2001-11-07 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-11-07 22:48 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
"Nick Fankhauser" <nickf@ontko.com> writes:
> I agree that this still may be a bug.
It definitely is a bug --- we fixed a similar problem around 7.1.1 or
so, but this test case appears to expose a different variant of the
mistake. (The planner is generating a plan that the executor can't
handle; it's supposed to know not to do that.)
I think I know where to fix it, but am not confident enough in
my powers of analysis today to want to actually commit anything.
(I've had a bad head-cold all week and am still unable to do anything
that requires more than a few minutes of sustained thought :-()
Will get back on it as soon as I feel better...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] RIGHT JOIN is only supported with mergejoinable join conditions
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
@ 2001-11-11 19:37 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-11-11 19:37 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-hackers@postgreSQL.org
"Nick Fankhauser" <nickf@ontko.com> writes:
> I'm on version 7.1, and I'm getting this error when attempting to select
> from a view:
> RIGHT JOIN is only supported with mergejoinable join conditions
I have committed a fix for this problem --- of the three routines that
can generate mergejoin plans, only two were checking to ensure they'd
generated a valid join plan in RIGHT/FULL join cases. I seem to recall
having deliberately decided that sort_inner_and_outer didn't need to
check, but your example proves that it does.
There is still a related problem with FULL JOIN, which is that *all*
the possible join plans may get rejected:
regression=# create table aa (v1 varchar, v2 varchar);
CREATE
regression=# create table bb (v1 varchar, v2 varchar, v3 varchar);
CREATE
regression=# select * from aa a full join bb b on
regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1 and a.v2 = b.v1;
ERROR: Unable to devise a query plan for the given query
regression=#
This is not exactly fatal, since you can work around it by pushing
down the redundant join condition to one of the input relations:
regression=# select * from aa a full join bb b on
regression-# a.v2 = b.v3 and a.v1 = b.v2 and a.v1 = b.v1
regression-# where a.v2 = a.v1;
[ okay ]
But it's pretty annoying anyway. I'm trying to figure out how we could
implement the query as given...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-11-11 19:37 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-07 16:16 RIGHT JOIN is only supported with mergejoinable join conditions Nick Fankhauser <nickf@ontko.com>
2001-11-07 16:49 ` Re: RIGHT JOIN is only supported with mergejoinable join Josh Berkus <josh@agliodbs.com>
2001-11-07 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 18:29 ` Nick Fankhauser <nickf@ontko.com>
2001-11-07 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-07 22:22 ` Nick Fankhauser <nickf@ontko.com>
2001-11-07 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-11 19:37 ` Tom Lane <tgl@sss.pgh.pa.us>
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