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 users-list-bounces+fgroot=svb.nl@monetdb.org 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 Y.Zhang@cwi.nl wrote:
On 22 Apr 2020, at 12:04, Groot, Frank (Sociale Verzekeringsbank) FGroot@svb.nl 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