Rules for MySQL type conversion

Question

I'm wondering what the rules are for mysql type conversions are. e.g.

select foo/2 from table seems to yield a decimal if the foo column is an int. select sum(foo) from table gives the sum(foo) column back as a double if foo is a float. select i*i from table gives a bigint if i is an int.

What are the rules for the type conversion here when using common operators such as +-/* or aggregates such as sum/avg ?

Answer 1

The rules of type conversion are quite straightforward. Quoting from the MySQL Manual :: Type Conversion in Expression Evaluation:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly.

The following rules describe how conversion occurs for comparison operations:

• If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

• If both arguments in a comparison operation are strings, they are compared as strings.

• If both arguments are integers, they are compared as integers.

• Hexadecimal values are treated as binary strings if not compared to a number.

• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN() To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

• In all other cases, the arguments are compared as floating-point (real) numbers.

In the case of arithmetic operators, the result is determined according to the following rules. Quoting from the MySQL Manual :: Arithmetic Operators

• In the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both arguments are integers.

• If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer.

• If any of the operands of a +, -, /, *, %is a real or string value, the precision of the result is the precision of the argument with the maximum precision.

• In division performed with /, the scale of the result when using two exact values is the scale of the first argument plus the value of the div_precision_increment system variable (which is 4 by default). For example, the result of the expression 5.05 / 0.014 has a scale of six decimal places (360.714286).

Then for aggregate functions, the following applies. Quoting from the MySQL Manual :: Aggregate Functions:

For numeric arguments, the variance and standard deviation functions return a DOUBLE value. The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).