phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael McAllister <>
Subject RE: can I prevent rounding of a/b when a and b are integers
Date Thu, 22 Sep 2016 15:42:34 GMT
Actually, in Oracle, at least the last time I was working on it, if you divided an int column
by an int column, you got an int result which, if I remember correctly, was truncated.

For people who write SQL, this is a fairly well known issue that we confront, and solve using
the solution proposed below. Either by multiplying one of the operands by 1.0, or casting
one of them as a real number.

Additionally, don’t lose sight of the warning James Heather raised in one of the earlier
responses. Make sure you restrict what your users do. Having a table dropped could end up
being very expensive.

Michael McAllister
Staff Data Warehouse Engineer | Decision Systems<> | C: 512.423.7447 | skype:
michael.mcallister.ha<> | webex: https://h.a/mikewebex
[Description: Description: cid:3410354473_30269081]
This electronic communication (including any attachment) is confidential.  If you are not
an intended recipient of this communication, please be advised that any disclosure, dissemination,
distribution, copying or other use of this communication or any attachment is strictly prohibited.
 If you have received this communication in error, please notify the sender immediately by
reply e-mail and promptly destroy all electronic and printed copies of this communication
and any attachment.

From: Bulvik, Noam []
Sent: Thursday, September 22, 2016 5:25 AM
Subject: RE: can I prevent rounding of a/b when a and b are integers

I checked both oracle and impala and in both 1/3 is 0.333 and not 0.
I think that SQL writer is not a programmer and he  does not care about data type. He just
want to get the correct results. BTW – it is not only constant, even expression like sum(case
when <condition > then 1 else 0 end) / count(*) will get wrong results unless you will
start implementing workarounds

From: John Hancock []
Sent: Thursday, September 22, 2016 12:56 PM
Subject: Re: can I prevent rounding of a/b when a and b are integers

I think it is fairly standard in programming languages for / to mean integer division when
the operands used are integers.  Therefore 1/3=0 is not a surprising result to me; other programming
languages I have worked in give 1/3=0.  However if one of the operands to / is a decimal,
the result is also decimal, so 1.0/3 = 0.333... I don't think the behavior of / is incorrect
as is.

On Thu, Sep 22, 2016 at 4:45 AM, Heather, James (ELS-LON) <<>>

On Thu, 2016-09-22 at 05:39 +0000, Bulvik, Noam wrote:

We have an app that let user write their own SQL

Um, do they write DROP TABLE statements in there?


Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, Oxford, OX5
1GB, United Kingdom, Registration No. 1982084, Registered in England and Wales.


PLEASE NOTE: The information contained in this message is privileged and confidential, and
is intended only for the use of the individual to whom it is addressed and others who have
been specifically authorized to receive it. If you are not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, or if any problems occur with
transmission, please contact sender. Thank you.
View raw message