


RE: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow
Yash Sharma 20130921, 15:43
Am picking from Hive and Pig function list. Sent from my Sony Xperia™ smartphone "Ted Dunning (JIRA)" <[EMAIL PROTECTED]> wrote: [ https://issues.apache.org/jira/browse/DRILL247?page=com.atlassian.jira.plugin.system.issuetabpanels:commenttabpanel&focusedCommentId=13773839#comment13773839 ] Ted Dunning commented on DRILL247:  yash, What reference are you using for picking functions and names? It might be nice to find names common to, say, Oracle, Postgres and Sequel Server. > New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow >  > > Key: DRILL247 > URL: https://issues.apache.org/jira/browse/DRILL247> Project: Apache Drill > Issue Type: Improvement > Reporter: Yash Sharma > > Improving the math functions collection with more functions: > log(a): the natural logarithm (base e) of an expression > log10(a): base 10 logarithm > sqrt(a): square root > cbrt(a): cube root > rand(): returns random number from 01. > round(a) > exp(a): exponential e^a > e(): returns value of e > pi(): returns value of pi > pow(a, p): returns a^p  This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira________________________________ NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.

Re: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow
Julian Hyde 20130923, 16:42
I'm worried about "e". In SQL, functions with zero parameters are called without parentheses. Thus a query like select e.empid from emps as e might be ambiguous. Anyone who has a real need to use e in their queries will also know that they can write exp(1.0). I'd start from the SQL standard and the longer established databases (especially Oracle and Postgres) rather than Pig and Hive. Less chance of  ahem  innovative specifications that are incompatible with people's expectations. There is a long list of operator tests in Optiq's SqlOperatorBaseTest.java [ https://github.com/julianhyde/optiq/blob/master/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java ]. They check parsing, validation, return type derivation, and null handling, as well as that the functions give the right answer. The operators are drawn mainly from the SQL standard, but there are some from Oracle. It's worth subclassing that test and calling from Drill. Julian On Sep 21, 2013, at 8:43 AM, Yash Sharma <[EMAIL PROTECTED]> wrote: > Am picking from Hive and Pig function list. > > Sent from my Sony Xperia™ smartphone > > "Ted Dunning (JIRA)" <[EMAIL PROTECTED]> wrote: > > > [ https://issues.apache.org/jira/browse/DRILL247?page=com.atlassian.jira.plugin.system.issuetabpanels:commenttabpanel&focusedCommentId=13773839#comment13773839 ] > > Ted Dunning commented on DRILL247: >  > > yash, > > What reference are you using for picking functions and names? > > It might be nice to find names common to, say, Oracle, Postgres and Sequel Server. > >> New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow >>  >> >> Key: DRILL247 >> URL: https://issues.apache.org/jira/browse/DRILL247>> Project: Apache Drill >> Issue Type: Improvement >> Reporter: Yash Sharma >> >> Improving the math functions collection with more functions: >> log(a): the natural logarithm (base e) of an expression >> log10(a): base 10 logarithm >> sqrt(a): square root >> cbrt(a): cube root >> rand(): returns random number from 01. >> round(a) >> exp(a): exponential e^a >> e(): returns value of e >> pi(): returns value of pi >> pow(a, p): returns a^p > >  > This message is automatically generated by JIRA. > If you think it was sent incorrectly, please contact your JIRA administrators > For more information on JIRA, see: http://www.atlassian.com/software/jira> > ________________________________ > > > > > > > NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.

RE: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow
Yash Sharma 20130923, 17:21
Thanks Julian, I had not seen into these intricacies :) . I would let the question open for seniors to handle. I was planning the queries to use e as a function rather that 'e' directly. That is how its used in Hive. Something like: SELECT e() FROM "sampledata/regions.parquet"; Other functions without params are pi() & rand() would be used in same fashion. Open for comments/suggestions. Thanks, Yash ________________________________________ From: Julian Hyde [[EMAIL PROTECTED]] Sent: Monday, September 23, 2013 10:12 PM To: [EMAIL PROTECTED] Subject: Re: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow I'm worried about "e". In SQL, functions with zero parameters are called without parentheses. Thus a query like select e.empid from emps as e might be ambiguous. Anyone who has a real need to use e in their queries will also know that they can write exp(1.0). I'd start from the SQL standard and the longer established databases (especially Oracle and Postgres) rather than Pig and Hive. Less chance of  ahem  innovative specifications that are incompatible with people's expectations. There is a long list of operator tests in Optiq's SqlOperatorBaseTest.java [ https://github.com/julianhyde/optiq/blob/master/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java ]. They check parsing, validation, return type derivation, and null handling, as well as that the functions give the right answer. The operators are drawn mainly from the SQL standard, but there are some from Oracle. It's worth subclassing that test and calling from Drill. Julian On Sep 21, 2013, at 8:43 AM, Yash Sharma <[EMAIL PROTECTED]> wrote: > Am picking from Hive and Pig function list. > > Sent from my Sony Xperia™ smartphone > > "Ted Dunning (JIRA)" <[EMAIL PROTECTED]> wrote: > > > [ https://issues.apache.org/jira/browse/DRILL247?page=com.atlassian.jira.plugin.system.issuetabpanels:commenttabpanel&focusedCommentId=13773839#comment13773839 ] > > Ted Dunning commented on DRILL247: >  > > yash, > > What reference are you using for picking functions and names? > > It might be nice to find names common to, say, Oracle, Postgres and Sequel Server. > >> New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow >>  >> >> Key: DRILL247 >> URL: https://issues.apache.org/jira/browse/DRILL247>> Project: Apache Drill >> Issue Type: Improvement >> Reporter: Yash Sharma >> >> Improving the math functions collection with more functions: >> log(a): the natural logarithm (base e) of an expression >> log10(a): base 10 logarithm >> sqrt(a): square root >> cbrt(a): cube root >> rand(): returns random number from 01. >> round(a) >> exp(a): exponential e^a >> e(): returns value of e >> pi(): returns value of pi >> pow(a, p): returns a^p > >  > This message is automatically generated by JIRA. > If you think it was sent incorrectly, please contact your JIRA administrators > For more information on JIRA, see: http://www.atlassian.com/software/jira> > ________________________________ > > > > > > > NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference. ________________________________ NOTE: This message may contain information that is confidential, proprietary, privileged or otherwise protected by law. The message is intended solely for the named addressee. If received in error, please destroy and notify the sender. Any use of this email is prohibited when received in error. Impetus does not represent, warrant and/or guarantee, that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.

Re: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow
Ted Dunning 20130924, 03:04
Is e any worse than any function? Really? Couldn't exp be just as bad (EXPense)? Or even something like cos (Cost Of String)? On Mon, Sep 23, 2013 at 9:42 AM, Julian Hyde <[EMAIL PROTECTED]> wrote: > I'm worried about "e". In SQL, functions with zero parameters are called > without parentheses. Thus a query like > > select e.empid from emps as e > > might be ambiguous. Anyone who has a real need to use e in their queries > will also know that they can write exp(1.0). > > I'd start from the SQL standard and the longer established databases > (especially Oracle and Postgres) rather than Pig and Hive. Less chance of  > ahem  innovative specifications that are incompatible with people's > expectations. > > There is a long list of operator tests in Optiq's SqlOperatorBaseTest.java > [ > https://github.com/julianhyde/optiq/blob/master/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java]. They check parsing, validation, return type derivation, and null > handling, as well as that the functions give the right answer. The > operators are drawn mainly from the SQL standard, but there are some from > Oracle. It's worth subclassing that test and calling from Drill. > > Julian > > On Sep 21, 2013, at 8:43 AM, Yash Sharma <[EMAIL PROTECTED]> > wrote: > > > Am picking from Hive and Pig function list. > > > > Sent from my Sony Xperia™ smartphone > > > > "Ted Dunning (JIRA)" <[EMAIL PROTECTED]> wrote: > > > > > > [ > https://issues.apache.org/jira/browse/DRILL247?page=com.atlassian.jira.plugin.system.issuetabpanels:commenttabpanel&focusedCommentId=13773839#comment13773839]> > > > Ted Dunning commented on DRILL247: > >  > > > > yash, > > > > What reference are you using for picking functions and names? > > > > It might be nice to find names common to, say, Oracle, Postgres and > Sequel Server. > > > >> New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow > >>  > >> > >> Key: DRILL247 > >> URL: https://issues.apache.org/jira/browse/DRILL247> >> Project: Apache Drill > >> Issue Type: Improvement > >> Reporter: Yash Sharma > >> > >> Improving the math functions collection with more functions: > >> log(a): the natural logarithm (base e) of an expression > >> log10(a): base 10 logarithm > >> sqrt(a): square root > >> cbrt(a): cube root > >> rand(): returns random number from 01. > >> round(a) > >> exp(a): exponential e^a > >> e(): returns value of e > >> pi(): returns value of pi > >> pow(a, p): returns a^p > > > >  > > This message is automatically generated by JIRA. > > If you think it was sent incorrectly, please contact your JIRA > administrators > > For more information on JIRA, see: > http://www.atlassian.com/software/jira> > > > ________________________________ > > > > > > > > > > > > > > NOTE: This message may contain information that is confidential, > proprietary, privileged or otherwise protected by law. The message is > intended solely for the named addressee. If received in error, please > destroy and notify the sender. Any use of this email is prohibited when > received in error. Impetus does not represent, warrant and/or guarantee, > that the integrity of this communication has been maintained nor that the > communication is free of errors, virus, interception or interference. > >

Re: [jira] [Commented] (DRILL247) New Math Functions: log, sqrt, cbrt, rand, round, exp, e, pi, pow
Julian Hyde 20130924, 04:28
On Sep 23, 2013, at 8:04 PM, Ted Dunning <[EMAIL PROTECTED]> wrote:
> Is e any worse than any function? Really? 1. "e" is short, so greater chance of overlap with user identifiers, which also tend to be short.
2. In standard SQL, functions with no parameters do not take arguments. So their namespaces overlap with the column and table namespaces. (MySQL and Postgres do not adhere to the standard in this regard. If we want to depart from the standard, let's do it with our eyes open.)
Careful what functions we put into Drill. Once they are in, we'll never be able to take them out. (Ask the MySQL and Hive folks what pain they went through to clean up their function set once they decided to become SQL compliant.)
I recommend that we just put in functions that are in the standard or are in at least 2 major databases. And provide a facility for userdefined functions so that people can do what they need without cluttering up the namespace for perpetuity.
"e" doesn't pass this test. It is not in the standard, is not in Oracle, DB2, MySQL, or Postgres. And there is an easy workaround: exp(1).
By the way, the standard has "ln" and "log10" but no "log". I suggest we follow suit. (Oracle has "log(n, base)", which is different. MySQL has "ln" and "log", and they both do the same thing. Hmm  I wonder how that happened?)
Similar comments regarding "pow". The standard has "power". MySQL has both "pow" and "power". LucidDB (upon which Optiq is based) had "pow" and "power", and we bit the bullet and removed "pow".
> Couldn't exp be just as bad (EXPense)? Or even something like cos (Cost Of > String)? There's always a chance of overlap. But you minimize it by sticking to the same set of builtins, keywords and reserved words as the other databases.
Julian

