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 !!!
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 |