Re: Integer divided by integer never produces a real number

On 22/04/2020 12.51, Ying Zhang wrote:

...

On 22 Apr 2020, at 12:33, Ying Zhang wrote:

...

On 22 Apr 2020, at 12:04, Groot, Frank (Sociale Verzekeringsbank) wrote:

Hi,

Can you tell me which of the following division outcomes is expected behaviour?

--Division -- outcome select 10/3, -- 3

Hai Frank,

This is expected.

...

10/3.0, -- 3.333 10.0/3, -- 3.333 cast(10 as decimal(18,2))/3, -- 3.333 cast(10 as decimal(18,0))/3, -- 3

Not sure about this. I’ll ask around.

Hai Frank,

The SQL standard says it’s implementation dependent.

For “select cast(10 as decimal(18,0))/3;”, MonetDB and SQLite return 3, while Postgres and MySQL return 3.333

For “select 10/3”, MonetDB and Postgres return 3, while MySQL returns 3.333

Further, in python2 3/9 => 0, in python3 3/9 => 0.333333

Conclusion: use proper cast to get the result you want.

Hope this helps.

Jennie

...

Jennie

...

cast(10 as decimal(18,0))/3.0 -- 3.333 ;

Personally, I would have expected all of the outcomes to be real numbers and not integers. Can you tell me if that is a realistic expectation?

Kind regards,

Frank Groot

None of the results are real numbers. They are all either an integer of some sort (here TINYINT) or DECIMAL (i.e. fixed point). -- Sjoerd Mullender