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

Hi Jeff,

I have it now but it might be difficult for you to reproduce.

1) pg_dump a lookup table from a Postgres 10(.5) with -Fc and pg_restore to Postgres 11
2) Join to the table in a view, but when you join do an INTEGER cast

CREATE OR REPLACE VIEW citext_view AS
SELECT citext_table.*
FROM citext_table
LEFT OUTER JOIN lookuptable ON citext_table.some_link:INT = lookuptable.linkvalue;

3) Test with different quantities of data as it works with smaller quantities of data, but fails with larger:

-- Works with 1,000
INSERT INTO citext_table SELECT NULL, 'A', GENERATE_SERIES(1, 1000);
SELECT type_id, COUNT(*) FROM citext_view WHERE type_id NOT IN ('2', '3') GROUP BY type_id;
-- gets a 1000 value

-- Doesn't work with 1,000,000
TRUNCATE TABLE citext_table;
INSERT INTO citext_table SELECT NULL, 'A', GENERATE_SERIES(1, 1000000);
SELECT type_id, COUNT(*) FROM citext_view WHERE type_id NOT IN ('2', '3') GROUP BY type_id;
-- No rows come back

n.b. I see nothing in the postgresql.log

The workarounds I have discovered are:
A) pg_dump in 11 and restore back in 11
B) pg_dump from 10 but NOT -Fc (so SQL) and restore to 11
C) Reverse the logic on the JOIN to citext_table.some_link = lookuptable.linkvalue::CITEXT;

CHEERS
Paul

> On 1 Nov 2018, at 10:06, Paul Schaap <ps(at)ipggroup(dot)com> wrote:
>
> Hi Jeff,
>
> That was fast :-)
>
> I am trying to create a script that reproduces the issue as it seems it is not quite that simple, please bear with me.
>
> datcollate en_US.UTF-8
> datctype en_US.UTF-8
> SERVER_ENCODING UTF8
>
> CHEERS
> Paul
>
>> On 1 Nov 2018, at 10:00, Jeff Janes <jeff(dot)janes(at)gmail(dot)com <mailto:jeff(dot)janes(at)gmail(dot)com>> wrote:
>>
>> On Wed, Oct 31, 2018 at 6:45 PM PG Bug reporting form <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org>> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 15475
>> Logged by: Paul Schaap
>> Email address: ps(at)ipggroup(dot)com <mailto:ps(at)ipggroup(dot)com>
>> PostgreSQL version: 11.0
>> Operating system: OSX 10.14 and Ubuntu 18.10
>> Description:
>>
>> The steps to reproduce are:
>> 1) Install CITEXT extension
>> 2) Create a table with a CITEXT column
>> 3) Create a view (not materialised) over the table
>> 4) Query the view filtering on the CITEXT column
>>
>> No data gets returned when querying via the view, this works under
>> Postgresql 10 but not 11.
>>
>>
>> There is no step here where you populate the table.
>>
>> Add one, I can't reproduce this (on Ubuntu 16.04, so maybe that is important).
>>
>> create extension citext ;
>> create table j1 (x citext);
>> insert into j1 values ('abc');
>> create view j2 as select * from j1;
>> select * from j2 where x ='aBc';
>>
>> <see a row>
>>
>> What are your encoding, collation, and ctype?
>>
>> Cheers,
>>
>> Jeff
>

--

PRIVACY & CONFIDENTIALITY NOTICE

The information contained in this
email is intended for the named recipients  only.  It may contain
privileged and confidential information and if you are  not the intended
recipient, you should not copy it or disclose its contents  to any other
person, or take any action in reliance on it.  If you have  received this
email in error, please notify us immediately by return email.