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:
you will see 0.5 as the result.
I had also those results, but I was puzzled because in Mysql
select 1/2 => gets 0.5000
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?
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.
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...