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@sysexperts.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
>