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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Thanks all, We are in an environment with many different database technologies and our analists sometimes try the same query's on different platforms. What's in the SQL standards was therefore also in the back of our minds. ;-) Solid answer on what can be expected, thanks! Frank -----Oorspronkelijk bericht----- Van: users-list Namens Sjoerd Mullender Verzonden: woensdag 22 april 2020 13:33 Aan: users-list@monetdb.org Onderwerp: 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 _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list