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

"Sokolov Yura aka "funny_falcon"" <funny.falcon@gmail.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs