...
Data type conversion behaviors for MS-SQL
Proposed Design
...
The Common Type
The whole rules to find the proper conversion type:
...
We try the best to find the tightest common type that does not lose precision, for example DOUBLE and INT return DOUBLE, DATE and TIMESTAMP return TIMESTAMP; If we can not find the tightest common type, then try to coerce all the operands to VARCHAR type which may lose some precision(or 2 DECIMALs with wider precision/scale).
Conversion
...
Context and
...
Strategy
SQL Contexts | Expression subtype | Strategies |
Set Operation | union/except/intersect | compare the data type of each branch row to find the common type of each fields pair |
Arithmetic Expression | binary arithmetic: [+, -, &, |, ^, /, %, pmod] | 1. promote string operand to data type of the other numeric operand; 2. two strings would all be coerced to DECIMAL. |
binary comparison: [=, <, <=, >, >=, <>] | 1. promote string and timestamp to timestamp; 2. make 1=true and 0=false always evaluates true; 3. find common type for both operands if there is numeric type operand. | |
IN Expression | with subquery | compare type of LHS and RHS, find the common type, if it is struct type, find wider type for every field |
without subquery | if RHS is a expr list, compare every expr to find the wider type | |
Special AGG Function | promote string all to decimal type | |
Case When Expression | case when expression | find then and else operands common wider type |
Colesce FUNC | same as case when | |
[Date|Timestamp|String] +/- interval | promote string to timestamp | |
Function with Expected Inputs Type | builtin functions | look up the families registered in the operand type checker, find the family default type if rule allows it |
UDF/UDAF | try to coerce based on the argument operands types of eval() func |
...