A basic requirement is the ability to build keys for index lookups whenever possible, without regard to mixed datatypes of any of the join predicates versus the index key. Consider the following query:
create table T1 (c1 int, c2 int) create table T2 (c1 int, c2 float) create index i1 on T1(c2) create index i1 on T2(c2) select * from T1, T2 where T1.c2=T2.c2
Assume that T1.c2 is of type int and has an index on it, and that T2.c2 is of type float with an index.
As long as datatypes are implicitly convertible, the query optimizer can use index scans to process the join. In other words, the query optimizer uses the column value from the outer table to position the index scan on the inner table, even when the lookup value from the outer table has a different datatype than the respective index attribute of the inner table.
