Skip to content

Commit 31acee4

Browse files
michaelpqmichaelpq
committed
Reduce dependency to money data type in main regression test suite
Most of these tests have been introduced in 6dd8b00, to check for behaviors related to hashing and hash plans, and money is a data type with btree support but no hash functions. These tests are switched to use varbit instead, to provide the same coverage. Some other tests historically used money but don't really need it for what they wanted to test (see rules.sql). Plans and coverage are unchanged after the modifications done here. Support for money may be removed a a later point, but this needs more discussion. Discussion: https://postgr.es/m/18240-c5da758d7dc1ecf0@postgresql.org
1 parent 6545ba9 commit 31acee4

14 files changed

+121
-121
lines changed

‎src/test/regress/expected/hash_func.out

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -179,11 +179,11 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
179179

180180
-- array hashing with non-hashable element type
181181
SELECT v as value, hash_array(v)::bit(32) as standard
182-
FROM (VALUES ('{0}'::money[])) x(v);
183-
ERROR: could not identify a hash function for type money
182+
FROM (VALUES ('{101}'::varbit[])) x(v);
183+
ERROR: could not identify a hash function for type bit varying
184184
SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0
185-
FROM (VALUES ('{0}'::money[])) x(v);
186-
ERROR: could not identify an extended hash function for type money
185+
FROM (VALUES ('{101}'::varbit[])) x(v);
186+
ERROR: could not identify an extended hash function for type bit varying
187187
SELECT v as value, hashbpchar(v)::bit(32) as standard,
188188
hashbpcharextended(v, 0)::bit(32) as extended0,
189189
hashbpcharextended(v, 1)::bit(32) as extended1
@@ -331,13 +331,13 @@ WHERE hash_record(v)::bit(32) != hash_record_extended(v, 0)::bit(32)
331331

332332
DROP TYPE hash_test_t1;
333333
-- record hashing with non-hashable field type
334-
CREATE TYPE hash_test_t2 AS (a money, b text);
334+
CREATE TYPE hash_test_t2 AS (a varbit, b text);
335335
SELECT v as value, hash_record(v)::bit(32) as standard
336-
FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v);
337-
ERROR: could not identify a hash function for type money
336+
FROM (VALUES (row('10'::varbit, 'aaa')::hash_test_t2)) x(v);
337+
ERROR: could not identify a hash function for type bit varying
338338
SELECT v as value, hash_record_extended(v, 0)::bit(32) as extended0
339-
FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v);
340-
ERROR: could not identify an extended hash function for type money
339+
FROM (VALUES (row('11'::varbit, 'aaa')::hash_test_t2)) x(v);
340+
ERROR: could not identify an extended hash function for type bit varying
341341
DROP TYPE hash_test_t2;
342342
--
343343
-- Check special cases for specific data types

‎src/test/regress/expected/multirangetypes.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3273,10 +3273,10 @@ NOTICE: drop cascades to type two_ints_range
32733273
-- Check behavior when subtype lacks a hash function
32743274
--
32753275
set enable_sort = off; -- try to make it pick a hash setop implementation
3276-
select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange;
3277-
cashmultirange
3278-
-----------------
3279-
{($2.00,$5.00)}
3276+
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
3277+
varbitmultirange
3278+
------------------
3279+
{(01,10)}
32803280
(1 row)
32813281

32823282
reset enable_sort;

‎src/test/regress/expected/rangetypes.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1761,12 +1761,12 @@ NOTICE: drop cascades to type two_ints_range
17611761
--
17621762
-- Check behavior when subtype lacks a hash function
17631763
--
1764-
create type cashrange as range (subtype = money);
1764+
create type varbitrange as range (subtype = varbit);
17651765
set enable_sort = off; -- try to make it pick a hash setop implementation
1766-
select '(2,5)'::cashrange except select '(5,6)'::cashrange;
1767-
cashrange
1768-
---------------
1769-
($2.00,$5.00)
1766+
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
1767+
varbitrange
1768+
-------------
1769+
(01,10)
17701770
(1 row)
17711771

17721772
reset enable_sort;

‎src/test/regress/expected/rules.out

Lines changed: 49 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -51,9 +51,9 @@ create rule rtest_pers_del as on delete to rtest_person do also
5151
--
5252
-- Tables and rules for the logging test
5353
--
54-
create table rtest_emp (ename char(20), salary money);
55-
create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
56-
create table rtest_empmass (ename char(20), salary money);
54+
create table rtest_emp (ename char(20), salary numeric);
55+
create table rtest_emplog (ename char(20), who name, action char(10), newsal numeric, oldsal numeric);
56+
create table rtest_empmass (ename char(20), salary numeric);
5757
create rule rtest_emp_ins as on insert to rtest_emp do
5858
insert into rtest_emplog values (new.ename, current_user,
5959
'hired', new.salary, '0.00');
@@ -410,68 +410,68 @@ update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx';
410410
update rtest_emp set salary = '7000.00' where ename = 'wieck';
411411
delete from rtest_emp where ename = 'gates';
412412
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
413-
ename | matches user | action | newsal | oldsal
414-
----------------------+--------------+------------+------------+------------
415-
gates | t | fired | $0.00 | $80,000.00
416-
gates | t | hired | $80,000.00 | $0.00
417-
wiecc | t | hired | $5,000.00 | $0.00
418-
wieck | t | honored | $6,000.00 | $5,000.00
419-
wieck | t | honored | $7,000.00 | $6,000.00
413+
ename | matches user | action | newsal | oldsal
414+
----------------------+--------------+------------+----------+----------
415+
gates | t | fired | 0.00 | 80000.00
416+
gates | t | hired | 80000.00 | 0.00
417+
wiecc | t | hired | 5000.00 | 0.00
418+
wieck | t | honored | 6000.00 | 5000.00
419+
wieck | t | honored | 7000.00 | 6000.00
420420
(5 rows)
421421

422422
insert into rtest_empmass values ('meyer', '4000.00');
423423
insert into rtest_empmass values ('maier', '5000.00');
424424
insert into rtest_empmass values ('mayr', '6000.00');
425425
insert into rtest_emp select * from rtest_empmass;
426426
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
427-
ename | matches user | action | newsal | oldsal
428-
----------------------+--------------+------------+------------+------------
429-
gates | t | fired | $0.00 | $80,000.00
430-
gates | t | hired | $80,000.00 | $0.00
431-
maier | t | hired | $5,000.00 | $0.00
432-
mayr | t | hired | $6,000.00 | $0.00
433-
meyer | t | hired | $4,000.00 | $0.00
434-
wiecc | t | hired | $5,000.00 | $0.00
435-
wieck | t | honored | $6,000.00 | $5,000.00
436-
wieck | t | honored | $7,000.00 | $6,000.00
427+
ename | matches user | action | newsal | oldsal
428+
----------------------+--------------+------------+----------+----------
429+
gates | t | fired | 0.00 | 80000.00
430+
gates | t | hired | 80000.00 | 0.00
431+
maier | t | hired | 5000.00 | 0.00
432+
mayr | t | hired | 6000.00 | 0.00
433+
meyer | t | hired | 4000.00 | 0.00
434+
wiecc | t | hired | 5000.00 | 0.00
435+
wieck | t | honored | 6000.00 | 5000.00
436+
wieck | t | honored | 7000.00 | 6000.00
437437
(8 rows)
438438

439439
update rtest_empmass set salary = salary + '1000.00';
440440
update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
441441
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
442-
ename | matches user | action | newsal | oldsal
443-
----------------------+--------------+------------+------------+------------
444-
gates | t | fired | $0.00 | $80,000.00
445-
gates | t | hired | $80,000.00 | $0.00
446-
maier | t | hired | $5,000.00 | $0.00
447-
maier | t | honored | $6,000.00 | $5,000.00
448-
mayr | t | hired | $6,000.00 | $0.00
449-
mayr | t | honored | $7,000.00 | $6,000.00
450-
meyer | t | hired | $4,000.00 | $0.00
451-
meyer | t | honored | $5,000.00 | $4,000.00
452-
wiecc | t | hired | $5,000.00 | $0.00
453-
wieck | t | honored | $6,000.00 | $5,000.00
454-
wieck | t | honored | $7,000.00 | $6,000.00
442+
ename | matches user | action | newsal | oldsal
443+
----------------------+--------------+------------+----------+----------
444+
gates | t | fired | 0.00 | 80000.00
445+
gates | t | hired | 80000.00 | 0.00
446+
maier | t | hired | 5000.00 | 0.00
447+
maier | t | honored | 6000.00 | 5000.00
448+
mayr | t | hired | 6000.00 | 0.00
449+
mayr | t | honored | 7000.00 | 6000.00
450+
meyer | t | hired | 4000.00 | 0.00
451+
meyer | t | honored | 5000.00 | 4000.00
452+
wiecc | t | hired | 5000.00 | 0.00
453+
wieck | t | honored | 6000.00 | 5000.00
454+
wieck | t | honored | 7000.00 | 6000.00
455455
(11 rows)
456456

457457
delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
458458
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
459-
ename | matches user | action | newsal | oldsal
460-
----------------------+--------------+------------+------------+------------
461-
gates | t | fired | $0.00 | $80,000.00
462-
gates | t | hired | $80,000.00 | $0.00
463-
maier | t | fired | $0.00 | $6,000.00
464-
maier | t | hired | $5,000.00 | $0.00
465-
maier | t | honored | $6,000.00 | $5,000.00
466-
mayr | t | fired | $0.00 | $7,000.00
467-
mayr | t | hired | $6,000.00 | $0.00
468-
mayr | t | honored | $7,000.00 | $6,000.00
469-
meyer | t | fired | $0.00 | $5,000.00
470-
meyer | t | hired | $4,000.00 | $0.00
471-
meyer | t | honored | $5,000.00 | $4,000.00
472-
wiecc | t | hired | $5,000.00 | $0.00
473-
wieck | t | honored | $6,000.00 | $5,000.00
474-
wieck | t | honored | $7,000.00 | $6,000.00
459+
ename | matches user | action | newsal | oldsal
460+
----------------------+--------------+------------+----------+----------
461+
gates | t | fired | 0.00 | 80000.00
462+
gates | t | hired | 80000.00 | 0.00
463+
maier | t | fired | 0.00 | 6000.00
464+
maier | t | hired | 5000.00 | 0.00
465+
maier | t | honored | 6000.00 | 5000.00
466+
mayr | t | fired | 0.00 | 7000.00
467+
mayr | t | hired | 6000.00 | 0.00
468+
mayr | t | honored | 7000.00 | 6000.00
469+
meyer | t | fired | 0.00 | 5000.00
470+
meyer | t | hired | 4000.00 | 0.00
471+
meyer | t | honored | 5000.00 | 4000.00
472+
wiecc | t | hired | 5000.00 | 0.00
473+
wieck | t | honored | 6000.00 | 5000.00
474+
wieck | t | honored | 7000.00 | 6000.00
475475
(14 rows)
476476

477477
--

‎src/test/regress/expected/stats_ext.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -359,7 +359,7 @@ CREATE TABLE ndistinct (
359359
WITH (autovacuum_enabled = off);
360360
-- over-estimates when using only per-column statistics
361361
INSERT INTO ndistinct (a, b, c, filler1)
362-
SELECT i/100, i/100, i/100, cash_words((i/100)::money)
362+
SELECT i/100, i/100, i/100, (i/100) || ' dollars and zero cents'
363363
FROM generate_series(1,1000) s(i);
364364
ANALYZE ndistinct;
365365
-- Group Aggregate, due to over-estimate of the number of groups
@@ -499,7 +499,7 @@ TRUNCATE TABLE ndistinct;
499499
-- under-estimates when using only per-column statistics
500500
INSERT INTO ndistinct (a, b, c, filler1)
501501
SELECT mod(i,13), mod(i,17), mod(i,19),
502-
cash_words(mod(i,23)::int::money)
502+
mod(i,23) || ' dollars and zero cents'
503503
FROM generate_series(1,1000) s(i);
504504
ANALYZE ndistinct;
505505
SELECT s.stxkind, d.stxdndistinct

‎src/test/regress/expected/union.out

Lines changed: 24 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -479,7 +479,7 @@ reset enable_hashagg;
479479
-- non-hashable type
480480
set enable_hashagg to on;
481481
explain (costs off)
482-
select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
482+
select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
483483
QUERY PLAN
484484
-----------------------------------------------
485485
Unique
@@ -492,7 +492,7 @@ select x from (values (100::money), (200::money)) _(x) union select x from (valu
492492

493493
set enable_hashagg to off;
494494
explain (costs off)
495-
select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
495+
select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
496496
QUERY PLAN
497497
-----------------------------------------------
498498
Unique
@@ -563,7 +563,7 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (v
563563

564564
-- non-hashable type
565565
explain (costs off)
566-
select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
566+
select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
567567
QUERY PLAN
568568
-----------------------------------------------
569569
Unique
@@ -574,12 +574,12 @@ select x from (values (array[100::money]), (array[200::money])) _(x) union selec
574574
-> Values Scan on "*VALUES*_1"
575575
(6 rows)
576576

577-
select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
578-
x
579-
-----------
580-
{$100.00}
581-
{$200.00}
582-
{$300.00}
577+
select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
578+
x
579+
------
580+
{01}
581+
{10}
582+
{11}
583583
(3 rows)
584584

585585
set enable_hashagg to off;
@@ -710,7 +710,7 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value
710710
-- With an anonymous row type, the typcache does not report that the
711711
-- type is hashable. (Otherwise, this would fail at execution time.)
712712
explain (costs off)
713-
select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
713+
select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
714714
QUERY PLAN
715715
-----------------------------------------------
716716
Unique
@@ -721,19 +721,19 @@ select x from (values (row(100::money)), (row(200::money))) _(x) union select x
721721
-> Values Scan on "*VALUES*_1"
722722
(6 rows)
723723

724-
select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
725-
x
726-
-----------
727-
($100.00)
728-
($200.00)
729-
($300.00)
724+
select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
725+
x
726+
------
727+
(01)
728+
(10)
729+
(11)
730730
(3 rows)
731731

732732
-- With a defined row type, the typcache can inspect the type's fields
733733
-- for hashability.
734-
create type ct1 as (f1 money);
734+
create type ct1 as (f1 varbit);
735735
explain (costs off)
736-
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);
736+
select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
737737
QUERY PLAN
738738
-----------------------------------------------
739739
Unique
@@ -744,12 +744,12 @@ select x from (values (row(100::money)::ct1), (row(200::money)::ct1)) _(x) union
744744
-> Values Scan on "*VALUES*_1"
745745
(6 rows)
746746

747-
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);
748-
x
749-
-----------
750-
($100.00)
751-
($200.00)
752-
($300.00)
747+
select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
748+
x
749+
------
750+
(01)
751+
(10)
752+
(11)
753753
(3 rows)
754754

755755
drop type ct1;

‎src/test/regress/expected/with.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -51,11 +51,11 @@ SELECT * FROM t;
5151

5252
-- UNION DISTINCT requires hashable type
5353
WITH RECURSIVE t(n) AS (
54-
VALUES (1::money)
54+
VALUES ('01'::varbit)
5555
UNION
56-
SELECT n+1::money FROM t WHERE n < 100::money
56+
SELECT n || '10'::varbit FROM t WHERE n < '100'::varbit
5757
)
58-
SELECT sum(n) FROM t;
58+
SELECT n FROM t;
5959
ERROR: could not implement recursive UNION
6060
DETAIL: All column datatypes must be hashable.
6161
-- recursive view

‎src/test/regress/sql/hash_func.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -132,9 +132,9 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
132132

133133
-- array hashing with non-hashable element type
134134
SELECT v as value, hash_array(v)::bit(32) as standard
135-
FROM (VALUES ('{0}'::money[])) x(v);
135+
FROM (VALUES ('{101}'::varbit[])) x(v);
136136
SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0
137-
FROM (VALUES ('{0}'::money[])) x(v);
137+
FROM (VALUES ('{101}'::varbit[])) x(v);
138138

139139
SELECT v as value, hashbpchar(v)::bit(32) as standard,
140140
hashbpcharextended(v, 0)::bit(32) as extended0,
@@ -247,11 +247,11 @@ WHERE hash_record(v)::bit(32) != hash_record_extended(v, 0)::bit(32)
247247
DROP TYPE hash_test_t1;
248248

249249
-- record hashing with non-hashable field type
250-
CREATE TYPE hash_test_t2 AS (a money, b text);
250+
CREATE TYPE hash_test_t2 AS (a varbit, b text);
251251
SELECT v as value, hash_record(v)::bit(32) as standard
252-
FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v);
252+
FROM (VALUES (row('10'::varbit, 'aaa')::hash_test_t2)) x(v);
253253
SELECT v as value, hash_record_extended(v, 0)::bit(32) as extended0
254-
FROM (VALUES (row(1, 'aaa')::hash_test_t2)) x(v);
254+
FROM (VALUES (row('11'::varbit, 'aaa')::hash_test_t2)) x(v);
255255
DROP TYPE hash_test_t2;
256256

257257
--

‎src/test/regress/sql/multirangetypes.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -802,7 +802,7 @@ drop type two_ints cascade;
802802

803803
set enable_sort = off; -- try to make it pick a hash setop implementation
804804

805-
select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange;
805+
select '{(01,10)}'::varbitmultirange except select '{(10,11)}'::varbitmultirange;
806806

807807
reset enable_sort;
808808

‎src/test/regress/sql/rangetypes.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -575,11 +575,11 @@ drop type two_ints cascade;
575575
-- Check behavior when subtype lacks a hash function
576576
--
577577

578-
create type cashrange as range (subtype = money);
578+
create type varbitrange as range (subtype = varbit);
579579

580580
set enable_sort = off; -- try to make it pick a hash setop implementation
581581

582-
select '(2,5)'::cashrange except select '(5,6)'::cashrange;
582+
select '(01,10)'::varbitrange except select '(10,11)'::varbitrange;
583583

584584
reset enable_sort;
585585

‎src/test/regress/sql/rules.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -57,9 +57,9 @@ create rule rtest_pers_del as on delete to rtest_person do also
5757
--
5858
-- Tables and rules for the logging test
5959
--
60-
create table rtest_emp (ename char(20), salary money);
61-
create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
62-
create table rtest_empmass (ename char(20), salary money);
60+
create table rtest_emp (ename char(20), salary numeric);
61+
create table rtest_emplog (ename char(20), who name, action char(10), newsal numeric, oldsal numeric);
62+
create table rtest_empmass (ename char(20), salary numeric);
6363

6464
create rule rtest_emp_ins as on insert to rtest_emp do
6565
insert into rtest_emplog values (new.ename, current_user,

0 commit comments

Comments
 (0)