Re: Getting non_NULL right-side values on a non-matching join?

On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > If you just need a work-around-it-right-now solution, I'd suggest
> > introducing an "OFFSET 0" optimization fence into one or another of the
> > levels of view below the outer joins. I've not experimented but I think
> > that ought to fix it, at some possibly-annoying cost in query
> > optimization. Hopefully I'll have another answer tomorrow.
>
> I found a less nasty workaround: if you replace "my_field" by
> "foo.my_field" in the SELECT list of boo_top_view, the problem goes away.
> The bug seems to be due to wrong processing of join alias variables
> during subquery pullup. The unqualified name "my_field" is a reference
> to an output alias of the unnamed LEFT JOIN in that view, but if you
> qualify it with the name of the component table, it's not an alias
> anymore so the bug doesn't trigger.
>
> Thanks for reporting this! I'll try to make sure there's a real fix
> in the next update releases, which will be out PDQ because of the
> replication bug that was identified this week.
>
> regards, tom lane
>

I'm happy to be of assistance, but mostly glad it wasn't a me-bug! :) I
put an alias on every field reference in the view, and the problem did
indeed go away. Thank you very much for providing an easy workaround!

Cheers,
Ken

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ <http://agency-software.org/>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.