Your proposal very quickly dives into implementation details. For type-casting
to work, the rules need to be simple to understand for end-users. I think you
should describe those rules first, and only then describe how the rules should
be implemented.
One particular case that needs to be handled is aggregate functions. Suppose
that you write "min(c)", and c contains the values {10, ‘9’}. The system needs
to know whether to invoke the numeric or string variant of “min”, before it has
looked at any data.
There is also the issue that the type of a bind variable needs to be inferred
from its context, say “select * from emp where x + ? > 10”.
Optiq’s validator has very extensive support for type-derivation, and that work
is consistent with the SQL standard. (SQL leaves some of the type-derivation
rules to the implementor, and Optiq has chosen sensible defaults. We argued
long and hard about what should be the precision and scale of the return from
dividing two decimals.) See this package:
http://www.hydromatic.net/optiq/optiq-core/apidocs/org/eigenbase/sql/type/package-summary.html.
Even if you don’t use it, it will give you an idea of some of the concepts at
play.
Julian
