From eb42f59a3d6059b95e13d7d76483c95f36ba2049 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 1 Feb 2025 18:49:05 -0500 Subject: [PATCH v1 4/5] Add cross-type comparisons for string types. (Only these two cases appear in the catalogs.) --- contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++++++++ contrib/btree_gin/btree_gin.c | 49 ++++++++++++++++--- contrib/btree_gin/expected/name.out | 59 +++++++++++++++++++++++ contrib/btree_gin/expected/text.out | 50 +++++++++++++++++++ contrib/btree_gin/sql/name.sql | 11 +++++ contrib/btree_gin/sql/text.sql | 9 ++++ 6 files changed, 192 insertions(+), 6 deletions(-) diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql index 88ed5d5b3e..13c84ad667 100644 --- a/contrib/btree_gin/btree_gin--1.3--1.4.sql +++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql @@ -81,3 +81,23 @@ ADD OPERATOR 12 >= (float8, float4), OPERATOR 13 > (float8, float4) ; + +ALTER OPERATOR FAMILY text_ops USING gin +ADD + -- Code 1: RHS is name + OPERATOR 9 < (text, name), + OPERATOR 10 <= (text, name), + OPERATOR 11 = (text, name), + OPERATOR 12 >= (text, name), + OPERATOR 13 > (text, name) +; + +ALTER OPERATOR FAMILY name_ops USING gin +ADD + -- Code 1: RHS is text + OPERATOR 9 < (name, text), + OPERATOR 10 <= (name, text), + OPERATOR 11 = (name, text), + OPERATOR 12 >= (name, text), + OPERATOR 13 > (name, text) +; diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c index 95c193a4ca..1d4469bef0 100644 --- a/contrib/btree_gin/btree_gin.c +++ b/contrib/btree_gin/btree_gin.c @@ -6,6 +6,7 @@ #include #include "access/stratnum.h" +#include "mb/pg_wchar.h" #include "utils/builtins.h" #include "utils/date.h" #include "utils/float.h" @@ -13,6 +14,7 @@ #include "utils/numeric.h" #include "utils/timestamp.h" #include "utils/uuid.h" +#include "varatt.h" PG_MODULE_MAGIC; @@ -621,13 +623,24 @@ leftmostvalue_text(void) return PointerGetDatum(cstring_to_text_with_len("", 0)); } +static Datum +cvt_name_text(Datum input) +{ + Name val = DatumGetName(input); + + return PointerGetDatum(cstring_to_text(NameStr(*val))); +} + static const bool text_rhs_is_varlena[] = -{true}; +{true, false}; + +static const btree_gin_convert_function text_cvt_fns[] = +{NULL, cvt_name_text}; static const PGFunction text_cmp_fns[] = -{bttextcmp}; +{bttextcmp, btnametextcmp}; -GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns) +GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, text_cvt_fns, text_cmp_fns) static const bool bpchar_rhs_is_varlena[] = {true}; @@ -827,13 +840,37 @@ leftmostvalue_name(void) return NameGetDatum(result); } +static Datum +cvt_text_name(Datum input) +{ + text *val = DatumGetTextPP(input); + NameData *result = (NameData *) palloc0(NAMEDATALEN); + int len = VARSIZE_ANY_EXHDR(val); + + /* + * Truncate oversize input. We're assuming this will produce a result + * considered less than the original. That could be a bad assumption in + * some collations, but fortunately an index on "name" is generally going + * to use C collation. + */ + if (len >= NAMEDATALEN) + len = pg_mbcliplen(VARDATA_ANY(val), len, NAMEDATALEN - 1); + + memcpy(NameStr(*result), VARDATA_ANY(val), len); + + return NameGetDatum(result); +} + static const bool name_rhs_is_varlena[] = -{false}; +{false, true}; + +static const btree_gin_convert_function name_cvt_fns[] = +{NULL, cvt_text_name}; static const PGFunction name_cmp_fns[] = -{btnamecmp}; +{btnamecmp, bttextnamecmp}; -GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns) +GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, name_cvt_fns, name_cmp_fns) static Datum leftmostvalue_bool(void) diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out index 174de6576f..3a30f62519 100644 --- a/contrib/btree_gin/expected/name.out +++ b/contrib/btree_gin/expected/name.out @@ -95,3 +95,62 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i; Index Cond: (i > 'abc'::name) (6 rows) +explain (costs off) +SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_name + Recheck Cond: (i < 'abc'::text) + -> Bitmap Index Scan on idx_name + Index Cond: (i < 'abc'::text) +(6 rows) + +SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_name WHERE i='abc'::text ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i; + i +----- + axy + xyz +(2 rows) + +SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + diff --git a/contrib/btree_gin/expected/text.out b/contrib/btree_gin/expected/text.out index 3e31ad744d..7f52f3db7b 100644 --- a/contrib/btree_gin/expected/text.out +++ b/contrib/btree_gin/expected/text.out @@ -42,3 +42,53 @@ SELECT * FROM test_text WHERE i>'abc' ORDER BY i; xyz (2 rows) +explain (costs off) +SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i; + QUERY PLAN +--------------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_text + Recheck Cond: (i < 'abc'::name COLLATE "default") + -> Bitmap Index Scan on idx_text + Index Cond: (i < 'abc'::name COLLATE "default") +(6 rows) + +SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i; + i +----- + axy + xyz +(2 rows) + diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql index c11580cdf9..551d928940 100644 --- a/contrib/btree_gin/sql/name.sql +++ b/contrib/btree_gin/sql/name.sql @@ -19,3 +19,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i; EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i; EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i; EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i; + +explain (costs off) +SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i; + +SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i; +SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i; +SELECT * FROM test_name WHERE i='abc'::text ORDER BY i; +SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i; +SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i; + +SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i; diff --git a/contrib/btree_gin/sql/text.sql b/contrib/btree_gin/sql/text.sql index d5b3b39898..978b21376f 100644 --- a/contrib/btree_gin/sql/text.sql +++ b/contrib/btree_gin/sql/text.sql @@ -13,3 +13,12 @@ SELECT * FROM test_text WHERE i<='abc' ORDER BY i; SELECT * FROM test_text WHERE i='abc' ORDER BY i; SELECT * FROM test_text WHERE i>='abc' ORDER BY i; SELECT * FROM test_text WHERE i>'abc' ORDER BY i; + +explain (costs off) +SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i; + +SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i; +SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i; +SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i; +SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i; +SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i; -- 2.43.5