value <0 <, <= ,= int2col=null >,>= in2col is not null
value > 0 <,<= in2col is not null =,>,>= int2col=null
Im not sure why pg allows me to do a int2col=null and returns nothing so I am assuming that internally pg just resolves this to false.
Tom Lane wrote:
Kevin Brown <[EMAIL PROTECTED]> writes:
Hmm...but what if the cast were to return NULL in the event that the cast fails or cannot be done? Would that even be reasonable?
Yeah, I was wondering about that myself. I'd not want to try to use such an idea in general, but if we find that int2 indexes are the only sore spot in an otherwise-useful solution, some klugery for int2 might be the way to go. What I was visualizing was that for an int2 index, we might transform "int2col op int4-or-int8-comparison-value" into "int2col int2op special_cast_fn(int4-or-int8-comparison-value)" where the trick is to make up a good special_cast_fn (possibly one specific to the comparison op being used).
Returning NULL might be an acceptable substitute when the cast function wants to force an always-false answer, but what about cases where it needs to force an always-true answer? For instance int2col < 1000000 should yield true always. There's no int2 value the cast function could output to make that happen. I thought maybe we could hack it by changing the operator to "<=" and introducing an offset of -1 in the cast function to compensate. I haven't worked out all the combinations though, and I'm not real sure that it's acceptable to substitute NULL for always-false cases. It'd work at the top level of WHERE but possibly not in other cases where indexscanning is desirable.
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
