Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oMyzT-0007k2-Q7 for pgsql-bugs@arkaria.postgresql.org; Sat, 13 Aug 2022 21:48:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oMyzR-0003qH-Va for pgsql-bugs@arkaria.postgresql.org; Sat, 13 Aug 2022 21:48:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oMyzR-0003pF-Ky for pgsql-bugs@lists.postgresql.org; Sat, 13 Aug 2022 21:48:17 +0000 Received: from mout.kundenserver.de ([212.227.17.13]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oMyzJ-000269-AG for pgsql-bugs@lists.postgresql.org; Sat, 13 Aug 2022 21:48:16 +0000 Received: from MacBook-Pro.fritz.box ([37.201.41.94]) by mrelayeu.kundenserver.de (mreue109 [213.165.67.113]) with ESMTPSA (Nemesis) id 1MvJsF-1nWfY51p8D-00rFSb; Sat, 13 Aug 2022 23:48:04 +0200 Subject: Re: BUG #17584: SQL crashes PostgreSQL when using ICU collation To: Euler Taveira Cc: pgsql-bugs@lists.postgresql.org References: <17584-95c79b4a7d771f44@postgresql.org> From: James Inform Autocrypt: addr=james.inform@pharmapp.de; keydata= xjMEYafuxBYJKwYBBAHaRw8BAQdAbBiKs8+VgLFHJH+OWhko0ZJRtL4mHkwz1sHvjR7ykI/N J0phbWVzIEluZm9ybSA8amFtZXMuaW5mb3JtQHBoYXJtYXBwLmRlPsKVBBAWCgAmBQJhp+7E BQkJZgGABgsJBwgDAgQVCAoCBBYCAQACGQECGwMCHgEAIQkQNghb50u3mv0WIQRjN5aMjf98 k+czuhU2CFvnS7ea/fWKAQDCVBgwqLe1E6oWQFFGc4Yl/zJUpvM8VCnipaWhYjv9xwD/SzL8 Y+G6hNDfXZyBwZJ/6mfGI8Wz6GezqevvTBWh2wLOOARhp+7EEgorBgEEAZdVAQUBAQdAopXK CmcibmTFDsPkzIDqMip/MhzT8v78OkmKqrgwC3kDAQgHwn4EGBYIAA8FAmGn7sQFCQlmAYAC GwwAIQkQNghb50u3mv0WIQRjN5aMjf98k+czuhU2CFvnS7ea/RYXAP4iaXxt4fWaGRBgmj2B L1vNjwowq81NGGuoH59BvSr8XwEAmijZ0e/n/Ida+ERteogSH/2A6l8V1041fjwv8lPDhwU= Message-ID: Date: Sat, 13 Aug 2022 23:48:01 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:52.0) Gecko/20100101 PostboxApp/7.0.56 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------7751E1E9D21824E28D34E8D6" Content-Language: de-DE X-Provags-ID: V03:K1:pcfx2j95LCm6pXTVPyGA8Azlfc9upVIWYtwk6TmBohBEo1E8JXq Q7GwKXUFLrf/5j7HpPuzYYXl58D2xqSmQtdzn3mxLU89Q3+ODXyTIbSP0E1hcaJdoyb907e 1azBPemuz8SktUjamiwZZx3Dl8QXwMuUifdGBddU298QTMJN1/ruqq9IX70ZK8koq9y3CtO iHxkS7ppH2eLG6oHtpXcA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:DckIVE3fCGE=:K9RY2aK6Y5sCEcMp0u870G phv89ehX9aKfX1dP50M/IG2ZDWf7QfVRXPy0xsgqQBMd9F7sMy+lf6xPeenYDzDLg8WW+M96v kRs5ydH8+crAEbbN/kmDb802CDe92/UNzbzDbxdzIWS1aMT2snZPnoUgWakwl81er6fPd6fPr Fs+AZ4lhzaWt4j84iSwg8VCnSzGIfn7IoWr5yagLlVeQVjhqH6+bvvQCMlgZvpWM9UATqIzKc zKnORRD/t9QEWk9a1gAW+i0IZGrB1cdcdoaKDSyGfHU3LTYeiROa3+Y5DIJ/MhmyUnBngZYlX 2Vt/DSEs0r6v0f0McYN1f2cqN4gtSBkzM6lx6rTR0cUrCFvV7/Z05RPk5VHJc9BwduffyXocv CbnwRtFht3xUv01ox+URMcv4E+8YI/qEkoMz0/V7koR3g/BjX/027157T5LvWUJcfE8jK+SPF plR1M2alVGiBgN9GvlQGJOgxPK9I8jeBR5kMSoAfL58XJebM1v2VV5ybx0TAb9Rq3Ms2OCJlr n2X8lOO7dFhOZtLsF5bN2ENp2XwwJYgn4Ls93pOFBaGaexomUx72ryLHquduQzEjFEf9lOupL qaQbqPJuScfVw38VaAio0TjQRxWK+aK4UoqLB6FHns23BEVbwiH1rYqHQT7kFj2E/tflzW5XT X2dZj+5rNCrvgv0knL5NoDrb7+iHGjPvzaq18s5ZZYLP4BHkI6t99jiT4TfsrtIxa8i2ERmlG 7g9F46KsjM4vBJjHr+hbPrmIwB4qEQEBUqE7NKlsgWClDoW7FSlwKHrGe/M= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------7751E1E9D21824E28D34E8D6 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit How can I add an attachment? The bug reporting form doesn't have an upload function. My attachemnt is 8MB. > Euler Taveira > 12. August 2022 um 21:25 > On Fri, Aug 12, 2022, at 11:18 AM, PG Bug reporting form wrote: > You forgot to attach the file. You should also provide additional > information > about your setup. > > * OS information ("Linux" without a distro name and version is too > generic); > * Postgres information (pg_config --configure); > * database information (\l output); > * ICU package information (sudo dkpg -l | grep icu) or >   (sudo yum list installed | grep libicu). > > > -- > Euler Taveira > EDB   https://www.enterprisedb.com/ > > PG Bug reporting form > 12. August 2022 um 16:18 > The following bug has been logged on the website: > > Bug reference: 17584 > Logged by: James Inform > Email address: james.inform@pharmapp.de > PostgreSQL version: 14.5 > Operating system: Linux / Mac > Description: > > This bug appears in PG up to 14.5. > > I have attached a data file in 7z format. Please extract the "data..csv" > from it and run the following statements in a newly created database on > PostgreSQL 14.x. > > -- 1. Create collation and implizit cast > create collation mycol from "de-DE-x-icu"; > create cast (varchar as integer) with inout as implicit; > > -- 2. Create table > create table testtab ( > a1 text not null collate "mycol", > b2 text not null collate "mycol", > c3 text not null collate "mycol", > d4 varchar(6) null collate "mycol", > e5 varchar(2) null collate "mycol", > f6 varchar(3) null collate "mycol", > g7 varchar(10) null collate "mycol", > h8 varchar(10) null collate "mycol", > i9 text null collate "mycol", > j10 text null collate "mycol", > k11 varchar(10) null collate "mycol", > l12 text null collate "mycol", > m13 varchar(10) null collate "mycol", > n14 varchar(500) null collate "mycol", > o15 varchar(80) null collate "mycol", > p16 varchar(80) null collate "mycol", > q17 varchar(10) null collate "mycol", > r18 varchar(60) null collate "mycol", > s19 varchar(10) null collate "mycol", > t20 varchar(10) null collate "mycol", > u21 text null collate "mycol", > v22 varchar(1) null collate "mycol", > w23 varchar(250) null collate "mycol", > x24 varchar(1000) null collate "mycol", > y25 varchar(1000) null collate "mycol", > z26 varchar(1000) null collate "mycol", > a27 varchar(1000) null collate "mycol", > b28 varchar(1000) null collate "mycol", > c29 varchar(1000) null collate "mycol", > d30 varchar(1000) null collate "mycol", > e31 varchar(1000) null collate "mycol", > f32 varchar(1000) null collate "mycol", > g33 int8 null, > h34 varchar(2) null collate "mycol", > i35 varchar(2) null collate "mycol", > j36 varchar(2) null collate "mycol", > k37 varchar(250) null collate "mycol", > l38 varchar(250) null collate "mycol", > m39 varchar(2) null collate "mycol", > n40 timestamptz null, > o41 timestamptz null, > p42 int8 null, > q43 int8 null, > r44 int8 not null, > constraint ak_1 unique (r44), > constraint pk_1 primary key (a1) > ) > ; > > -- 3. Load data into table > copy testtab from '{PATH_TO_YOUR_FILE}/data.csv' csv header; > > -- 4. Create index and analyze table > create index on testtab using btree (i9); > analyze testtab; > > -- ERROR > -- 5. This is where the error occurs > -- ERROR: could not find block containing chunk 0x > select count(*) from > ( > select vou_d.* from > ( > select > r44, > i9, > j10, > n14 > from > testtab > ) vou_d > order by i9 asc, j10 asc > ) a > ; > --------------7751E1E9D21824E28D34E8D6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
How can I add an attachment?
The bug reporting form doesn't have an upload function.

My attachemnt is 8MB.
12. August 2022 um 21:25
On Fri, Aug 12, 2022, at 11:18 AM, PG Bug reporting form wrote:
You forgot to attach the file. You should also provide additional information
about your setup.

* OS information ("Linux" without a distro name and version is too generic);
* Postgres information (pg_config --configure);
* database information (\l output);
* ICU package information (sudo dkpg -l | grep icu) or
  (sudo yum list installed | grep libicu).


--
Euler Taveira
EDB   https://www.enterprisedb.com/

12. August 2022 um 16:18
The following bug has been logged on the website:

Bug reference: 17584
Logged by: James Inform
Email address: james.inform@pharmapp.de
PostgreSQL version: 14.5
Operating system: Linux / Mac
Description:

This bug appears in PG up to 14.5.

I have attached a data file in 7z format. Please extract the "data..csv"
from it and run the following statements in a newly created database on
PostgreSQL 14.x.

-- 1. Create collation and implizit cast
create collation mycol from "de-DE-x-icu";
create cast (varchar as integer) with inout as implicit;

-- 2. Create table
create table testtab (
a1 text not null collate "mycol",
b2 text not null collate "mycol",
c3 text not null collate "mycol",
d4 varchar(6) null collate "mycol",
e5 varchar(2) null collate "mycol",
f6 varchar(3) null collate "mycol",
g7 varchar(10) null collate "mycol",
h8 varchar(10) null collate "mycol",
i9 text null collate "mycol",
j10 text null collate "mycol",
k11 varchar(10) null collate "mycol",
l12 text null collate "mycol",
m13 varchar(10) null collate "mycol",
n14 varchar(500) null collate "mycol",
o15 varchar(80) null collate "mycol",
p16 varchar(80) null collate "mycol",
q17 varchar(10) null collate "mycol",
r18 varchar(60) null collate "mycol",
s19 varchar(10) null collate "mycol",
t20 varchar(10) null collate "mycol",
u21 text null collate "mycol",
v22 varchar(1) null collate "mycol",
w23 varchar(250) null collate "mycol",
x24 varchar(1000) null collate "mycol",
y25 varchar(1000) null collate "mycol",
z26 varchar(1000) null collate "mycol",
a27 varchar(1000) null collate "mycol",
b28 varchar(1000) null collate "mycol",
c29 varchar(1000) null collate "mycol",
d30 varchar(1000) null collate "mycol",
e31 varchar(1000) null collate "mycol",
f32 varchar(1000) null collate "mycol",
g33 int8 null,
h34 varchar(2) null collate "mycol",
i35 varchar(2) null collate "mycol",
j36 varchar(2) null collate "mycol",
k37 varchar(250) null collate "mycol",
l38 varchar(250) null collate "mycol",
m39 varchar(2) null collate "mycol",
n40 timestamptz null,
o41 timestamptz null,
p42 int8 null,
q43 int8 null,
r44 int8 not null,
constraint ak_1 unique (r44),
constraint pk_1 primary key (a1)
)
;

-- 3. Load data into table
copy testtab from '{PATH_TO_YOUR_FILE}/data.csv' csv header;

-- 4. Create index and analyze table
create index on testtab using btree (i9);
analyze testtab;

-- ERROR
-- 5. This is where the error occurs
-- ERROR: could not find block containing chunk 0x
select count(*) from
(
select vou_d.* from
(
select
r44,
i9,
j10,
n14
from
testtab
) vou_d
order by i9 asc, j10 asc
) a
;


--------------7751E1E9D21824E28D34E8D6--