4 Replies Latest reply on Mar 1, 2017 6:22 AM by Marco Ardito

# Integer division result

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

• ###### 1. Re: Integer division result

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.

• ###### 2. Re: Integer division result

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?

• ###### 3. Re: Integer division result

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.

• ###### 4. Re: Integer division result

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...