4 Replies Latest reply on Mar 1, 2017 6:22 AM by m.ardito

    Integer division result

    m.ardito
    m.ardito Feb 24, 2017 8:24 AM

      What is the expected output of SELECT <integer>/<integer> ?

       

      Like SELECT 1/2

       

      In Squirrel I try that, and I get 0, while I would like to get 0.5

      Tried parsing, casting, but nothing seems to work, how should I do?

       

      in the same way, SELECT 23/2 gives 11, not 11.5

      while SELECT 22/2 gives 11

       

      (Teiid 9.0.2.)

       

      Thanks

      • 182 Views
      • Tags:
        • 1. Re: Integer division result
          shawkins
          shawkins Feb 24, 2017 8:53 AM (in response to m.ardito)

          Typed as integers, you will get the integer result so 0 and 11 are expected.  If you involve either a floating (double, float) or decimal (bigdecimal) type, then you will get the answer you want.  You just need to consider whether you need exact or approximate precision.  For example with a bigdecimal value:

           

          SELECT 1.0/2

           

          you will see 0.5 as the result.

            • Actions
          • 2. Re: Integer division result
            m.ardito
            m.ardito Feb 24, 2017 10:11 AM (in response to shawkins)

            Thanks,

            I had also those results, but I was puzzled because in Mysql

            select 1/2 => gets 0.5000

            and

            select 23/2 => gets 11.5000

             

            I suppose it's another of those Mysql nonstandard behaviours...

             

            But, since I usually get those integer counts from a dataset, and wish to get a percentage, how should I do

            - select cast(23 as double) /2 => gives 11.5 in Teiid

            - select parsedouble(23, '0.00') /2 => gives 11.5 in Teiid

            - other ways?

             

            Is there any difference, is there a best way?

              • Actions
            • 3. Re: Integer division result
              shawkins
              shawkins Feb 24, 2017 10:27 AM (in response to m.ardito)

              I believe the spec says that the literal must be parsed as an exact numeric type - but it doesn't imply whether that means an integral or decimal type.

               

              So for mysql they parse the literals as decimals - MySQL :: MySQL 5.7 Reference Manual :: 10.1.2 Number Literals

               

              For Teiid, Postgresql, and other databases the literals are parsed as an integer values.

                • Actions
              • 4. Re: Integer division result
                m.ardito
                m.ardito Mar 1, 2017 6:22 AM (in response to shawkins)

                Not sql realated, but I noted this on a php manual page:

                 

                "The division operator ("/") returns a float value unless the two operands are integers (or strings that get converted to integers) and the numbers are evenly divisible, in which case an integer value will be returned."

                 

                This is probably another reason because I was expecting a float result...

                  • Actions