Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jIAwJ-0007sQ-9d for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Mar 2020 12:51:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jIAwI-0004uO-1e for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Mar 2020 12:51:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from <9erthalion6@gmail.com>) id 1jIAwH-0004uH-JY for pgsql-bugs@lists.postgresql.org; Sat, 28 Mar 2020 12:51:49 +0000 Received: from mail-wr1-x444.google.com ([2a00:1450:4864:20::444]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1jIAwF-0000sO-9J for pgsql-bugs@lists.postgresql.org; Sat, 28 Mar 2020 12:51:48 +0000 Received: by mail-wr1-x444.google.com with SMTP id 31so15051818wrs.3 for ; Sat, 28 Mar 2020 05:51:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=TOoNe6Znwlz7x6LrwbyhfeApEBW8ZJ3GpwtAiCSG6ik=; b=tG8kdZnmE/MhpqlXGcGAOg0qCHK2ccDT4J+oQJNOCKqYVINZAC+UbmkE3voqIrbFQE uPHXlAPi7KuqSrXyTU/HPdS3TrRzQVlU9iFfdKwNb84b7gO4iEoQe+QpnfAuGK7yjILH GxZ/UzB5Z/D1NY6e9z7w4BGyaDHVKuhr7gAZnlfDlsZgAvR0LRcBKomcl5TNnaMKMUB6 ypDH6sxmR165055qvIRoatl+BrRCdbFwGspSydpjpT+7jxVjjwy/y+98DWSccKl9Ooe1 RWuRQ+jBp7ycQwa6nYxu4x+CB9TnYTMRKVcEYe87BEoLVHn2bFpHcQ9skj500r2gw8MP 0Dmg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=TOoNe6Znwlz7x6LrwbyhfeApEBW8ZJ3GpwtAiCSG6ik=; b=eJ/0yg9WMxmI0J+nETWXG80JlMCRjJl/QUad8i5d2eVlHM4GgEKdWewvRt9zjk4JpA l7CAD7Z2BWXUK39OzqCpkP7u2LyzdiwpVUn3MKIcAUu+bEid/kRwR/jxOGKKisS/YYHY kQlMEd9yMTbWCFhlJ2xGZBePdAUwdaG/QtMsI2ZFWFKlq43GVZZExAWzSbkw1PEHGRAs 408uVGmlozv/JjAq2ADEGqZlgemrqXBSNH4EX6743g+RSkm5fG2opPez5v1dQbicjHkp Ibnz1dng+ebjTh7UpJzFdIMciYT/qkw5RdfLz+Tv3HGSwumvuwVvHSvQNy+PPSJMMWNQ 0Fnw== X-Gm-Message-State: ANhLgQ2XccmHI7Zl0lSrb6RfS33DWAcHt4pYQKxq1FFWu5F1idB+qJmy WYpgdGvm0PBnqn6mOcR+DWM= X-Google-Smtp-Source: ADFU+vtXCXo5uyt9/WCA63pW9+fZFTxLQqEK4aJZsWrKHd1oJN/NRpPgtvauOECMXndluxxSO8BCtQ== X-Received: by 2002:adf:e304:: with SMTP id b4mr4874957wrj.352.1585399906063; Sat, 28 Mar 2020 05:51:46 -0700 (PDT) Received: from localhost (dslb-094-222-004-201.094.222.pools.vodafone-ip.de. [94.222.4.201]) by smtp.gmail.com with ESMTPSA id a71sm11843738wmd.22.2020.03.28.05.51.45 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 28 Mar 2020 05:51:45 -0700 (PDT) Date: Sat, 28 Mar 2020 13:53:03 +0100 From: Dmitry Dolgov <9erthalion6@gmail.com> To: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16325: Assert failure on partitioning by int for a text value with a collation Message-ID: <20200328125303.4me6vetvzgebobw5@localhost> References: <16325-809194cf742313ab@postgresql.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="cgdm4l6t4xz6hpqy" Content-Disposition: inline In-Reply-To: <16325-809194cf742313ab@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --cgdm4l6t4xz6hpqy Content-Type: text/plain; charset=us-ascii Content-Disposition: inline > On Sat, Mar 28, 2020 at 05:43:05AM +0000, PG Bug reporting form wrote: > > > The following query: > create table parted (i int) partition by list (i); > create table part_coll partition of parted for values in ('1' collate > "POSIX"); > > leads to an assert failure with the following stack trace: Thanks for reporting! Looks like transformPartitionBoundValue needs to check that the source collumn is collatable, then we get more expected result: =# create table part_coll partition of parted for values in ('1' collate "POSIX"); ERROR: 42804: collations are not supported by type integer --cgdm4l6t4xz6hpqy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="part_value_collations.patch" diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index c1911411d0..04e24f6a8e 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4069,6 +4069,23 @@ transformPartitionBoundValue(ParseState *pstate, Node *val, { Oid exprCollOid = exprCollation(value); + if (type_is_collatable(colType)) + { + if (!OidIsValid(exprCollOid)) + ereport(ERROR, + (errcode(ERRCODE_INDETERMINATE_COLLATION), + errmsg("could not determine which collation to use for partition expression"), + errhint("Use the COLLATE clause to set the collation explicitly."))); + } + else + { + if (OidIsValid(exprCollOid)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("collations are not supported by type %s", + format_type_be(colType)))); + } + if (OidIsValid(exprCollOid) && exprCollOid != DEFAULT_COLLATION_OID && exprCollOid != partCollation) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index c5e95edbed..abd1bfe2b9 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -594,6 +594,8 @@ CREATE TABLE list_parted ( CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); +CREATE TABLE part_p4 PARTITION OF list_parted FOR VALUES IN ('1' collate "POSIX"); +ERROR: collations are not supported by type integer CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted Partitioned table "public.list_parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 00ef81a685..3fe77a7711 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -522,6 +522,7 @@ CREATE TABLE list_parted ( CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); +CREATE TABLE part_p4 PARTITION OF list_parted FOR VALUES IN ('1' collate "POSIX"); CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted --cgdm4l6t4xz6hpqy--