Hi Dave,

Charles and I added the sqlTypeOf() function while writing the book so we could 
clearly explain types. Drill also has a SQL-standard function, typeOf(), but 
this tends to report "NULL" if the value is null, regardless of type, which 
made it hard to explain the behavior of nullable types (which are the kind most 
often used in Drill...)

The other way to check this is to find the template used to generate the math 
functions. See [1]. This template is fed by a table in [2]. Turns out that 
table will answer your original question:


      {className: "Divide", funcName: "divide", op: "/", types: [
          {input1: "Int", input2: "Int", outputType: "Int", castType: "int"},

That is, for a (INT, INT) input to divide, the output is also INT.

To answer your other question: I believe Drill does type promotion, but I don't 
know the details. For example, is INT + INT an INT or a BIGINT? Is a SHORT + 
INT an INT or a BIGINT? The info is not in the table referenced above, but is 
probably available in the template or surrounding files.

Thanks,
- Paul

[1] 
https://github.com/apache/drill/blob/master/exec/java-exec/src/main/codegen/templates/MathFunctions.java
[2] 
https://github.com/apache/drill/blob/master/exec/java-exec/src/main/codegen/data/MathFunctionTypes.tdd



    On Friday, August 16, 2019, 07:09:58 AM PDT, Dave Challis 
<dave.chal...@cipher.ai> wrote:  
 
 Thanks Paul, I hadn't seen sqlTypeOf before, that looks perfect for
checking this sort of thing.

Dave

On Thu, 15 Aug 2019 at 18:04, Paul Rogers <par0...@yahoo.com.invalid> wrote:

> Hi Dave,
>
> As it turns out, improving the detail in function documentation is a
> long-standing request. The historical answer has been to either 1) read the
> code, or 2) try it with a test query.
>
> You can use the sqlTypeOf() function to learn the answer to your question:
>
> SELECT sqlTypeOf(cast(1 AS INT) / cast(2 AS INT)) FROM values(1)
>
> Charles Givre patiently tracked down and documented all the Drill
> functions in his appendix to our book "Learning Apache Drill." But, even
> there, the level of detail you request is missing.
>
> Maybe, once you do the research to find the answers you want, you could
> submit a Documentation JIRA ticket with the results so that it can be added
> to the documentation.
>
> Thanks,
> - Paul
>
>
>
>    On Thursday, August 15, 2019, 03:55:11 AM PDT, Dave Challis <
> dave.chal...@cipher.ai> wrote:
>
>  Is there any documentation out there on how mathematical functions are
> handled when operating on different types?
>
> E.g.:
>
> * would integer division of 1 / 2 produce a float or double of 0.5? Or an
> integer of the same type set to 0?
>
> * if two INT are multipled and produce a result larger than INT can
> support, is the result returned as a BIGINT?
>
  

Reply via email to