Re: Getting non_NULL right-side values on a non-matching join? - Mailing list pgsql-general

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