BUG #4216: Index scan goes wrong with crosstype comparison and between in one statement

From: "Sokolov Yura aka "funny_falcon"" <funny(dot)falcon(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4216: Index scan goes wrong with crosstype comparison and between in one statement
Date: 2008-05-30 09:13:18
Message-ID: 200805300913.m4U9DIPI091945@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 4216
Logged by: Sokolov Yura aka "funny_falcon"
Email address: funny(dot)falcon(at)gmail(dot)com
PostgreSQL version: 8.3.1
Operating system: debian etch 4r3
Description: Index scan goes wrong with crosstype comparison and
between in one statement
Details:

I've created a new type - time_interval, define operators on it and
timestamp, add those operators into OPERATOR FAMILY datetime_ops USING
btree;

When I query a table using BETWEEN and equality timestamp = time_interval
(which means timestamp included in time_interval) then statement gives
strange results.
It seems that query optimization goes wrong when tries to simplify
condition.

Stripped working example:

/***************************************************/
\c postgres

drop database test_eq;

CREATE DATABASE test_eq
WITH TEMPLATE template0;

\c test_eq

set lc_messages='C';

create type time_interval as (
start timestamp,
stop timestamp
);

--------------------------------

create or replace function timestamp_more_time_interval(time_interval,
timestamp)
returns boolean
language sql as $$
select $2 >= $1.stop
$$ strict immutable;

create or replace function timestamp_lesseq_time_interval(time_interval,
timestamp)
returns boolean
language sql as $$
select $2 < $1.stop
$$ strict immutable;
-------------------------------
create or replace function timestamp_in_time_interval(timestamp,
time_interval)
returns boolean
language sql as $$
select $1 >= $2.start and $1 < $2.stop
$$ strict immutable;

-------------------------------
create or replace function timestamp_time_interval_compare(timestamp,
time_interval)
returns int4
language sql as $$
select case when $1 < $2.start then -1
when $1 >= $2.stop then 1
else 0
end $$
strict immutable;

--------------------------------

create operator = (
procedure = timestamp_in_time_interval,
leftarg = timestamp, rightarg = time_interval,
commutator = =
);

create operator < (
procedure = timestamp_more_time_interval,
leftarg = time_interval, rightarg = timestamp,
commutator = >,
negator = >=
);

create operator >= (
procedure = timestamp_lesseq_time_interval,
leftarg = time_interval, rightarg = timestamp,
commutator = <=,
negator = <
);

ALTER OPERATOR FAMILY datetime_ops USING btree ADD
operator 3 = (timestamp, time_interval),
function 1 timestamp_time_interval_compare(timestamp, time_interval),
operator 1 < (time_interval, timestamp),
operator 4 >= (time_interval, timestamp)
;

create table test_bug
(
id serial primary key,
ts timestamp not null
);

create index test_bug_ix_ts on test_bug
( ts );

insert into test_bug (ts)
select '2008-01-01'::timestamp + i*'1 hour'::interval
from generate_series(0, 71) as i;

\echo
\echo SHOULD RETURN 24
select count(*) from test_bug
where ts >= '2008-01-02' and ts < '2008-01-03';
-- should be 24 and returns 24

\echo
\echo SHOULD RETURN 24
select count(*) from test_bug
where ts = ('2008-01-02', '2008-01-03');
-- should be 24 and returns 24

\echo
\echo SHOULD RETURN 24
select count(*) from test_bug
where ts >= '2008-01-02' and ts < '2008-01-03'
and ts >= '2008-01-01' and ts < '2008-01-04';
-- should be 24 and returns 24

\echo
\echo SHOULD RETURN 24
select count(*) from test_bug
where ts = ('2008-01-02', '2008-01-03')
and ts = ('2008-01-01', '2008-01-04');
-- should be 24 and returns 24

\echo
\echo SHOULD RETURN 24
select count(*) from test_bug
where ts >= '2008-01-02' and ts < '2008-01-03'
and ts = ('2008-01-01', '2008-01-04');
-- should be 24 and returns 0 !!!

\echo
\echo SHOULD RETURN 48
select count(*) from test_bug
where ts >= '2008-01-02' and ts < '2008-01-04'
and ts = ('2008-01-01', '2008-01-04');
-- should be 48 and returns 72 !!!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Shantanu 2008-05-30 16:27:49 what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"
Previous Message ROUSSEAU Jacques 2008-05-30 08:28:18 BUG #4214: Bug at installaing 8.1.11