Re: could not devise a query plan

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano(at)mailbox(dot)hu> writes:
> I couldn't find the string of my email's subject on the web, except for one
> place: the PostgreSQL source :)

Seems that you've managed to tickle a strange corner case, which can be
reduced to simplest form like this:

regression=# select * from a full join b on true;
ERROR: Unable to devise a query plan for the given query

In your first example, the empty join clause falls out because you don't
actually have any columns of the same names on both sides, and so the
NATURAL join doesn't find any columns to join. AFAICS this is allowed
by the SQL spec, but still I can't help suspecting that it is
programming error on your part. You're going to get a cross-product
join ... is that really what you intended?

The second example is slightly more interesting: it boils down to a case
like this:

select * from
(select unique1 from tenk1 where unique1 = 42) a
full join
(select unique1 from tenk1 where unique1 = 42) b
on a.unique1 = b.unique1;

7.4 is perhaps too smart for its own good here: it is able to figure out
that the join clause is redundant because every row coming up from the
subselects must have the same value in the join columns (here, 42). So
it discards the join clause ... leaving it in the same situation where
it can't generate a plan :-(

Although I think the first case is really user error, the second case
looks like it could arise unexpectedly in program-generated queries
given the right combination of inputs, so we probably ought to do
something about it. I have applied the attached patch to 7.4.
(It would probably work in 7.3 too, but no guarantees.)

regards, tom lane

Index: costsize.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.115.2.1
diff -c -r1.115.2.1 costsize.c
*** costsize.c 3 Dec 2003 17:45:36 -0000 1.115.2.1
--- costsize.c 6 Apr 2004 18:41:17 -0000
***************
*** 928,950 ****
* all mergejoin paths associated with the merge clause, we cache the
* results in the RestrictInfo node.
*/
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
! &firstclause->left_mergescansel,
! &firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids, outer_path->parent->relids))
{
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel;
}
else
{
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel;
}

/* convert selectivity to row count; must scan at least one row */
--- 928,958 ----
* all mergejoin paths associated with the merge clause, we cache the
* results in the RestrictInfo node.
*/
! if (mergeclauses)
{
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
! &firstclause->left_mergescansel,
! &firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids, outer_path->parent->relids))
! {
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel;
! }
! else
! {
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel;
! }
}
else
{
! /* cope with clauseless mergejoin */
! outerscansel = innerscansel = 1.0;
}

/* convert selectivity to row count; must scan at least one row */
Index: joinpath.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.82
diff -c -r1.82 joinpath.c
*** joinpath.c 25 Sep 2003 06:58:00 -0000 1.82
--- joinpath.c 6 Apr 2004 18:41:18 -0000
***************
*** 489,497 ****
outerpath->pathkeys,
mergeclause_list);

! /* Done with this outer path if no chance for a mergejoin */
if (mergeclauses == NIL)
! continue;
if (useallclauses && length(mergeclauses) != length(mergeclause_list))
continue;

--- 489,515 ----
outerpath->pathkeys,
mergeclause_list);

! /*
! * Done with this outer path if no chance for a mergejoin.
! *
! * Special corner case: for "x FULL JOIN y ON true", there will be
! * no join clauses at all. Ordinarily we'd generate a clauseless
! * nestloop path, but since mergejoin is our only join type that
! * supports FULL JOIN, it's necessary to generate a clauseless
! * mergejoin path instead.
! *
! * Unfortunately this can't easily be extended to handle the case
! * where there are joinclauses but none of them use mergejoinable
! * operators; nodeMergejoin.c can only do a full join correctly if
! * all the joinclauses are mergeclauses.
! */
if (mergeclauses == NIL)
! {
! if (jointype == JOIN_FULL && restrictlist == NIL)
! /* okay to try for mergejoin */ ;
! else
! continue;
! }
if (useallclauses && length(mergeclauses) != length(mergeclause_list))
continue;