Re: BUG #15475: Views over CITEXT columns return no data

On Fri, Nov 2, 2018 at 2:29 PM Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Fri, Nov 2, 2018 at 2:07 PM Andrew Gierth
> <andrew@tao11.riddles.org.uk> wrote:
> > >>>>> "Paul" == Paul Schaap <ps@ipggroup.com> writes:
> >  Paul> Hi Andrew,
> >  Paul> Bingo, set enable_parallel_hash=false; gets a correct result
> >  Paul> whereas set enable_parallel_hash=true; gets 0.
> >
> >  Paul> Yes I might have reversed some of the explains, my excuse its
> >  Paul> Friday and I went to bed late and am burnt out today :-)
> >
> > Are all the values of the my_citext column actually null?
>
> Thanks for the report Paul and the analysis Andrew.  Discussed with
> Andrew a bit on IRC.  Summary: multi-batch left joins are not handling
> NULLs correctly in the left table when partitioning.  Looking into
> this now.

Here's a repro.

create table r as select generate_series(1, 1000000) i, null::int j;
update r set j = i where i <= 10;
create table s as select generate_series(1, 1000000) i;
analyze;
select count(*), count(r.j) from r left join s on r.j = s.i;

Unpatched master gives me a 16 batch Parallel Hash Join with the
incorrect answer:

 count | count
-------+-------
    10 |    10

With the attached patch the answer is correct:

  count  | count
---------+-------
 1000000 |    10

The brown-paper-bag level fix is:

-                  false, /* outer join, currently unsupported */
+                  HJ_FILL_OUTER(hjstate),

It is right and full outer joins that are not yet supported by
Parallel Hash Join.  Left outer joins *are* supported.  The effect of that
thinko is to make them behave like inner joins (but only in multi-batch
joins, ie when work_mem is exceeded).

--
Thomas Munro
http://www.enterprisedb.com