From fc896821c4448038c5cc678c1aff7349ee850b23 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 3 Jul 2025 16:30:38 -0400 Subject: [PATCH] Add more cross-type comparisons to contrib/btree_gin. Using the just-added infrastructure, extend btree_gin to support cross-type operators in its other opclasses. All of the cross-type comparison operators supported by the core btree opclasses for these datatypes are now available for btree_gin indexes as well. Author: Tom Lane Reviewed-by: Arseniy Mukhin Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us --- contrib/btree_gin/btree_gin--1.3--1.4.sql | 88 +++++ contrib/btree_gin/btree_gin.c | 192 +++++++++-- contrib/btree_gin/expected/date.out | 362 +++++++++++++++++++++ contrib/btree_gin/expected/float4.out | 321 ++++++++++++++++++ contrib/btree_gin/expected/float8.out | 50 +++ contrib/btree_gin/expected/name.out | 59 ++++ contrib/btree_gin/expected/text.out | 50 +++ contrib/btree_gin/expected/timestamp.out | 306 ++++++++++++++++- contrib/btree_gin/expected/timestamptz.out | 111 ++++++- contrib/btree_gin/sql/date.sql | 64 ++++ contrib/btree_gin/sql/float4.sql | 53 +++ contrib/btree_gin/sql/float8.sql | 9 + contrib/btree_gin/sql/name.sql | 11 + contrib/btree_gin/sql/text.sql | 9 + contrib/btree_gin/sql/timestamp.sql | 55 +++- contrib/btree_gin/sql/timestamptz.sql | 22 +- 16 files changed, 1725 insertions(+), 37 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 4c77138fabe..61b5dcbede6 100644 --- a/contrib/btree_gin/btree_gin--1.3--1.4.sql +++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql @@ -61,3 +61,91 @@ ADD OPERATOR 0x24 >= (int8, int4), OPERATOR 0x25 > (int8, int4) ; + +ALTER OPERATOR FAMILY float4_ops USING gin +ADD + -- Code 1: RHS is float8 + OPERATOR 0x11 < (float4, float8), + OPERATOR 0x12 <= (float4, float8), + OPERATOR 0x13 = (float4, float8), + OPERATOR 0x14 >= (float4, float8), + OPERATOR 0x15 > (float4, float8) +; + +ALTER OPERATOR FAMILY float8_ops USING gin +ADD + -- Code 1: RHS is float4 + OPERATOR 0x11 < (float8, float4), + OPERATOR 0x12 <= (float8, float4), + OPERATOR 0x13 = (float8, float4), + OPERATOR 0x14 >= (float8, float4), + OPERATOR 0x15 > (float8, float4) +; + +ALTER OPERATOR FAMILY text_ops USING gin +ADD + -- Code 1: RHS is name + OPERATOR 0x11 < (text, name), + OPERATOR 0x12 <= (text, name), + OPERATOR 0x13 = (text, name), + OPERATOR 0x14 >= (text, name), + OPERATOR 0x15 > (text, name) +; + +ALTER OPERATOR FAMILY name_ops USING gin +ADD + -- Code 1: RHS is text + OPERATOR 0x11 < (name, text), + OPERATOR 0x12 <= (name, text), + OPERATOR 0x13 = (name, text), + OPERATOR 0x14 >= (name, text), + OPERATOR 0x15 > (name, text) +; + +ALTER OPERATOR FAMILY date_ops USING gin +ADD + -- Code 1: RHS is timestamp + OPERATOR 0x11 < (date, timestamp), + OPERATOR 0x12 <= (date, timestamp), + OPERATOR 0x13 = (date, timestamp), + OPERATOR 0x14 >= (date, timestamp), + OPERATOR 0x15 > (date, timestamp), + -- Code 2: RHS is timestamptz + OPERATOR 0x21 < (date, timestamptz), + OPERATOR 0x22 <= (date, timestamptz), + OPERATOR 0x23 = (date, timestamptz), + OPERATOR 0x24 >= (date, timestamptz), + OPERATOR 0x25 > (date, timestamptz) +; + +ALTER OPERATOR FAMILY timestamp_ops USING gin +ADD + -- Code 1: RHS is date + OPERATOR 0x11 < (timestamp, date), + OPERATOR 0x12 <= (timestamp, date), + OPERATOR 0x13 = (timestamp, date), + OPERATOR 0x14 >= (timestamp, date), + OPERATOR 0x15 > (timestamp, date), + -- Code 2: RHS is timestamptz + OPERATOR 0x21 < (timestamp, timestamptz), + OPERATOR 0x22 <= (timestamp, timestamptz), + OPERATOR 0x23 = (timestamp, timestamptz), + OPERATOR 0x24 >= (timestamp, timestamptz), + OPERATOR 0x25 > (timestamp, timestamptz) +; + +ALTER OPERATOR FAMILY timestamptz_ops USING gin +ADD + -- Code 1: RHS is date + OPERATOR 0x11 < (timestamptz, date), + OPERATOR 0x12 <= (timestamptz, date), + OPERATOR 0x13 = (timestamptz, date), + OPERATOR 0x14 >= (timestamptz, date), + OPERATOR 0x15 > (timestamptz, date), + -- Code 2: RHS is timestamp + OPERATOR 0x21 < (timestamptz, timestamp), + OPERATOR 0x22 <= (timestamptz, timestamp), + OPERATOR 0x23 = (timestamptz, timestamp), + OPERATOR 0x24 >= (timestamptz, timestamp), + OPERATOR 0x25 > (timestamptz, timestamp) +; diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c index 818a33af97f..8c477d17e22 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_EXT( .name = "btree_gin", @@ -401,13 +403,34 @@ leftmostvalue_float4(void) return Float4GetDatum(-get_float4_infinity()); } +static Datum +cvt_float8_float4(Datum input) +{ + float8 val = DatumGetFloat8(input); + float4 result; + + /* + * Assume that ordinary C conversion will produce a usable result. + * (Compare dtof(), which raises error conditions that we don't need.) + * Note that for inputs that aren't exactly representable as float4, it + * doesn't matter whether the conversion rounds up or down. That might + * cause us to scan a few index entries that we'll reject as not matching, + * but we won't miss any that should match. + */ + result = (float4) val; + return Float4GetDatum(result); +} + static const bool float4_rhs_is_varlena[] = -{false}; +{false, false}; + +static const btree_gin_convert_function float4_cvt_fns[] = +{NULL, cvt_float8_float4}; static const PGFunction float4_cmp_fns[] = -{btfloat4cmp}; +{btfloat4cmp, btfloat84cmp}; -GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns) +GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, float4_cvt_fns, float4_cmp_fns) static Datum leftmostvalue_float8(void) @@ -415,13 +438,24 @@ leftmostvalue_float8(void) return Float8GetDatum(-get_float8_infinity()); } +static Datum +cvt_float4_float8(Datum input) +{ + float4 val = DatumGetFloat4(input); + + return Float8GetDatum((float8) val); +} + static const bool float8_rhs_is_varlena[] = -{false}; +{false, false}; + +static const btree_gin_convert_function float8_cvt_fns[] = +{NULL, cvt_float4_float8}; static const PGFunction float8_cmp_fns[] = -{btfloat8cmp}; +{btfloat8cmp, btfloat48cmp}; -GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns) +GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, float8_cvt_fns, float8_cmp_fns) static Datum leftmostvalue_money(void) @@ -457,21 +491,75 @@ leftmostvalue_timestamp(void) return TimestampGetDatum(DT_NOBEGIN); } +static Datum +cvt_date_timestamp(Datum input) +{ + DateADT val = DatumGetDateADT(input); + Timestamp result; + int overflow; + + result = date2timestamp_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return TimestampGetDatum(result); +} + +static Datum +cvt_timestamptz_timestamp(Datum input) +{ + TimestampTz val = DatumGetTimestampTz(input); + Timestamp result; + int overflow; + + result = timestamptz2timestamp_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return TimestampGetDatum(result); +} + static const bool timestamp_rhs_is_varlena[] = -{false}; +{false, false, false}; + +static const btree_gin_convert_function timestamp_cvt_fns[] = +{NULL, cvt_date_timestamp, cvt_timestamptz_timestamp}; static const PGFunction timestamp_cmp_fns[] = -{timestamp_cmp}; +{timestamp_cmp, date_cmp_timestamp, timestamptz_cmp_timestamp}; -GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns) +GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, timestamp_cvt_fns, timestamp_cmp_fns) + +static Datum +cvt_date_timestamptz(Datum input) +{ + DateADT val = DatumGetDateADT(input); + TimestampTz result; + int overflow; + + result = date2timestamptz_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return TimestampTzGetDatum(result); +} + +static Datum +cvt_timestamp_timestamptz(Datum input) +{ + Timestamp val = DatumGetTimestamp(input); + TimestampTz result; + int overflow; + + result = timestamp2timestamptz_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return TimestampTzGetDatum(result); +} static const bool timestamptz_rhs_is_varlena[] = -{false}; +{false, false, false}; + +static const btree_gin_convert_function timestamptz_cvt_fns[] = +{NULL, cvt_date_timestamptz, cvt_timestamp_timestamptz}; static const PGFunction timestamptz_cmp_fns[] = -{timestamp_cmp}; +{timestamp_cmp, date_cmp_timestamptz, timestamp_cmp_timestamptz}; -GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, NULL, timestamptz_cmp_fns) +GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, timestamptz_cvt_fns, timestamptz_cmp_fns) static Datum leftmostvalue_time(void) @@ -512,13 +600,40 @@ leftmostvalue_date(void) return DateADTGetDatum(DATEVAL_NOBEGIN); } +static Datum +cvt_timestamp_date(Datum input) +{ + Timestamp val = DatumGetTimestamp(input); + DateADT result; + int overflow; + + result = timestamp2date_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return DateADTGetDatum(result); +} + +static Datum +cvt_timestamptz_date(Datum input) +{ + TimestampTz val = DatumGetTimestampTz(input); + DateADT result; + int overflow; + + result = timestamptz2date_opt_overflow(val, &overflow); + /* We can ignore the overflow result, since result is useful as-is */ + return DateADTGetDatum(result); +} + static const bool date_rhs_is_varlena[] = -{false}; +{false, false, false}; + +static const btree_gin_convert_function date_cvt_fns[] = +{NULL, cvt_timestamp_date, cvt_timestamptz_date}; static const PGFunction date_cmp_fns[] = -{date_cmp}; +{date_cmp, timestamp_cmp_date, timestamptz_cmp_date}; -GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, NULL, date_cmp_fns) +GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, date_cvt_fns, date_cmp_fns) static Datum leftmostvalue_interval(void) @@ -598,13 +713,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}; @@ -804,13 +930,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/date.out b/contrib/btree_gin/expected/date.out index 40dfa308cf7..e69c1da2000 100644 --- a/contrib/btree_gin/expected/date.out +++ b/contrib/btree_gin/expected/date.out @@ -49,3 +49,365 @@ SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; 10-28-2004 (2 rows) +explain (costs off) +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i; + QUERY PLAN +----------------------------------------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_date + Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone) + -> Bitmap Index Scan on idx_date + Index Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone) +(6 rows) + +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 +(3 rows) + +SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 +(4 rows) + +SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i; + i +------------ + 10-26-2004 +(1 row) + +SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 +(3 rows) + +SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i; + i +------------ + 10-27-2004 + 10-28-2004 +(2 rows) + +explain (costs off) +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_date + Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone) + -> Bitmap Index Scan on idx_date + Index Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone) +(6 rows) + +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 +(3 rows) + +SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 +(4 rows) + +SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i; + i +------------ + 10-26-2004 +(1 row) + +SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 +(3 rows) + +SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i; + i +------------ + 10-27-2004 + 10-28-2004 +(2 rows) + +-- Check endpoint and out-of-range cases +INSERT INTO test_date VALUES ('-infinity'), ('infinity'); +SELECT gin_clean_pending_list('idx_date'); + gin_clean_pending_list +------------------------ + 1 +(1 row) + +SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(8 rows) + +SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(7 rows) + +SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 +(7 rows) + +SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(8 rows) + +SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(8 rows) + +SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(7 rows) + +SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 +(7 rows) + +SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(8 rows) + +SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i; + i +--- +(0 rows) + +-- Check rounding cases +-- '2004-10-25 00:00:01' rounds to '2004-10-25' for date. +-- '2004-10-25 23:59:59' also rounds to '2004-10-25', +-- so it's the same case as '2004-10-25 00:00:01' +SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamp ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 +(4 rows) + +SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamp ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 +(4 rows) + +SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamp ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamp ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(4 rows) + +SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamp ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(4 rows) + +SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamptz ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 +(4 rows) + +SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamptz ORDER BY i; + i +------------ + -infinity + 10-23-2004 + 10-24-2004 + 10-25-2004 +(4 rows) + +SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamptz ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamptz ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(4 rows) + +SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamptz ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 + infinity +(4 rows) + diff --git a/contrib/btree_gin/expected/float4.out b/contrib/btree_gin/expected/float4.out index 7b9134fcd4b..c8bb04e59be 100644 --- a/contrib/btree_gin/expected/float4.out +++ b/contrib/btree_gin/expected/float4.out @@ -42,3 +42,324 @@ SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i; 3 (2 rows) +explain (costs off) +SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i; + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_float4 + Recheck Cond: (i < '1'::double precision) + -> Bitmap Index Scan on idx_float4 + Index Cond: (i < '1'::double precision) +(6 rows) + +SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + +-- Check endpoint and out-of-range cases +INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf'); +SELECT gin_clean_pending_list('idx_float4'); + gin_clean_pending_list +------------------------ + 1 +(1 row) + +SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i; + i +----------- + -Infinity +(1 row) + +SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i; + i +----------- + -Infinity +(1 row) + +SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 + Infinity + NaN +(9 rows) + +SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i; + i +---------- + -2 + -1 + 0 + 1 + 2 + 3 + Infinity + NaN +(8 rows) + +SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 +(7 rows) + +SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 + Infinity +(8 rows) + +SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i; + i +---------- + Infinity +(1 row) + +SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i; + i +---------- + Infinity + NaN +(2 rows) + +SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i; + i +----- + NaN +(1 row) + +SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 +(7 rows) + +SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 +(7 rows) + +SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i; + i +---------- + Infinity + NaN +(2 rows) + +SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i; + i +---------- + Infinity + NaN +(2 rows) + +SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 + Infinity +(8 rows) + +SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 + 1 + 2 + 3 + Infinity + NaN +(9 rows) + +SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i; + i +----- + NaN +(1 row) + +SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i; + i +----- + NaN +(1 row) + +SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i; + i +--- +(0 rows) + +-- Check rounding cases +-- 1e-300 rounds to 0 for float4 but not for float8 +SELECT * FROM test_float4 WHERE i < -1e-300::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 +(3 rows) + +SELECT * FROM test_float4 WHERE i <= -1e-300::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 +(3 rows) + +SELECT * FROM test_float4 WHERE i = -1e-300::float8 ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_float4 WHERE i > -1e-300::float8 ORDER BY i; + i +---------- + 0 + 1 + 2 + 3 + Infinity + NaN +(6 rows) + +SELECT * FROM test_float4 WHERE i >= -1e-300::float8 ORDER BY i; + i +---------- + 0 + 1 + 2 + 3 + Infinity + NaN +(6 rows) + +SELECT * FROM test_float4 WHERE i < 1e-300::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 +(4 rows) + +SELECT * FROM test_float4 WHERE i <= 1e-300::float8 ORDER BY i; + i +----------- + -Infinity + -2 + -1 + 0 +(4 rows) + +SELECT * FROM test_float4 WHERE i = 1e-300::float8 ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_float4 WHERE i > 1e-300::float8 ORDER BY i; + i +---------- + 1 + 2 + 3 + Infinity + NaN +(5 rows) + +SELECT * FROM test_float4 WHERE i >= 1e-300::float8 ORDER BY i; + i +---------- + 1 + 2 + 3 + Infinity + NaN +(5 rows) + diff --git a/contrib/btree_gin/expected/float8.out b/contrib/btree_gin/expected/float8.out index a41d4f9f6bb..b2877dfa3c1 100644 --- a/contrib/btree_gin/expected/float8.out +++ b/contrib/btree_gin/expected/float8.out @@ -42,3 +42,53 @@ SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i; 3 (2 rows) +explain (costs off) +SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_float8 + Recheck Cond: (i < '1'::real) + -> Bitmap Index Scan on idx_float8 + Index Cond: (i < '1'::real) +(6 rows) + +SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out index 174de6576f0..3a30f62519c 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 3e31ad744d6..7f52f3db7b3 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/expected/timestamp.out b/contrib/btree_gin/expected/timestamp.out index a236cdc94a9..b7565285e68 100644 --- a/contrib/btree_gin/expected/timestamp.out +++ b/contrib/btree_gin/expected/timestamp.out @@ -7,8 +7,8 @@ INSERT INTO test_timestamp VALUES ( '2004-10-26 04:55:08' ), ( '2004-10-26 05:55:08' ), ( '2004-10-26 08:55:08' ), - ( '2004-10-26 09:55:08' ), - ( '2004-10-26 10:55:08' ) + ( '2004-10-27 09:55:08' ), + ( '2004-10-27 10:55:08' ) ; CREATE INDEX idx_timestamp ON test_timestamp USING gin (i); SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; @@ -38,14 +38,308 @@ SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i -------------------------- Tue Oct 26 08:55:08 2004 - Tue Oct 26 09:55:08 2004 - Tue Oct 26 10:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 (3 rows) SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; i -------------------------- - Tue Oct 26 09:55:08 2004 - Tue Oct 26 10:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 (2 rows) +explain (costs off) +SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i; + QUERY PLAN +---------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_timestamp + Recheck Cond: (i < '10-27-2004'::date) + -> Bitmap Index Scan on idx_timestamp + Index Cond: (i < '10-27-2004'::date) +(6 rows) + +SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i; + i +-------------------------- + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(2 rows) + +SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i; + i +-------------------------- + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(2 rows) + +explain (costs off) +SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_timestamp + Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone) + -> Bitmap Index Scan on idx_timestamp + Index Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone) +(6 rows) + +SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 08:55:08 2004 +(1 row) + +SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +-------------------------- + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(2 rows) + +-- Check endpoint and out-of-range cases +INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity'); +SELECT gin_clean_pending_list('idx_timestamp'); + gin_clean_pending_list +------------------------ + 1 +(1 row) + +SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(8 rows) + +SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(7 rows) + +SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(7 rows) + +SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(8 rows) + +SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(8 rows) + +SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(7 rows) + +SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 +(7 rows) + +SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i; + i +-------------------------- + -infinity + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(8 rows) + +SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i; + i +---------- + infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i; + i +--- +(0 rows) + +-- This PST timestamptz will underflow if converted to timestamp +SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i; + i +----------- + -infinity +(1 row) + +SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Wed Oct 27 09:55:08 2004 + Wed Oct 27 10:55:08 2004 + infinity +(7 rows) + diff --git a/contrib/btree_gin/expected/timestamptz.out b/contrib/btree_gin/expected/timestamptz.out index d53963d2a04..0dada0b662c 100644 --- a/contrib/btree_gin/expected/timestamptz.out +++ b/contrib/btree_gin/expected/timestamptz.out @@ -7,8 +7,8 @@ INSERT INTO test_timestamptz VALUES ( '2004-10-26 04:55:08' ), ( '2004-10-26 05:55:08' ), ( '2004-10-26 08:55:08' ), - ( '2004-10-26 09:55:08' ), - ( '2004-10-26 10:55:08' ) + ( '2004-10-27 09:55:08' ), + ( '2004-10-27 10:55:08' ) ; CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i); SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; @@ -38,14 +38,113 @@ SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER i ------------------------------ Tue Oct 26 08:55:08 2004 PDT - Tue Oct 26 09:55:08 2004 PDT - Tue Oct 26 10:55:08 2004 PDT + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT (3 rows) SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; i ------------------------------ - Tue Oct 26 09:55:08 2004 PDT - Tue Oct 26 10:55:08 2004 PDT + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT +(2 rows) + +explain (costs off) +SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i; + QUERY PLAN +---------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_timestamptz + Recheck Cond: (i < '10-27-2004'::date) + -> Bitmap Index Scan on idx_timestamptz + Index Cond: (i < '10-27-2004'::date) +(6 rows) + +SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i; + i +------------------------------ + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT +(2 rows) + +SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i; + i +------------------------------ + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT +(2 rows) + +explain (costs off) +SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; + QUERY PLAN +----------------------------------------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_timestamptz + Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone) + -> Bitmap Index Scan on idx_timestamptz + Index Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone) +(6 rows) + +SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +------------------------------ + Tue Oct 26 08:55:08 2004 PDT +(1 row) + +SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +------------------------------ + Tue Oct 26 08:55:08 2004 PDT + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; + i +------------------------------ + Wed Oct 27 09:55:08 2004 PDT + Wed Oct 27 10:55:08 2004 PDT (2 rows) diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql index 35086f6b81b..006f6f528b8 100644 --- a/contrib/btree_gin/sql/date.sql +++ b/contrib/btree_gin/sql/date.sql @@ -20,3 +20,67 @@ SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; + +explain (costs off) +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i; + +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i; + +explain (costs off) +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i; + +SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i; + +-- Check endpoint and out-of-range cases + +INSERT INTO test_date VALUES ('-infinity'), ('infinity'); +SELECT gin_clean_pending_list('idx_date'); + +SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i; + +SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i; + +SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i; + +SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i; + +-- Check rounding cases +-- '2004-10-25 00:00:01' rounds to '2004-10-25' for date. +-- '2004-10-25 23:59:59' also rounds to '2004-10-25', +-- so it's the same case as '2004-10-25 00:00:01' + +SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamp ORDER BY i; +SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamp ORDER BY i; + +SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamptz ORDER BY i; +SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamptz ORDER BY i; diff --git a/contrib/btree_gin/sql/float4.sql b/contrib/btree_gin/sql/float4.sql index 759778ad3c3..0707ed6518f 100644 --- a/contrib/btree_gin/sql/float4.sql +++ b/contrib/btree_gin/sql/float4.sql @@ -13,3 +13,56 @@ SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i; SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i; SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i; SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i; + +explain (costs off) +SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i; + +SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i; + +-- Check endpoint and out-of-range cases + +INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf'); +SELECT gin_clean_pending_list('idx_float4'); + +SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i; + +SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i; + +SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i; + +SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i; + +-- Check rounding cases +-- 1e-300 rounds to 0 for float4 but not for float8 + +SELECT * FROM test_float4 WHERE i < -1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i <= -1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i = -1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i > -1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i >= -1e-300::float8 ORDER BY i; + +SELECT * FROM test_float4 WHERE i < 1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i <= 1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i = 1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i > 1e-300::float8 ORDER BY i; +SELECT * FROM test_float4 WHERE i >= 1e-300::float8 ORDER BY i; diff --git a/contrib/btree_gin/sql/float8.sql b/contrib/btree_gin/sql/float8.sql index b046ac4e6c4..5f393147082 100644 --- a/contrib/btree_gin/sql/float8.sql +++ b/contrib/btree_gin/sql/float8.sql @@ -13,3 +13,12 @@ SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i; SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i; SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i; SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i; + +explain (costs off) +SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i; + +SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i; +SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i; +SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i; +SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i; +SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i; diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql index c11580cdf96..551d9289407 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 d5b3b398989..978b21376fd 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; diff --git a/contrib/btree_gin/sql/timestamp.sql b/contrib/btree_gin/sql/timestamp.sql index 56727e81c4a..1ee4edb5ea4 100644 --- a/contrib/btree_gin/sql/timestamp.sql +++ b/contrib/btree_gin/sql/timestamp.sql @@ -9,8 +9,8 @@ INSERT INTO test_timestamp VALUES ( '2004-10-26 04:55:08' ), ( '2004-10-26 05:55:08' ), ( '2004-10-26 08:55:08' ), - ( '2004-10-26 09:55:08' ), - ( '2004-10-26 10:55:08' ) + ( '2004-10-27 09:55:08' ), + ( '2004-10-27 10:55:08' ) ; CREATE INDEX idx_timestamp ON test_timestamp USING gin (i); @@ -20,3 +20,54 @@ SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; + +explain (costs off) +SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i; + +SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i; + +explain (costs off) +SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; + +SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; + +-- Check endpoint and out-of-range cases + +INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity'); +SELECT gin_clean_pending_list('idx_timestamp'); + +SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i; + +SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i; + +SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i; + +SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i; + +-- This PST timestamptz will underflow if converted to timestamp +SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i; diff --git a/contrib/btree_gin/sql/timestamptz.sql b/contrib/btree_gin/sql/timestamptz.sql index e6cfdb1b074..40d2d7ed329 100644 --- a/contrib/btree_gin/sql/timestamptz.sql +++ b/contrib/btree_gin/sql/timestamptz.sql @@ -9,8 +9,8 @@ INSERT INTO test_timestamptz VALUES ( '2004-10-26 04:55:08' ), ( '2004-10-26 05:55:08' ), ( '2004-10-26 08:55:08' ), - ( '2004-10-26 09:55:08' ), - ( '2004-10-26 10:55:08' ) + ( '2004-10-27 09:55:08' ), + ( '2004-10-27 10:55:08' ) ; CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i); @@ -20,3 +20,21 @@ SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; + +explain (costs off) +SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i; + +SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i; + +explain (costs off) +SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; + +SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; -- 2.39.5