Re: [HACKERS] Interesting behaviour ! - Mailing list pgsql-hackers

I wrote:
>> This might be a relatively simple bug to fix after all,
>> but it needs more time to find exactly where things are going wrong...
>> and I have to get some Real Work done...

Well, no, it's not simple.  After looking at the executor I can see that
indexscan support is only prepared to deal with comparison operators
that are in the pg_amop class associated with the index.  In other
words, for an int2 index the indexquals have to be "int2 op int2".
The optimizer is doing the right thing by not trying to use int24eq
as an indexqual.

So, we're back to needing to figure out that we can reduce the int4
constant to an int2 constant and adjust the comparison operator
appropriately.

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Don't let me stop anyone from looking at this, but fyi this is the one
> area I didn't yet touch for the "transparent type coersion" work I did
> for v6.4 and which is still ongoing of course. 
>
> istm that wherever index use is evaluated one could allow
> pre-evaluated functions on constants, rather than just strict
> constants as is the case now. There is a precedent for pre-evaluation
> of elements of the query tree.

Perhaps that could be handled by the constant-subexpression-reducer
that I want to add.  That is, "typeconversionfunction(constant)" would
be reduced to "constant", and then the optimizer has the same case to
deal with as it has now.

> If function calls are allowed, then we can try coercing constants
> using these existing coersion functions,

Where are said functions?  I have not run across them yet...

> at least when the target
> column is a "superset type" of the constant. You still run into
> trouble for cases like
>   select intcol from table1 where intcol < 2.5;

Right, you don't want to truncate the float constant to integer
(at least not without adding even more smarts).

I think we are probably going to have to do this in the form of code
that has some type-specific knowledge about conversions between certain
standard types, and knows some things about the operators on those types
as well.  Here is another example that can produce trouble:select int2col + 30000 from table1;
If we reduce the int4 constant to int2 and change int24add to int2add,
we have just created a potential for int2 overflow in an expression
that did not have it before.  So, while folding an int4 constant to int2
if it's within int2 range is safe when the constant is an argument of
a comparison operator, it is *not* safe in the general case.

I don't see any real good way to build a type-independent transformation
routine that can do this sort of thing.  Probably best just to hardcode
it for the standard numeric and character-string types, and leave it at
that.
        regards, tom lane