I wrote: > I suppose the change I made here > http://archives.postgresql.org/pgsql-committers/2009-04/msg00329.php > to improve constant-join-qual handling is what is preventing the > assertion failure, though I'm still not quite sure why (I'd better look > closer to see if there is still some form of the bug lurking).
After tracing through this, that change isn't actually related at all. The bug was introduced last June (so it's in 8.3.recent too) and was fixed here: http://archives.postgresql.org/pgsql-committers/2009-04/msg00203.php The actual issue can be boiled down to approximately this: select ... from a left join b on something where false and exists(select 1 from c where something-else) (IOW, the critical constant-false is actually the occurrence of "ad_tab_id = to_number(1)" in the outer WHERE, not the one in the EXISTS as I'd supposed.) What happens is that when distribute_qual_to_rels is given the constant-false qual to process, it correctly determines that this should be pushed to the top of the join tree; which is not where it is syntactically, because at this point we've rewritten the whole thing to select ... from (select ... from a left join b on something where false) semi join c on something-else if you pretend that SQL has SEMI JOIN as a native join type. So this means we execute these lines: /* if not below outer join, push it to top of tree */ if (!below_outer_join) relids = get_relids_in_jointree((Node *) root->parse->jointree, false); after which, relids is no longer a subset of qualscope (since qualscope is only a+b whereas the entire join tree is a+b+c). So if the check_outerjoin_delay call a few lines further down happens to return true, the "Assert(bms_is_subset(relids, qualscope))" after that will fail. It formerly was, and now is again, the case that check_outerjoin_delay can only return true if it enlarges the relids set, which of course is impossible if relids is already the whole join tree. So that's why the bug wasn't seen before, even though the logic has been like this for a long time. The patch I applied last June made it possible for check_outerjoin_delay to return true if there were any outer join within the passed-in relids set (which is why the LEFT JOIN is a critical part of the example). While that patch turned out to be wrong, it seems like this code is a bit on the fragile side. What I'm thinking is that when we set relids to the whole jointree, we ought to simultaneously set qualscope to the same value; that's only one extra assignment and it will prevent any future recurrence of the crash if check_outerjoin_delay gets changed again. This behavior is semantically sensible because what this code is doing is pretending that the qual appeared at the top syntactic level to begin with; if it had been there then qualscope would match. Kind of a long-winded explanation of what will be a one-line patch, but there you have it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
