MySQL Bugs: #9440: Integer division results in float, no float cast.

Bug #9440	Integer division results in float, no float cast.
Submitted:	29 Mar 2005 3:09	Modified:	29 Mar 2005 14:56
Reporter:	Steven Wittens	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Dividing two integers in MySQL results in a float number. This is inconsistent with how PGSQL and MSSQL do it. They only return a float if at least one of the arguments was a float or if an explicit cast to float is performed on the two integers.

MySQL provides a separate 'DIV' operator for integer division since 4.1. It seems odd to me that you would implement it like that, as it goes against what almost every typed programming language does. It is also inconsistent with the other arithmetic operators in MySQL (2*3 = 6, 2.0*3.0 = 6.0).

The problem is worse because there is no way to achieve consistency across database types. If I wanted to get floating point division out of integers on PGSQL for example, I'd use CAST(column AS float), but MySQL does not recognize this and throws a parse error.

How to repeat:
SELECT 3/2;
-> returns 1.5
-> expected return value 1 (truncation)

SELECT 3.0/2.0;
-> returns 1.5
-> correct.

SELECT CAST(3 AS float)/CAST(2 AS float);
-> parse error
-> expected return value 1.5 (floating point division)

Suggested fix:
- Division of two integers should result in an integer result.
- The DIV operator should be deprecated.
- CAST() should be expanded to support casting to floating point datatypes, so floating point division can still be achieved if desired.

(Note, even if the current division behaviour is kept, expanding CAST() to support more datatypes is a good idea regardless)

We cannot change the behaviour that drastically in the stable branch such as  4.1.

And 5.0 already behaves according to the standard (though it's not the way you want):

1) If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows:
a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2.
d) The precision and scale of the result of division are implementation-defined.
2) If the declared type of either operand of a dyadic arithmetic operator is approximate numeric, then the declared type of the result is an implementation-defined approximate numeric type.

Note that integer is <exact numeric type>,  while MySQL double is <approximate numeric type>.

SQL:2003 says that the result of dividing "exact numerics"
(decimal, numeric, integer, smallint, tinyint, bigint) must be an "exact numeric" with an "implementation-defined"
precision and scale. MySQL follows that rule in 5.0.4.
If I say "create table tte as select 5 / 3;" and then "show create table tte;" I see that the column is DECIMAL, not FLOAT. The complainer would prefer that the result be INTEGER, but the fact is, DECIMAL is okay.

So does MySQL 5 at least support all datatypes in CAST()?