
> I think there is some confusion here.  The runtime checks Andreas was
> talking about was allowing a double of 64.0 to cast to an int4 while
> disallowing 64.1 from being cast to an int4 because it is not a hole
> number.  

Yes, and Tom's proposal for numbers is sufficient for constants, since the 64.0
will initially be an int2 and thus do the correct thing together with an int4,
and the 64.1 constant will be a numeric, and thus also do the correct thing with
all other types.

It is not sufficient for the optimizer for joins though, since it cannot use the 
int4 index when confronted with "where tab1.int4col = tab2.numericcol".
Here only a runtime (non aborting) check would help.
Maybe this could be overcome if the index access (or something inbetween) would allow
a "numeric" constant for an int4 index (If the "numeric" value does not cleanly convert
to int4, return no rows).

Andreas

Tue, 17 Sep 2002 10:47:05 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Proposal for resolving casting issues
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> It is not sufficient for the optimizer for joins though, since it
> cannot use the int4 index when confronted with "where tab1.int4col =
> tab2.numericcol".

For cross-datatype joins, the proposal as I sketched it would result in
the parser producing, eg,
	where tab1.int4col::numeric = tab2.numericcol
that is, we'd have a single-datatype operator and a runtime cast in the
expression.

The optimizer is today capable of producing a nested loop with inner
indexscan join from this --- so long as the inner indexscan is on the
uncasted column (numericcol in this case).  It won't consider an int4
index on int4col for this.  This seems okay to me, actually.  It's
better than what you get now with a cross-datatype comparison operator
(neither side can be indexscanned since the operator matches neither
index opclass).

The major failing that needs to be rectified is that merge and hash
joins won't even be considered, because that code only works with
quals that are unadorned "Var = Var".  I don't believe there is any
fundamental reason for this restriction.  As long as the top operator
is merge/hashjoinable, any expression should work on either side.
It's just a matter of cleaning up a few unwarranted shortcuts in the
planner.

But that work does need to be done before we can rip out all the
cross-datatype operators ... so this is definitely not happening
for 7.3 ...

			regards, tom lane

Tue, 17 Sep 2002 09:38:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 