The following bug has been logged online:
Bug reference: 4216
Logged by: Sokolov Yura aka "funny_falcon"
Email address: [EMAIL PROTECTED]
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 !!!
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
