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

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"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