Description of implicit conversions in PL/SQL
Description of implicit conversion in Postgres
In light of this and the mysql implicit conversion for comparison types
I think fundamentally the model is as follows:
1. For overloaded comparison operators (>,<, >=, <=, <>, =):
If both the operands have the same type, then we have an exact match
otherwise, If one of the operands is a date and the other is not, then convert the other to a date.
otherwise, If the two arguments are not of the same type, convert them both to double and do the comparison.
Note that we do not do this for LIKE, RLIKE and REGEXP as those operators are not overloaded and every type is convertible to string
2. For overloaded arithmetic operators (+, -, *, /, %, &, |, ^):
If both the operands have the same types then we have an exact match
otherwise we convert everything to double
In this case the logical operators will just work as is as they are not overloaded in the sense that they do not have multiple evaluate functions.
We allow seemless conversions within numbers, from string to numbers and vice versa as well as from date to string and vice versa. We may allow seemless conversion between date and int at some point (once we support date more formally)
When looking for a match in a generic overloaded function (which is user defined and not one of the cases enumerated above as we have already addressed those), we use the current mechanism of picking up an evaluate function that needs
least number of conversions, otherwise we throw an ambiguity.
So instead of encoding hierarchies, in the UDFTo... classes we will encode the conversion rules and then use the above to figure out the matches.
I think this will give us the same results as mysql and other DBs as well.
One thing that is still not very clean in this is the bit operators...
I think this somewhat addresses all the concerns except the storage one which Joy raised, but even Oracle there warns about the same issue and asks the user to casting properly. These set of rules are also hopefully much simpler to code and to explain to the users.