www.webdeveloper.com
Results 1 to 8 of 8

Thread: What is the standard for division between integers?

  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Question 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
    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 "/"?

  2. #2
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    What version of MySQL?
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    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.

  4. #4
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    Quote Originally Posted by Horinius
    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/...#function_cast
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    So, in summary, there's not standard (ISO or ANSI or whatever) to govern the meaning of "/", right?

  6. #6
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    Well, yes, "/" is an ANSI standard for division.

    http://sqlcourse2.com/math.html

    But the results of the division is a different matter.
    Last edited by bubbisthedog; 08-23-2007 at 10:27 AM.
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  7. #7
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    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?
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  8. #8
    Join Date
    Jul 2004
    Posts
    300
    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?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles