RE: can I prevent rounding of a/b when a and b are integers
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 mmcallis...@homeaway.com<mailto:mmcallis...@homeaway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<mailto:zimmk...@hotmail.com> | 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 [mailto:noam.bul...@teoco.com] Sent: Thursday, September 22, 2016 5:25 AM To: user@phoenix.apache.org 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 then 1 else 0 end) / count(*) will get wrong results unless you will start implementing workarounds From: John Hancock [mailto:jhancock1...@gmail.com] Sent: Thursday, September 22, 2016 12:56 PM To: user@phoenix.apache.org<mailto:user@phoenix.apache.org> 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) mailto:james.heat...@elsevier.com>> wrote: On Thu, 2016-09-22 at 05:39 +, 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. PRIVILEGED AND CONFIDENTIAL 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.
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 then 1 else 0 end) / count(*) will get wrong results unless you will start implementing workarounds From: John Hancock [mailto:jhancock1...@gmail.com] Sent: Thursday, September 22, 2016 12:56 PM To: user@phoenix.apache.org 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) mailto:james.heat...@elsevier.com>> wrote: On Thu, 2016-09-22 at 05:39 +, 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. PRIVILEGED AND CONFIDENTIAL 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.
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) < james.heat...@elsevier.com> wrote: > > > On Thu, 2016-09-22 at 05:39 +, 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. >
Re: can I prevent rounding of a/b when a and b are integers
On Thu, 2016-09-22 at 05:39 +, 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.
RE: can I prevent rounding of a/b when a and b are integers
Thanks for the workaround , but it is not a solution for our case. We have an app that let user write their own SQL and we can’t tell them to always write int numbers as xxx.0 I created https://issues.apache.org/jira/browse/PHOENIX-3312 Regards, Noam From: James Taylor [mailto:jamestay...@apache.org] Sent: Wednesday, September 21, 2016 6:03 PM To: user Subject: Re: can I prevent rounding of a/b when a and b are integers Hi Noam, Please file a JIRA. As a workaround, you can do SELECT 1.0/3. Thanks, James On Wed, Sep 21, 2016 at 12:48 AM, Bulvik, Noam mailto:noam.bul...@teoco.com>> wrote: Hi, When I do something like select 1/3 from the result will be integer value (0) and not double or alike(0.33….). Is there some configuration that can force the result to be double BTW – when executing the same query in oracle (select 1/3 from dual ) I get correct result same in impala Noam Bulvik PRIVILEGED AND CONFIDENTIAL 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. PRIVILEGED AND CONFIDENTIAL 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.
Re: can I prevent rounding of a/b when a and b are integers
Adding some more workaround , if you are working on column:- select cast(col_int1 as decimal)/col_int2; select col_int1*1.0/3; On Wed, Sep 21, 2016 at 8:33 PM, James Taylor wrote: > Hi Noam, > Please file a JIRA. As a workaround, you can do SELECT 1.0/3. > Thanks, > James > > On Wed, Sep 21, 2016 at 12:48 AM, Bulvik, Noam > wrote: > >> Hi, >> >> >> >> When I do something like select 1/3 from the result will be >> integer value (0) and not double or alike(0.33….). Is there some >> configuration that can force the result to be double >> >> >> >> BTW – when executing the same query in oracle (select 1/3 from dual ) I >> get correct result same in impala >> >> >> >> *Noam Bulvik* >> >> >> >> -- >> >> PRIVILEGED AND CONFIDENTIAL >> 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. >> > >
Re: can I prevent rounding of a/b when a and b are integers
Hi Noam, Please file a JIRA. As a workaround, you can do SELECT 1.0/3. Thanks, James On Wed, Sep 21, 2016 at 12:48 AM, Bulvik, Noam wrote: > Hi, > > > > When I do something like select 1/3 from the result will be > integer value (0) and not double or alike(0.33….). Is there some > configuration that can force the result to be double > > > > BTW – when executing the same query in oracle (select 1/3 from dual ) I > get correct result same in impala > > > > *Noam Bulvik* > > > > -- > > PRIVILEGED AND CONFIDENTIAL > 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. >
can I prevent rounding of a/b when a and b are integers
Hi, When I do something like select 1/3 from the result will be integer value (0) and not double or alike(0.33). Is there some configuration that can force the result to be double BTW - when executing the same query in oracle (select 1/3 from dual ) I get correct result same in impala Noam Bulvik PRIVILEGED AND CONFIDENTIAL 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.
