What is the standard for division between integers?
I've just found a difference in behaviour between MySQL and MSSQL server (2000 and 2003).
The simple query
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 "/"?
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.
Last edited by Horinius; 08-23-2007 at 09:36 AM.
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.
Originally Posted by Horinius
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.
So, in summary, there's not standard (ISO or ANSI or whatever) to govern the meaning of "/", right?
Well, yes, "/" is an ANSI standard for division.
But the results of the division is a different matter.
Last edited by bubbisthedog; 08-23-2007 at 10:27 AM.
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.
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?
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)