What is the standard for division between integers?

[Horinius]

I've just found a difference in behaviour between MySQL and MSSQL server (2000 and 2003).

The simple query
select 1/2;
returns 0.5 in MySQL but 0 in MSSQL. That means MSSQL is doing integer division for the "/" symbol (when both operands are integers).

Is there any standard to fix the meaning of "/"?

[bubbisthedog]

What version of MySQL?

[Horinius]

Why? You can't get the same result as me?

Mine is version 5.0 community running in WinNT platform. But I think it's the same for version 4 as well.

NB: I knew the workaround to make sure that both DBMS produce the same result by using conversion, but that's not my point. I want to know if there's a standard on this, and which DBMS has wrong implementation.

[bubbisthedog]

Why?
You should always inform us of what version of RDBMS you're using because some functions are available in versions that others are not. It's just good to know up front.

Neither engine is producing a "wrong" result. I was thinking that maybe the CONVERT() function, available in both MS SQL and MySQL 5.0+, would work about the same in both engines; but they're not 'standards,' per se. On the other hand, I believe the CAST() function is an ANSI/ISO standard, and is available in both SQL Server and MySQL 5.0+. CAST() is used to convert a field data type into another data type. So, theoretically, if you converted the integer to a decimal data type, then you'd have complete control over how the results are returned.
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

[Horinius]

So, in summary, there's not standard (ISO or ANSI or whatever) to govern the meaning of "/", right?

[bubbisthedog]

Well, yes, "/" is an ANSI standard for division.

http://sqlcourse2.com/math.html

But the results of the division is a different matter.

[bubbisthedog]

Perhaps this will clarify matters:

Both engines know that "/" is meant for division; that's because "/" is the ANSI standard by which both engines abide. But how they return the results following the division is not standardized.

Better?

[mattyblah]

in sql server, you should probably use 1. / 2 as sql server will implicitly change the type of the value returned based on the situation. in other words, 1 and 2 are both integers, so the return is an integer, where as 1. is a float and 2 is an integer, so sql server will convert 2 to a float and return a float. does that make sense?