Re: Postgres_fdw join pushdown - wrong results with whole-row reference

On Mon, Jun 27, 2016 at 2:47 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> On Sat, Jun 25, 2016 at 12:44 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Jun 22, 2016 at 4:11 AM, Amit Langote
>> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> >> In an outer join we have to differentiate between a row being null
>> >> (because
>> >> there was no joining row on nullable side) and a non-null row with all
>> >> column values being null. If we cast the whole-row expression to a text
>> >> e.g. r.*::text and test the resultant value for nullness, it gives us
>> >> what
>> >> we want. A null row casted to text is null and a row with all null
>> >> values
>> >> casted to text is not null.
>> >
>> > You are right.  There may be non-null rows with all columns null which
>> > are
>> > handled wrongly (as Rushabh reports) and the hack I proposed is not
>> > right
>> > for.  Especially if from non-nullable side as in the reported case, NULL
>> > test for such a whole-row-var would produce the wrong result.  Casting
>> > to
>> > text as your patch does produces the correct behavior.
>>
>> I agree, but I think we'd better cast to pg_catalog.text instead, just
>> to be safe.  Committed that way.
>
> postgres_fdw resets the search path to pg_catalog while opening connection
> to the server. The reason behind this is explained in deparse.c
>
>  * We assume that the remote session's search_path is exactly "pg_catalog",
>  * and thus we need schema-qualify all and only names outside pg_catalog.

Hmm.  OK, should we revert the schema-qualification part of that
commit, or just leave it alone?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company