Re: type coersion (was OR clause status)

<I changed "x" to "i2" in the example for clarity>

> ... have an int2 column called i2, how do you place the conversion
> functions when it is being compared to an in4 constant?
>         i2 = int2(500)
>         int4(i2) = 500
> The first is good for indexing, the second is not.  If they are both
> variables or both constants, the handling does not matter.

Yes the handling does matter *in general*, since
  while i4 = 500.1
cannot be evaluated as
  while i4 = int4(500.1)
and get the right result.
Even for the types in your example,
  while i2 = 4000000
should execute correctly, even though we both know that *for this case*
it doesn't make a lot of sense since the constant exceeds the range of
the column.

Actually, there are a lot of mixed-type comparison functions in pg_proc.
For example, there is a int24eq function which would be used for your
query outside of a "where clause". But, read on...

*slaps forehead*

It dawned on me while I was waking up this morning that we'd forgotten a
major bit of info about indexing. The pg_proc routines which are
declared for =, <, >, etc. are _not_ directly used to access indices! It
is of course the pg_am, pg_amop, and pg_amproc tables which are used for
this.

Here are examples from v6.3.2:

regression=> explain select * from tenk1 where unique1 = 3000;
Index Scan on tenk1  (cost=2.05 size=1 width=100)
regression=> explain select * from tenk1 where unique1 = 3000+1;
Seq Scan on tenk1  (cost=512.00 size=1000 width=100)
regression=> explain select * from tenk1 where unique1 = int4(3000.1);
Seq Scan on tenk1  (cost=512.00 size=1000 width=100)

The *only case* I've noticed so far which does better in v6.3.2 than
"v6.4today" (not yet v6.4alpha :) is the one involving OIDs:
regression=> explain select * from tenk1 where oid = 3000;
Index Scan on tenk1  (cost=2.05 size=1 width=100)

And remember that there are a lot of cases which do better in v6.4today
than in earlier versions.

Let's try to figure out how to get constant expressions using indices,
rather than just patching to get mismatched constant values using them.
And even for that I should be able to fix up the "binary compatible"
cases such as OID and int4 without too much trouble. Just need to find
the right spot in the index handling.

However, I should be able to find that by looking for references to the
pg_am* tables in the source code now that my brain is partly unwedged :)
Will do that, and we might still want to figure out how to use Vadim's
new PARAM_EXEC nodes for function calls on constants and constant
expressions (e.g. func1(func2(const1+const2))+const3) in the parser.

                   - Tom