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;
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
The following bug has been logged on the website:
Bug reference: 15475
Logged by: Paul Schaap
Email address: ps@ipggroup.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.
