I have a FULL JIN between two simple tables, and am getting the message
" Query failed: ERRR: FULL JIN is only supported with merge-joinable
join conditions"
I'd be glad to fix this in my query if I knew what the #@!! it meant.
Using 8.0.4 on FC1; willing to upgrade (PostgreSQL, not FC1) IF it
would fix the problem.
-- Dean
(end of broadcast)
TIP 5: don't forget to increase your free space map settings

"Dean Gibson (DB Administrator)" <postgresql4 (AT) ultimeth (DOT) comwrites:
I have a FULL JIN between two simple tables, and am getting the message
" Query failed: ERRR: FULL JIN is only supported with merge-joinable
join conditions"

I'd be glad to fix this in my query if I knew what the #@!! it meant.

The join condition(s) of a FULL JIN have to be mergejoinable, which
basically means an equality relation between sortable (btree-indexable)
data types. You can see exactly which operators are mergejoinable with
a query like

select oid::regoperator from pg_operator where oprlsortop != 0;

regards, tom lane

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster

Michael Fuhr <mike (AT) fuhr (DOT) orgwrites:
The 8.0.5 Release Notes have the following item, but without seeing
your query it's hard to know whether this fix is relevant to your
problem (the fix mentions RIGHT JIN; your error says FULL JIN):

Good catch, but that bug was specific to left/right joins (basically,
the code failed to force the join to be flipped around when needed).
With a FULL JIN you're stuck flipping it doesn't help.

The reason it's an issue is that for a FULL JIN, the executor has to
keep track of whether rows on *both* sides of the join have been matched
to any rows of the other side. If there are join conditions that are
outside the mergejoin list then this requires an indefinitely large
amount of state.

It'd be possible to teach hash join to implement FULL JIN (basically,
you'd have to add an I've-been-joined flag to each entry in the hash
table, and then re-scan the hash table at the end of the join to see
which inner-side rows remain unjoined). With this you'd only need one
hashable join condition to make it work, whereas the FULL JIN mergejoin
code requires *all* the join conditions to be mergejoinable. The issue
hasn't come up often enough to make it seem like a high-priority
problem, however. I can only recall one or two people complaining about
it in all the time we've had outer-join support.

regards, tom lane

(end of broadcast)
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

2006-01-15 13:54, Michael Fuhr wrote:
What's the query?

SELECT count(*) FRM "GenAppExtra" FULL JIN "GeoRestrict" N callsign
~ pattern WHERE region_id = 4 R geo_region = 4;

If either of the "region_id = 4" (a field in the right-hand table) or
"geo_region = 4" (a field in the left-hand table) are removed, the
SELECT functions (does not give an error message), as does the case
where the "callsign ~ pattern" (one field is from each table) is
replaced by "callsign = pattern".

What's frustrating is that both tables are small, and "GeoRestrict" is
only about 15 rows.

At this point, I'm considering a UNIN; alternate suggestions welcome!
-- Dean

(end of broadcast)
TIP 5: don't forget to increase your free space map settings

"Dean Gibson (DB Administrator)" <postgresql4 (AT) ultimeth (DOT) comwrites:
SELECT count(*) FRM "GenAppExtra" FULL JIN "GeoRestrict" N callsign
~ pattern WHERE region_id = 4 R geo_region = 4;

If either of the "region_id = 4" (a field in the right-hand table) or
"geo_region = 4" (a field in the left-hand table) are removed, the
SELECT functions (does not give an error message),

Really? The FULL JIN condition using ~ is the source of the failure,
and I'd be quite surprised if changing WHERE makes it work.

regards, tom lane

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster

2006-01-15 15:21, Tom Lane wrote:
Really? The FULL JIN condition using ~ is the source of the failure, and I'd be quite surprised if changing WHERE makes it work.

Works fine:

EXPLAIN SELECT count(*) FRM "Extra" FULL JIN "GeoRestrict" N
callsign ~ pattern WHERE geo_region =
4;
QUERY
PLAN

Aggregate (cost=1934.021934.02 rows=1 width=0)
-Nested Loop Left Join (cost=1.181926.66 rows=2943 width=0)
Join Filter: ("outer".callsign ~ ("inner".pattern)::text)
-Seq Scan on "Extra" (cost=0.00866.00 rows=2943 width=10)
Filter: (geo_region = 4)
-Materialize (cost=1.181.34 rows=16 width=7)
-Seq Scan on "GeoRestrict" (cost=0.001.16 rows=16
width=7)

Note that this used to be just a LEFT JIN (which also worked), but
today I wanted to include rows from "GeoRestrict" that had nulls for the
left-hand-side of the query, so I changed the LEFT JIN to a FULL JIN
(that worked), and then attempted to add a condition to restrict which
rows were included from "GeoRestrict", and that gave the error.
-- Dean

(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

2006-01-15 15:42, Tom Lane wrote:

>EXPLAIN SELECT count(*) FRM "Extra" FULL JIN "GeoRestrict" N
>callsign ~ pattern WHERE geo_region =
>4;
>
>
, but that reduces it to a left join, as you can see in the EXPLAIN
output
Yes, I previously noticed that in the EXPLAIN output too.

any null-extension rows from the right side are going to fail
the WHERE condition anyway, so the planner simplifies the FULL JIN to a
LEFT JIN. If you'd eliminate the WHERE altogether then the failure
will come back.

Tried that, and you are right there as well.

So, given the fact that the right-hand-table is only about 15 rows, do
you think changing the SELECT back to a LEFT JIN, and then using a
UNIN to get the extra right-hand-rows in, is the best work-around?
-- Dean

(end of broadcast)
TIP 6: explain analyze is your friend

"Dean Gibson (DB Administrator)" <postgresql4 (AT) ultimeth (DOT) comwrites:
So, given the fact that the right-hand-table is only about 15 rows, do
you think changing the SELECT back to a LEFT JIN, and then using a
UNIN to get the extra right-hand-rows in, is the best work-around?

Yeah, a UNIN of left and right joins seems the only very reasonable
solution. As long as you're sure there are no duplicate rows you
need to keep, it'll work well enough.

regards, tom lane

(end of broadcast)
TIP 4: Have you searched our list archives?

http://archives.postgresql.org