Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 74b35eb

Browse filesBrowse files
tglsfdctglsfdc
committed
Fix CheckAttributeType's handling of collations for ranges.
Commit fc76958 changed CheckAttributeType to recurse into ranges, but made it pass down the wrong collation (always InvalidOid, since ranges as such have no collation). This would result in guaranteed failure when considering a range type whose subtype is collatable. Embarrassingly, we lack any regression tests that would expose such a problem (but fortunately, somebody noticed before we shipped this bug in any release). Fix it to pass down the range's subtype collation property instead, and add some regression test cases to exercise collatable-subtype ranges a bit more. Back-patch to all supported branches, as the previous patch was. Report and patch by Julien Rouhaud, test cases tweaked by me Discussion: https://postgr.es/m/CAOBaU_aBWqNweiGUFX0guzBKkcfJ8mnnyyGC_KBQmO12Mj5f_A@mail.gmail.com
1 parent 2425f8f commit 74b35eb
Copy full SHA for 74b35eb

File tree

Expand file treeCollapse file tree

6 files changed

+148
-3
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+148
-3
lines changed

‎src/backend/catalog/heap.c

Copy file name to clipboardExpand all lines: src/backend/catalog/heap.c
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -672,7 +672,8 @@ CheckAttributeType(const char *attname,
672672
/*
673673
* If it's a range, recurse to check its subtype.
674674
*/
675-
CheckAttributeType(attname, get_range_subtype(atttypid), attcollation,
675+
CheckAttributeType(attname, get_range_subtype(atttypid),
676+
get_range_collation(atttypid),
676677
containing_rowtypes,
677678
flags);
678679
}

‎src/backend/utils/cache/lsyscache.c

Copy file name to clipboardExpand all lines: src/backend/utils/cache/lsyscache.c
+26Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3150,6 +3150,32 @@ get_range_subtype(Oid rangeOid)
31503150
return InvalidOid;
31513151
}
31523152

3153+
/*
3154+
* get_range_collation
3155+
* Returns the collation of a given range type
3156+
*
3157+
* Returns InvalidOid if the type is not a range type,
3158+
* or if its subtype is not collatable.
3159+
*/
3160+
Oid
3161+
get_range_collation(Oid rangeOid)
3162+
{
3163+
HeapTuple tp;
3164+
3165+
tp = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(rangeOid));
3166+
if (HeapTupleIsValid(tp))
3167+
{
3168+
Form_pg_range rngtup = (Form_pg_range) GETSTRUCT(tp);
3169+
Oid result;
3170+
3171+
result = rngtup->rngcollation;
3172+
ReleaseSysCache(tp);
3173+
return result;
3174+
}
3175+
else
3176+
return InvalidOid;
3177+
}
3178+
31533179
/* ---------- PG_INDEX CACHE ---------- */
31543180

31553181
/*

‎src/include/utils/lsyscache.h

Copy file name to clipboardExpand all lines: src/include/utils/lsyscache.h
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,7 @@ extern void free_attstatsslot(AttStatsSlot *sslot);
179179
extern char *get_namespace_name(Oid nspid);
180180
extern char *get_namespace_name_or_temp(Oid nspid);
181181
extern Oid get_range_subtype(Oid rangeOid);
182+
extern Oid get_range_collation(Oid rangeOid);
182183
extern Oid get_index_column_opclass(Oid index_oid, int attno);
183184

184185
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/rangetypes.out
+88-1Lines changed: 88 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -582,8 +582,95 @@ select * from numrange_test natural join numrange_test2 order by nr;
582582
set enable_nestloop to default;
583583
set enable_hashjoin to default;
584584
set enable_mergejoin to default;
585-
DROP TABLE numrange_test;
585+
-- keep numrange_test around to help exercise dump/reload
586586
DROP TABLE numrange_test2;
587+
--
588+
-- Apply a subset of the above tests on a collatable type, too
589+
--
590+
CREATE TABLE textrange_test (tr textrange);
591+
create index textrange_test_btree on textrange_test(tr);
592+
INSERT INTO textrange_test VALUES('[,)');
593+
INSERT INTO textrange_test VALUES('["a",]');
594+
INSERT INTO textrange_test VALUES('[,"q")');
595+
INSERT INTO textrange_test VALUES(textrange('b', 'g'));
596+
INSERT INTO textrange_test VALUES('empty');
597+
INSERT INTO textrange_test VALUES(textrange('d', 'd', '[]'));
598+
SELECT tr, isempty(tr), lower(tr), upper(tr) FROM textrange_test;
599+
tr | isempty | lower | upper
600+
-------+---------+-------+-------
601+
(,) | f | |
602+
[a,) | f | a |
603+
(,q) | f | | q
604+
[b,g) | f | b | g
605+
empty | t | |
606+
[d,d] | f | d | d
607+
(6 rows)
608+
609+
SELECT tr, lower_inc(tr), lower_inf(tr), upper_inc(tr), upper_inf(tr) FROM textrange_test;
610+
tr | lower_inc | lower_inf | upper_inc | upper_inf
611+
-------+-----------+-----------+-----------+-----------
612+
(,) | f | t | f | t
613+
[a,) | t | f | f | t
614+
(,q) | f | t | f | f
615+
[b,g) | t | f | f | f
616+
empty | f | f | f | f
617+
[d,d] | t | f | t | f
618+
(6 rows)
619+
620+
SELECT * FROM textrange_test WHERE range_contains(tr, textrange('f', 'fx'));
621+
tr
622+
-------
623+
(,)
624+
[a,)
625+
(,q)
626+
[b,g)
627+
(4 rows)
628+
629+
SELECT * FROM textrange_test WHERE tr @> textrange('a', 'z');
630+
tr
631+
------
632+
(,)
633+
[a,)
634+
(2 rows)
635+
636+
SELECT * FROM textrange_test WHERE range_contained_by(textrange('0','9'), tr);
637+
tr
638+
------
639+
(,)
640+
(,q)
641+
(2 rows)
642+
643+
SELECT * FROM textrange_test WHERE 'e'::text <@ tr;
644+
tr
645+
-------
646+
(,)
647+
[a,)
648+
(,q)
649+
[b,g)
650+
(4 rows)
651+
652+
select * from textrange_test where tr = 'empty';
653+
tr
654+
-------
655+
empty
656+
(1 row)
657+
658+
select * from textrange_test where tr = '("b","g")';
659+
tr
660+
----
661+
(0 rows)
662+
663+
select * from textrange_test where tr = '["b","g")';
664+
tr
665+
-------
666+
[b,g)
667+
(1 row)
668+
669+
select * from textrange_test where tr < 'empty';
670+
tr
671+
----
672+
(0 rows)
673+
587674
-- test canonical form for int4range
588675
select int4range(1, 10, '[]');
589676
int4range

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/sanity_check.out
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,7 @@ num_exp_sqrt|t
9292
num_exp_sub|t
9393
num_input_test|f
9494
num_result|f
95+
numrange_test|t
9596
onek|t
9697
onek2|t
9798
path_tbl|f
@@ -201,6 +202,7 @@ test_range_spgist|t
201202
test_tsvector|f
202203
testjsonb|f
203204
text_tbl|f
205+
textrange_test|t
204206
time_tbl|f
205207
timestamp_tbl|f
206208
timestamptz_tbl|f

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

Copy file name to clipboardExpand all lines: src/test/regress/sql/rangetypes.sql
+29-1Lines changed: 29 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -148,9 +148,37 @@ set enable_nestloop to default;
148148
set enable_hashjoin to default;
149149
set enable_mergejoin to default;
150150

151-
DROP TABLE numrange_test;
151+
-- keep numrange_test around to help exercise dump/reload
152152
DROP TABLE numrange_test2;
153153

154+
--
155+
-- Apply a subset of the above tests on a collatable type, too
156+
--
157+
158+
CREATE TABLE textrange_test (tr textrange);
159+
create index textrange_test_btree on textrange_test(tr);
160+
161+
INSERT INTO textrange_test VALUES('[,)');
162+
INSERT INTO textrange_test VALUES('["a",]');
163+
INSERT INTO textrange_test VALUES('[,"q")');
164+
INSERT INTO textrange_test VALUES(textrange('b', 'g'));
165+
INSERT INTO textrange_test VALUES('empty');
166+
INSERT INTO textrange_test VALUES(textrange('d', 'd', '[]'));
167+
168+
SELECT tr, isempty(tr), lower(tr), upper(tr) FROM textrange_test;
169+
SELECT tr, lower_inc(tr), lower_inf(tr), upper_inc(tr), upper_inf(tr) FROM textrange_test;
170+
171+
SELECT * FROM textrange_test WHERE range_contains(tr, textrange('f', 'fx'));
172+
SELECT * FROM textrange_test WHERE tr @> textrange('a', 'z');
173+
SELECT * FROM textrange_test WHERE range_contained_by(textrange('0','9'), tr);
174+
SELECT * FROM textrange_test WHERE 'e'::text <@ tr;
175+
176+
select * from textrange_test where tr = 'empty';
177+
select * from textrange_test where tr = '("b","g")';
178+
select * from textrange_test where tr = '["b","g")';
179+
select * from textrange_test where tr < 'empty';
180+
181+
154182
-- test canonical form for int4range
155183
select int4range(1, 10, '[]');
156184
select int4range(1, 10, '[)');

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.