Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rNjcl-00F8Q2-MM for pgsql-bugs@arkaria.postgresql.org; Thu, 11 Jan 2024 01:12:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rNjcj-008OnC-Hg for pgsql-bugs@arkaria.postgresql.org; Thu, 11 Jan 2024 01:12:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rNjch-008On2-MP for pgsql-bugs@lists.postgresql.org; Thu, 11 Jan 2024 01:12:45 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rNjcc-000vZl-Tu for pgsql-bugs@lists.postgresql.org; Thu, 11 Jan 2024 01:12:42 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 4CF3E5C0121; Wed, 10 Jan 2024 20:12:37 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Wed, 10 Jan 2024 20:12:37 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1704935557; x=1705021957; bh=Qrq/D83obC Veyd8erLwDaYI95i7UJCLD+jGjbMyoZFo=; b=ZnSPDbEp4XWRqz+aK3kzySss3i 6/eW3emqYc5dQQw5lSH9hkupeAd+hLmQzuE7Illx2Go2wLXHqA7myMvyxdTWbbdw mdBD736Q2L+OYbYoQamATlX1gpwFibEP54TucX9GyKeSATbs1HJElc1en+yM+3ta KgYPLslNnLakfFBXhKB9/iyYOhm8IgvfoaHXQ+DsvA2mojFsDS2SAbK4X+jqm9R3 QLPQ/1iga2f32Uxd+xY3p+23t/gLLSmc/DONBRmefsM/XbBtIqIfdblnv81n7MjO a3GqyCBJrYOV7sfGYgjWtHPuMkJhqkUjTE9sC6HZCG/uy9GDrMNmxQhCJwhQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1704935557; x=1705021957; bh=Qrq/D83obCVeyd8erLwDaYI95i7U JCLD+jGjbMyoZFo=; b=z+CoB4hg3WP8p3cuJ9tati189qu1chL5G6zctvn7R6Sh oZXV6+YJgZNcWFnyn4B2ou8vDBfTy8nynDWT3keP/u/4gY/U6i5NhtPuU1zNCviK NNjiYB1Ywpk4qfSy6Zb9ertzosCdNIA19qOhe+lQ356Wj2zi5L3oaVgBAcCBD83N t3C2Mjlo96V40KB3h/WQioPCX1mXU5+klxkwTe4WfIWYQQt2E3Yxt3VjzbDTre9l Roc8HuMNWVWO99JX6O0ToWnHjacn0RHCdv9zq4vPPCHvTqw0D9m6qCeV7+0fmXjD /eyPQzcnja/Y8VDH1DIKXrcOP7OYFB0IJpWFxPOitg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeivddgvdelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvvefukfhfgggtuggjsehgtderredt tddvnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesph grqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepteelieefudffhffhtdetleeg geegfffhkeeuveetiefgudduvedutefggeeivdejnecuvehluhhsthgvrhfuihiivgeptd enucfrrghrrghmpehmrghilhhfrhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiih ii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 10 Jan 2024 20:12:34 -0500 (EST) Date: Thu, 11 Jan 2024 10:12:23 +0900 From: Michael Paquier To: Tom Lane Cc: "Jonathan S. Katz" , David Rowley , exclusion@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18240: Undefined behaviour in cash_mul_flt8() and friends Message-ID: References: <2433182.1702391878@sss.pgh.pa.us> <3453263.1702739958@sss.pgh.pa.us> <1673413.1703442075@sss.pgh.pa.us> <1851211.1703519939@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="QM5bDpVfnuPKU592" Content-Disposition: inline In-Reply-To: <1851211.1703519939@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --QM5bDpVfnuPKU592 Content-Type: multipart/mixed; boundary="0mxAdYGhgnx10yju" Content-Disposition: inline --0mxAdYGhgnx10yju Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, Dec 25, 2023 at 10:58:59AM -0500, Tom Lane wrote: > I'm a little nervous about using tsvector or tsquery, as it seems > pretty plausible that somebody would get around to making hash > support for them someday. Perhaps the same argument could be made > about bit or varbit, but I'd bet a lot less on that happening, > as those are backwater-ish types (not even in the standard > anymore, IIRC). So I'd think about using one of those. Okay, I've used varbit then. > Or we could build a for-test-purposes-only datatype, but that > could require a lot of scaffolding work. It took me some time to come back to this thread, apologies for the delay.=20 I have been looking at all that, and finished for now with the attached patch that removes the dependency to "money" in the main regression test suite in all the relevant places without losing coverage, switching these to use varbit (well, mostly): - Tests for hashing and hash-based plans (6dd8b0080787). - A set of tests in rules used money historically but these can be switched to numeric. - Two tests in stats_ext relied on cash_words() to generate different strings, but the same variance can be achieved without it. - One test in create_table relied on money as its cast is not immutable. Here it is possible to use a timestamptz to achieve the same, with something like to_date() to force a function execution=20 when defining a partition boundary for one of the partitions (from 7c079d7417a8). With this patch, the tests related to money that remain are for type_sanity, window.sql and of course money which are all related to=20 the data type so they could be wiped out once the type is itself removed. While looking at the whole picture, an issue with the direct removal of money is how we should handle btree_gin and btree_gist which have operators based on money. We try to keep things compatible at run-time, but could this be worth a hard break in these modules, dropping the older sql scripts used in the modules if we don't have access to money anymore at runtime? These are not popular modules.. Any thoughts about that? For now, please find attached a patch to adjust the regression tests to depend less on money, which does not depend on the type removal. Comments are welcome. -- Michael --0mxAdYGhgnx10yju Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-Reduce-dependency-to-money-type-in-regression-tests.patch" Content-Transfer-Encoding: quoted-printable =46rom a62b6f731a1c876f172283573f15dc72a572d508 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Thu, 11 Jan 2024 09:51:20 +0900 Subject: [PATCH] Reduce dependency to money type in regression tests Most of these tests have been introduced in 6dd8b0080787. Some other tests historically used money but don't really need it for what they wanted to test. Plans and coverage is unchanged. --- src/test/regress/expected/create_table.out | 13 +-- src/test/regress/expected/hash_func.out | 18 ++-- src/test/regress/expected/multirangetypes.out | 8 +- src/test/regress/expected/rangetypes.out | 10 +- src/test/regress/expected/rules.out | 98 +++++++++---------- src/test/regress/expected/stats_ext.out | 4 +- src/test/regress/expected/union.out | 48 ++++----- src/test/regress/expected/with.out | 6 +- src/test/regress/sql/create_table.sql | 13 +-- src/test/regress/sql/hash_func.sql | 10 +- src/test/regress/sql/multirangetypes.sql | 2 +- src/test/regress/sql/rangetypes.sql | 4 +- src/test/regress/sql/rules.sql | 6 +- src/test/regress/sql/stats_ext.sql | 4 +- src/test/regress/sql/union.sql | 18 ++-- src/test/regress/sql/with.sql | 6 +- 16 files changed, 135 insertions(+), 133 deletions(-) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/= expected/create_table.out index 344d05233a..7828a46095 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -474,13 +474,14 @@ LINE 1: ...REATE TABLE bools_true PARTITION OF bools = FOR VALUES IN (1); ^ DROP TABLE bools; -- specified literal can be cast, and the cast might not be immutable -CREATE TABLE moneyp ( - a money +CREATE TABLE castablep ( + a timestamptz ) PARTITION BY LIST (a); -CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10); -CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11'); -CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99'= )::int); -DROP TABLE moneyp; +CREATE TABLE castablep_1 PARTITION OF castablep + FOR VALUES IN ('2014-01-01 01:00:00'); +CREATE TABLE castablep_2 PARTITION OF castablep + FOR VALUES IN (to_date('2014', 'yyyy-mm-dd')::timestamptz); +DROP TABLE castablep; -- cast is immutable CREATE TABLE bigintp ( a bigint diff --git a/src/test/regress/expected/hash_func.out b/src/test/regress/exp= ected/hash_func.out index 8e23dc3978..2d625a247b 100644 --- a/src/test/regress/expected/hash_func.out +++ b/src/test/regress/expected/hash_func.out @@ -179,11 +179,11 @@ WHERE hash_array(v)::bit(32) !=3D hash_array_extende= d(v, 0)::bit(32) =20 -- array hashing with non-hashable element type SELECT v as value, hash_array(v)::bit(32) as standard -FROM (VALUES ('{0}'::money[])) x(v); -ERROR: could not identify a hash function for type money +FROM (VALUES ('{101}'::varbit[])) x(v); +ERROR: could not identify a hash function for type bit varying SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0 -FROM (VALUES ('{0}'::money[])) x(v); -ERROR: could not identify an extended hash function for type money +FROM (VALUES ('{101}'::varbit[])) x(v); +ERROR: could not identify an extended hash function for type bit varying SELECT v as value, hashbpchar(v)::bit(32) as standard, hashbpcharextended(v, 0)::bit(32) as extended0, hashbpcharextended(v, 1)::bit(32) as extended1 @@ -331,13 +331,13 @@ WHERE hash_record(v)::bit(32) !=3D hash_record_exten= ded(v, 0)::bit(32) =20 DROP TYPE hash_test_t1; -- record hashing with non-hashable field type -CREATE TYPE hash_test_t2 AS (a money, b text); +CREATE TYPE hash_test_t2 AS (a varbit, b text); SELECT v as value, hash_record(v)::bit(32) as standard -FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v); -ERROR: could not identify a hash function for type money +FROM (VALUES (row('10'::varbit, 'aaa')::hash_test_t2)) x(v); +ERROR: could not identify a hash function for type bit varying SELECT v as value, hash_record_extended(v, 0)::bit(32) as extended0 -FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v); -ERROR: could not identify an extended hash function for type money +FROM (VALUES (row('11'::varbit, 'aaa')::hash_test_t2)) x(v); +ERROR: could not identify an extended hash function for type bit varying DROP TYPE hash_test_t2; -- -- Check special cases for specific data types diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regre= ss/expected/multirangetypes.out index a0cb875492..9808587532 100644 --- a/src/test/regress/expected/multirangetypes.out +++ b/src/test/regress/expected/multirangetypes.out @@ -3273,10 +3273,10 @@ NOTICE: drop cascades to type two_ints_range -- Check behavior when subtype lacks a hash function -- set enable_sort =3D off; -- try to make it pick a hash setop implementati= on -select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange; - cashmultirange =20 ------------------ - {($2.00,$5.00)} +select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmult= irange; + varbitmultirange=20 +------------------ + {(01,10)} (1 row) =20 reset enable_sort; diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/ex= pected/rangetypes.out index ee02ff0163..07d5621ef8 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -1761,12 +1761,12 @@ NOTICE: drop cascades to type two_ints_range -- -- Check behavior when subtype lacks a hash function -- -create type cashrange as range (subtype =3D money); +create type varbitrange as range (subtype =3D varbit); set enable_sort =3D off; -- try to make it pick a hash setop implementati= on -select '(2,5)'::cashrange except select '(5,6)'::cashrange; - cashrange =20 ---------------- - ($2.00,$5.00) +select '(01,10)'::varbitrange except select '(10,11)'::varbitrange; + varbitrange=20 +------------- + (01,10) (1 row) =20 reset enable_sort; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expecte= d/rules.out index d878a971df..55f2e95352 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -51,9 +51,9 @@ create rule rtest_pers_del as on delete to rtest_person d= o also -- -- Tables and rules for the logging test -- -create table rtest_emp (ename char(20), salary money); -create table rtest_emplog (ename char(20), who name, action char(10), news= al money, oldsal money); -create table rtest_empmass (ename char(20), salary money); +create table rtest_emp (ename char(20), salary numeric); +create table rtest_emplog (ename char(20), who name, action char(10), news= al numeric, oldsal numeric); +create table rtest_empmass (ename char(20), salary numeric); create rule rtest_emp_ins as on insert to rtest_emp do insert into rtest_emplog values (new.ename, current_user, 'hired', new.salary, '0.00'); @@ -410,13 +410,13 @@ update rtest_emp set ename =3D 'wieck', salary =3D '6= 000.00' where ename =3D 'wiecx'; update rtest_emp set salary =3D '7000.00' where ename =3D 'wieck'; delete from rtest_emp where ename =3D 'gates'; select ename, who =3D current_user as "matches user", action, newsal, olds= al from rtest_emplog order by ename, action, newsal; - ename | matches user | action | newsal | oldsal = =20 -----------------------+--------------+------------+------------+----------= -- - gates | t | fired | $0.00 | $80,000.00 - gates | t | hired | $80,000.00 | $0.00 - wiecc | t | hired | $5,000.00 | $0.00 - wieck | t | honored | $6,000.00 | $5,000.00 - wieck | t | honored | $7,000.00 | $6,000.00 + ename | matches user | action | newsal | oldsal =20 +----------------------+--------------+------------+----------+---------- + gates | t | fired | 0.00 | 80000.00 + gates | t | hired | 80000.00 | 0.00 + wiecc | t | hired | 5000.00 | 0.00 + wieck | t | honored | 6000.00 | 5000.00 + wieck | t | honored | 7000.00 | 6000.00 (5 rows) =20 insert into rtest_empmass values ('meyer', '4000.00'); @@ -424,54 +424,54 @@ insert into rtest_empmass values ('maier', '5000.00'); insert into rtest_empmass values ('mayr', '6000.00'); insert into rtest_emp select * from rtest_empmass; select ename, who =3D current_user as "matches user", action, newsal, olds= al from rtest_emplog order by ename, action, newsal; - ename | matches user | action | newsal | oldsal = =20 -----------------------+--------------+------------+------------+----------= -- - gates | t | fired | $0.00 | $80,000.00 - gates | t | hired | $80,000.00 | $0.00 - maier | t | hired | $5,000.00 | $0.00 - mayr | t | hired | $6,000.00 | $0.00 - meyer | t | hired | $4,000.00 | $0.00 - wiecc | t | hired | $5,000.00 | $0.00 - wieck | t | honored | $6,000.00 | $5,000.00 - wieck | t | honored | $7,000.00 | $6,000.00 + ename | matches user | action | newsal | oldsal =20 +----------------------+--------------+------------+----------+---------- + gates | t | fired | 0.00 | 80000.00 + gates | t | hired | 80000.00 | 0.00 + maier | t | hired | 5000.00 | 0.00 + mayr | t | hired | 6000.00 | 0.00 + meyer | t | hired | 4000.00 | 0.00 + wiecc | t | hired | 5000.00 | 0.00 + wieck | t | honored | 6000.00 | 5000.00 + wieck | t | honored | 7000.00 | 6000.00 (8 rows) =20 update rtest_empmass set salary =3D salary + '1000.00'; update rtest_emp set salary =3D rtest_empmass.salary from rtest_empmass wh= ere rtest_emp.ename =3D rtest_empmass.ename; select ename, who =3D current_user as "matches user", action, newsal, olds= al from rtest_emplog order by ename, action, newsal; - ename | matches user | action | newsal | oldsal = =20 -----------------------+--------------+------------+------------+----------= -- - gates | t | fired | $0.00 | $80,000.00 - gates | t | hired | $80,000.00 | $0.00 - maier | t | hired | $5,000.00 | $0.00 - maier | t | honored | $6,000.00 | $5,000.00 - mayr | t | hired | $6,000.00 | $0.00 - mayr | t | honored | $7,000.00 | $6,000.00 - meyer | t | hired | $4,000.00 | $0.00 - meyer | t | honored | $5,000.00 | $4,000.00 - wiecc | t | hired | $5,000.00 | $0.00 - wieck | t | honored | $6,000.00 | $5,000.00 - wieck | t | honored | $7,000.00 | $6,000.00 + ename | matches user | action | newsal | oldsal =20 +----------------------+--------------+------------+----------+---------- + gates | t | fired | 0.00 | 80000.00 + gates | t | hired | 80000.00 | 0.00 + maier | t | hired | 5000.00 | 0.00 + maier | t | honored | 6000.00 | 5000.00 + mayr | t | hired | 6000.00 | 0.00 + mayr | t | honored | 7000.00 | 6000.00 + meyer | t | hired | 4000.00 | 0.00 + meyer | t | honored | 5000.00 | 4000.00 + wiecc | t | hired | 5000.00 | 0.00 + wieck | t | honored | 6000.00 | 5000.00 + wieck | t | honored | 7000.00 | 6000.00 (11 rows) =20 delete from rtest_emp using rtest_empmass where rtest_emp.ename =3D rtest_= empmass.ename; select ename, who =3D current_user as "matches user", action, newsal, olds= al from rtest_emplog order by ename, action, newsal; - ename | matches user | action | newsal | oldsal = =20 -----------------------+--------------+------------+------------+----------= -- - gates | t | fired | $0.00 | $80,000.00 - gates | t | hired | $80,000.00 | $0.00 - maier | t | fired | $0.00 | $6,000.00 - maier | t | hired | $5,000.00 | $0.00 - maier | t | honored | $6,000.00 | $5,000.00 - mayr | t | fired | $0.00 | $7,000.00 - mayr | t | hired | $6,000.00 | $0.00 - mayr | t | honored | $7,000.00 | $6,000.00 - meyer | t | fired | $0.00 | $5,000.00 - meyer | t | hired | $4,000.00 | $0.00 - meyer | t | honored | $5,000.00 | $4,000.00 - wiecc | t | hired | $5,000.00 | $0.00 - wieck | t | honored | $6,000.00 | $5,000.00 - wieck | t | honored | $7,000.00 | $6,000.00 + ename | matches user | action | newsal | oldsal =20 +----------------------+--------------+------------+----------+---------- + gates | t | fired | 0.00 | 80000.00 + gates | t | hired | 80000.00 | 0.00 + maier | t | fired | 0.00 | 6000.00 + maier | t | hired | 5000.00 | 0.00 + maier | t | honored | 6000.00 | 5000.00 + mayr | t | fired | 0.00 | 7000.00 + mayr | t | hired | 6000.00 | 0.00 + mayr | t | honored | 7000.00 | 6000.00 + meyer | t | fired | 0.00 | 5000.00 + meyer | t | hired | 4000.00 | 0.00 + meyer | t | honored | 5000.00 | 4000.00 + wiecc | t | hired | 5000.00 | 0.00 + wieck | t | honored | 6000.00 | 5000.00 + wieck | t | honored | 7000.00 | 6000.00 (14 rows) =20 -- diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/exp= ected/stats_ext.out index a430153b22..10903bdab0 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -359,7 +359,7 @@ CREATE TABLE ndistinct ( WITH (autovacuum_enabled =3D off); -- over-estimates when using only per-column statistics INSERT INTO ndistinct (a, b, c, filler1) - SELECT i/100, i/100, i/100, cash_words((i/100)::money) + SELECT i/100, i/100, i/100, (i/100) || ' dollars and zero cents' FROM generate_series(1,1000) s(i); ANALYZE ndistinct; -- Group Aggregate, due to over-estimate of the number of groups @@ -499,7 +499,7 @@ TRUNCATE TABLE ndistinct; -- under-estimates when using only per-column statistics INSERT INTO ndistinct (a, b, c, filler1) SELECT mod(i,13), mod(i,17), mod(i,19), - cash_words(mod(i,23)::int::money) + mod(i,23) || ' dollars and zero cents' FROM generate_series(1,1000) s(i); ANALYZE ndistinct; SELECT s.stxkind, d.stxdndistinct diff --git a/src/test/regress/expected/union.out b/src/test/regress/expecte= d/union.out index 64cebe4833..73e320bad4 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -479,7 +479,7 @@ reset enable_hashagg; -- non-hashable type set enable_hashagg to on; explain (costs off) -select x from (values (100::money), (200::money)) _(x) union select x from= (values (100::money), (300::money)) _(x); +select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x = =66rom (values ('11'::varbit), ('10'::varbit)) _(x); QUERY PLAN =20 ----------------------------------------------- Unique @@ -492,7 +492,7 @@ select x from (values (100::money), (200::money)) _(x) = union select x from (valu =20 set enable_hashagg to off; explain (costs off) -select x from (values (100::money), (200::money)) _(x) union select x from= (values (100::money), (300::money)) _(x); +select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x = =66rom (values ('11'::varbit), ('10'::varbit)) _(x); QUERY PLAN =20 ----------------------------------------------- Unique @@ -563,7 +563,7 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x= ) except select x from (v =20 -- non-hashable type explain (costs off) -select x from (values (array[100::money]), (array[200::money])) _(x) union= select x from (values (array[100::money]), (array[300::money])) _(x); +select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) u= nion select x from (values (array['10'::varbit]), (array['01'::varbit])) _(= x); QUERY PLAN =20 ----------------------------------------------- Unique @@ -574,12 +574,12 @@ select x from (values (array[100::money]), (array[200= ::money])) _(x) union selec -> Values Scan on "*VALUES*_1" (6 rows) =20 -select x from (values (array[100::money]), (array[200::money])) _(x) union= select x from (values (array[100::money]), (array[300::money])) _(x); - x =20 ------------ - {$100.00} - {$200.00} - {$300.00} +select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) u= nion select x from (values (array['10'::varbit]), (array['01'::varbit])) _(= x); + x =20 +------ + {01} + {10} + {11} (3 rows) =20 set enable_hashagg to off; @@ -710,7 +710,7 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) ex= cept select x from (value -- With an anonymous row type, the typcache does not report that the -- type is hashable. (Otherwise, this would fail at execution time.) explain (costs off) -select x from (values (row(100::money)), (row(200::money))) _(x) union sel= ect x from (values (row(100::money)), (row(300::money))) _(x); +select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union= select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x); QUERY PLAN =20 ----------------------------------------------- Unique @@ -721,19 +721,19 @@ select x from (values (row(100::money)), (row(200::mo= ney))) _(x) union select x -> Values Scan on "*VALUES*_1" (6 rows) =20 -select x from (values (row(100::money)), (row(200::money))) _(x) union sel= ect x from (values (row(100::money)), (row(300::money))) _(x); - x =20 ------------ - ($100.00) - ($200.00) - ($300.00) +select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union= select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x); + x =20 +------ + (01) + (10) + (11) (3 rows) =20 -- With a defined row type, the typcache can inspect the type's fields -- for hashability. -create type ct1 as (f1 money); +create type ct1 as (f1 varbit); explain (costs off) -select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x)= union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)= ) _(x); +select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) = _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbi= t)::ct1)) _(x); QUERY PLAN =20 ----------------------------------------------- Unique @@ -744,12 +744,12 @@ select x from (values (row(100::money)::ct1), (row(20= 0::money)::ct1)) _(x) union -> Values Scan on "*VALUES*_1" (6 rows) =20 -select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x)= union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)= ) _(x); - x =20 ------------ - ($100.00) - ($200.00) - ($300.00) +select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) = _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbi= t)::ct1)) _(x); + x =20 +------ + (01) + (10) + (11) (3 rows) =20 drop type ct1; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected= /with.out index 3cf969d938..7d796ea69c 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -51,11 +51,11 @@ SELECT * FROM t; =20 -- UNION DISTINCT requires hashable type WITH RECURSIVE t(n) AS ( - VALUES (1::money) + VALUES ('01'::varbit) UNION - SELECT n+1::money FROM t WHERE n < 100::money + SELECT n || '10'::varbit FROM t WHERE n < '100'::varbit ) -SELECT sum(n) FROM t; +SELECT n FROM t; ERROR: could not implement recursive UNION DETAIL: All column datatypes must be hashable. -- recursive view diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/c= reate_table.sql index 1fd4cbfa7e..7a3761bb8d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -322,13 +322,14 @@ CREATE TABLE bools_true PARTITION OF bools FOR VALUES= IN (1); DROP TABLE bools; =20 -- specified literal can be cast, and the cast might not be immutable -CREATE TABLE moneyp ( - a money +CREATE TABLE castablep ( + a timestamptz ) PARTITION BY LIST (a); -CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10); -CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11'); -CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99'= )::int); -DROP TABLE moneyp; +CREATE TABLE castablep_1 PARTITION OF castablep + FOR VALUES IN ('2014-01-01 01:00:00'); +CREATE TABLE castablep_2 PARTITION OF castablep + FOR VALUES IN (to_date('2014', 'yyyy-mm-dd')::timestamptz); +DROP TABLE castablep; =20 -- cast is immutable CREATE TABLE bigintp ( diff --git a/src/test/regress/sql/hash_func.sql b/src/test/regress/sql/hash= _func.sql index 5ad33c1f9d..33756bd288 100644 --- a/src/test/regress/sql/hash_func.sql +++ b/src/test/regress/sql/hash_func.sql @@ -132,9 +132,9 @@ WHERE hash_array(v)::bit(32) !=3D hash_array_extended(= v, 0)::bit(32) =20 -- array hashing with non-hashable element type SELECT v as value, hash_array(v)::bit(32) as standard -FROM (VALUES ('{0}'::money[])) x(v); +FROM (VALUES ('{101}'::varbit[])) x(v); SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0 -FROM (VALUES ('{0}'::money[])) x(v); +FROM (VALUES ('{101}'::varbit[])) x(v); =20 SELECT v as value, hashbpchar(v)::bit(32) as standard, hashbpcharextended(v, 0)::bit(32) as extended0, @@ -247,11 +247,11 @@ WHERE hash_record(v)::bit(32) !=3D hash_record_exten= ded(v, 0)::bit(32) DROP TYPE hash_test_t1; =20 -- record hashing with non-hashable field type -CREATE TYPE hash_test_t2 AS (a money, b text); +CREATE TYPE hash_test_t2 AS (a varbit, b text); SELECT v as value, hash_record(v)::bit(32) as standard -FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v); +FROM (VALUES (row('10'::varbit, 'aaa')::hash_test_t2)) x(v); SELECT v as value, hash_record_extended(v, 0)::bit(32) as extended0 -FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v); +FROM (VALUES (row('11'::varbit, 'aaa')::hash_test_t2)) x(v); DROP TYPE hash_test_t2; =20 -- diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sq= l/multirangetypes.sql index fefb4b4d42..cadf312031 100644 --- a/src/test/regress/sql/multirangetypes.sql +++ b/src/test/regress/sql/multirangetypes.sql @@ -802,7 +802,7 @@ drop type two_ints cascade; =20 set enable_sort =3D off; -- try to make it pick a hash setop implementati= on =20 -select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange; +select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmult= irange; =20 reset enable_sort; =20 diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/ran= getypes.sql index c23be928c3..c5dbe0c04f 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -575,11 +575,11 @@ drop type two_ints cascade; -- Check behavior when subtype lacks a hash function -- =20 -create type cashrange as range (subtype =3D money); +create type varbitrange as range (subtype =3D varbit); =20 set enable_sort =3D off; -- try to make it pick a hash setop implementati= on =20 -select '(2,5)'::cashrange except select '(5,6)'::cashrange; +select '(01,10)'::varbitrange except select '(10,11)'::varbitrange; =20 reset enable_sort; =20 diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 8b7e255dcd..93aff4e66f 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -57,9 +57,9 @@ create rule rtest_pers_del as on delete to rtest_person d= o also -- -- Tables and rules for the logging test -- -create table rtest_emp (ename char(20), salary money); -create table rtest_emplog (ename char(20), who name, action char(10), news= al money, oldsal money); -create table rtest_empmass (ename char(20), salary money); +create table rtest_emp (ename char(20), salary numeric); +create table rtest_emplog (ename char(20), who name, action char(10), news= al numeric, oldsal numeric); +create table rtest_empmass (ename char(20), salary numeric); =20 create rule rtest_emp_ins as on insert to rtest_emp do insert into rtest_emplog values (new.ename, current_user, diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stat= s_ext.sql index 90b625a5a2..42cb7dd97d 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -234,7 +234,7 @@ WITH (autovacuum_enabled =3D off); =20 -- over-estimates when using only per-column statistics INSERT INTO ndistinct (a, b, c, filler1) - SELECT i/100, i/100, i/100, cash_words((i/100)::money) + SELECT i/100, i/100, i/100, (i/100) || ' dollars and zero cents' FROM generate_series(1,1000) s(i); =20 ANALYZE ndistinct; @@ -299,7 +299,7 @@ TRUNCATE TABLE ndistinct; -- under-estimates when using only per-column statistics INSERT INTO ndistinct (a, b, c, filler1) SELECT mod(i,13), mod(i,17), mod(i,19), - cash_words(mod(i,23)::int::money) + mod(i,23) || ' dollars and zero cents' FROM generate_series(1,1000) s(i); =20 ANALYZE ndistinct; diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 599013e7c9..6c509ac80c 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -162,12 +162,12 @@ reset enable_hashagg; set enable_hashagg to on; =20 explain (costs off) -select x from (values (100::money), (200::money)) _(x) union select x from= (values (100::money), (300::money)) _(x); +select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x = =66rom (values ('11'::varbit), ('10'::varbit)) _(x); =20 set enable_hashagg to off; =20 explain (costs off) -select x from (values (100::money), (200::money)) _(x) union select x from= (values (100::money), (300::money)) _(x); +select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x = =66rom (values ('11'::varbit), ('10'::varbit)) _(x); =20 reset enable_hashagg; =20 @@ -186,8 +186,8 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x= ) except select x from (v =20 -- non-hashable type explain (costs off) -select x from (values (array[100::money]), (array[200::money])) _(x) union= select x from (values (array[100::money]), (array[300::money])) _(x); -select x from (values (array[100::money]), (array[200::money])) _(x) union= select x from (values (array[100::money]), (array[300::money])) _(x); +select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) u= nion select x from (values (array['10'::varbit]), (array['01'::varbit])) _(= x); +select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) u= nion select x from (values (array['10'::varbit]), (array['01'::varbit])) _(= x); =20 set enable_hashagg to off; =20 @@ -221,15 +221,15 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) = except select x from (value -- With an anonymous row type, the typcache does not report that the -- type is hashable. (Otherwise, this would fail at execution time.) explain (costs off) -select x from (values (row(100::money)), (row(200::money))) _(x) union sel= ect x from (values (row(100::money)), (row(300::money))) _(x); -select x from (values (row(100::money)), (row(200::money))) _(x) union sel= ect x from (values (row(100::money)), (row(300::money))) _(x); +select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union= select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x); +select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union= select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x); =20 -- With a defined row type, the typcache can inspect the type's fields -- for hashability. -create type ct1 as (f1 money); +create type ct1 as (f1 varbit); explain (costs off) -select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x)= union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)= ) _(x); -select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x)= union select x from (values (row(100::money)::ct1), (row(300::money)::ct1)= ) _(x); +select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) = _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbi= t)::ct1)) _(x); +select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) = _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbi= t)::ct1)) _(x); drop type ct1; =20 set enable_hashagg to off; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index ff68e21e2e..f8a213e357 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -33,11 +33,11 @@ SELECT * FROM t; =20 -- UNION DISTINCT requires hashable type WITH RECURSIVE t(n) AS ( - VALUES (1::money) + VALUES ('01'::varbit) UNION - SELECT n+1::money FROM t WHERE n < 100::money + SELECT n || '10'::varbit FROM t WHERE n < '100'::varbit ) -SELECT sum(n) FROM t; +SELECT n FROM t; =20 -- recursive view CREATE RECURSIVE VIEW nums (n) AS --=20 2.43.0 --0mxAdYGhgnx10yju-- --QM5bDpVfnuPKU592 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmWfQHcACgkQnvQgOdby QH0jEQ//b7YGO5Jx6kfx3Bd9Lxr/45YlrF/BVA8G0kUZ7ug6dI/N+Z6Fpe5mLqkW QbTYkLH9cX8IOp9VgCZjzJ4Xhkmd5J9i7bGAs5SUYsvbpjsvf+q2VjQzE6rxbVkt EdR0EAPuHBgS6ovfFhhQgfpUBknQgD+d6n6bCw7LLe31xatYRP0y8BMtvguVkHcg P7jpw95m93QiLYDWE/ZRBD5zNOdlF6m/gq37CqG4YTvMwp0Otw9iuNrn8QUSUrx4 5x9H3xzbzSPKPBaF+AS1Xv0rwV5bKccu+2Mgpy4FuMMZMK09kQyT5Hmlq8NYOQeg k3yU+cEnAnQrzMoQ6nLktWdnIdJaEraKe2fvs/qQYWfSXY4ow2vhGUG6cMk8krJ1 W9ZUtAX3Q370QoD9SOBND+joc3gaNNvzeLxkXLZw50ceBkOtEMEv72NMyuUjr4zs MZY2MvS9PBRq9k5uzzj73NCWwc7kCU626Wk89EwF4B9nZQyv6eV6VhFrOhtRvfDR PIjcm8jIWlRKOb92jdqsssqAk27Yba/T4EsAbOFZLkF/+XBBCQYU18Bs+SLNYAN0 k9aBttgWge3muJFXauIr6PyhCAZZXfmrntMIcPtoZLANORnAg14JVMGACd/TGeva IzWgEkO8HUsBJlF/eL2ygl0yt0hmTIWEHdPXSi70+Wrn40SPJFc= =K/08 -----END PGP SIGNATURE----- --QM5bDpVfnuPKU592--