Integer divided by integer never produces a real number
Hi,
Can you tell me which of the following division outcomes is expected behaviour?
--Division -- outcome select 10/3, -- 3 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 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
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.
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
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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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).
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
participants (3)
-
Groot, Frank (Sociale Verzekeringsbank) -
Sjoerd Mullender -
Ying Zhang