Re: New thoughts about indexing cross-type comparisons

For the int2col op value I have this table for when the cast returns NULL

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 <kevin(at)sysexperts(dot)com> 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
>