BUG #4216: Index scan goes wrong with crosstype comparison and between in one statement - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4216
Logged by:          Sokolov Yura aka "funny_falcon"
Email address:      funny.falcon@gmail.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 !!!