Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Sokolov Yura aka " funny_falcon "" <funny(dot)falcon(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4216: Index scan goes wrong with crosstype comparison and between in one statement
Date: 2008-05-31 16:07:15
Message-ID: 16631.1212250035@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Sokolov Yura aka "funny_falcon"" <funny(dot)falcon(at)gmail(dot)com> writes:
> 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.

I think your operator class is broken.  You can't just invent some
randomly-defined operators that work sort of like a normal scalar
ordering and then expect btree indexes to work.  In particular,
this set of operators violates the transitive law when taken together
with regular timestamp equality: for two timestamps T1 and T2 and
some interval TI, we can have T1 = TI and T2 = TI from which it
should follow that T1 = T2, yet that does not follow.  But by putting
this notion of "equality" into the same opfamily as timestamp equality,
you are promising the system that it *does* follow.  I didn't bother
digging through the code to see exactly where that assumption comes into
play, but that's basically what's biting you.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: ShantanuDate: 2008-05-31 17:21:35
Subject: Re: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"
Previous:From: Tom LaneDate: 2008-05-31 15:25:36
Subject: Re: problems compiling in 64 bits

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group