Apache Drill: cast boolean to number

[Зиновьев Олег <le...@live.ru>]

Hello.

I am confused with the implicit Drill cast rules for BIT data type. According to TypeCastRules.java there is the possibility of conversion between BIT and INT (and other number type), e.g. cast(true as int). However, when trying to perform such a conversion, the error "Missing function implementation: [castBIGINT (BIT-REQUIRED)]" occurs.

As far as I understand, Drill scans the list of functions for conversion (in my case it is castINT) and tries to find the chain of transformations that is the least expensive. However, since there is no direct BIT -> INT conversion function, it selects the function with the least difference in ResolverTypePrecedence.precedenceMap and tries to add a additional cast for the argument (In my case it is [castINT (BIGINT-REQUIRED), since TypeCastRules.java also allows conversion BIT -> BIGINT). At the time of searching for the argument conversion function, the error mentioned above occurs.

questions:
1) Is it correct that BIT -> Number conversions are allowed in TypeCastRules?
2) Was the conversion supposed to be done through TinyInt? However, for this type I could not find the conversion functions to other types of numbers.
3) the inability to convert BIT to number is an error? Or is this the correct behavior?

Re: Apache Drill: cast boolean to number

[Paul Rogers <pa...@yahoo.com.INVALID>]

Hi,
I think this should also work:

IF(foo, 1, 0)

That said, the question was about the internal casting rules. As you've seen, a BIT is actually stored as a 1-bit integer internally (via the BitVector). One the one hand, it seems possible to add the needed implementations so that casting works.

On the other hand, I wonder if that would cause undesired interference with the SQL Boolean rules: maybe there is some place where the ability to treat a Bit as both an INT and a BOOLEAN causes ambiguities in the planner or in run-time type resolution code.

Still, easy enough to add the rule and rerun the unit tests to see what's what.

Thanks,
- Paul

 

    On Wednesday, February 26, 2020, 6:16:52 AM PST, Charles Givre <cg...@gmail.com> wrote:  
 
 Hi There, 
Thanks for your interest in Drill.  Can you please explain a bit more about what you're trying to do?  My intiial thought is to use a CASE statement rather than a CAST function.  IE:

SELECT ...
(CASE 
  WHEN foo = true THEN 1
  ELSE 0
END)
FROM dfs...

Best,
--C 

> On Feb 26, 2020, at 9:03 AM, Зиновьев Олег <le...@live.ru> wrote:
> 
> Hello.
> 
> I am confused with the implicit Drill cast rules for BIT data type. According to TypeCastRules.java there is the possibility of conversion between BIT and INT (and other number type), e.g. cast(true as int). However, when trying to perform such a conversion, the error "Missing function implementation: [castBIGINT (BIT-REQUIRED)]" occurs.
> 
> As far as I understand, Drill scans the list of functions for conversion (in my case it is castINT) and tries to find the chain of transformations that is the least expensive. However, since there is no direct BIT -> INT conversion function, it selects the function with the least difference in ResolverTypePrecedence.precedenceMap and tries to add a additional cast for the argument (In my case it is [castINT (BIGINT-REQUIRED), since TypeCastRules.java also allows conversion BIT -> BIGINT). At the time of searching for the argument conversion function, the error mentioned above occurs.
> 
> questions:
> 1) Is it correct that BIT -> Number conversions are allowed in TypeCastRules?
> 2) Was the conversion supposed to be done through TinyInt? However, for this type I could not find the conversion functions to other types of numbers.
> 3) the inability to convert BIT to number is an error? Or is this the correct behavior?
  

Re: Apache Drill: cast boolean to number

[Charles Givre <cg...@gmail.com>]

Hi There, 
Thanks for your interest in Drill.  Can you please explain a bit more about what you're trying to do?  My intiial thought is to use a CASE statement rather than a CAST function.  IE:

SELECT ...
(CASE 
   WHEN foo = true THEN 1
   ELSE 0
END)
FROM dfs...

Best,
--C 

> On Feb 26, 2020, at 9:03 AM, Зиновьев Олег <le...@live.ru> wrote:
> 
> Hello.
> 
> I am confused with the implicit Drill cast rules for BIT data type. According to TypeCastRules.java there is the possibility of conversion between BIT and INT (and other number type), e.g. cast(true as int). However, when trying to perform such a conversion, the error "Missing function implementation: [castBIGINT (BIT-REQUIRED)]" occurs.
> 
> As far as I understand, Drill scans the list of functions for conversion (in my case it is castINT) and tries to find the chain of transformations that is the least expensive. However, since there is no direct BIT -> INT conversion function, it selects the function with the least difference in ResolverTypePrecedence.precedenceMap and tries to add a additional cast for the argument (In my case it is [castINT (BIGINT-REQUIRED), since TypeCastRules.java also allows conversion BIT -> BIGINT). At the time of searching for the argument conversion function, the error mentioned above occurs.
> 
> questions:
> 1) Is it correct that BIT -> Number conversions are allowed in TypeCastRules?
> 2) Was the conversion supposed to be done through TinyInt? However, for this type I could not find the conversion functions to other types of numbers.
> 3) the inability to convert BIT to number is an error? Or is this the correct behavior?

Re: Apache Drill: cast boolean to number

[Зиновьев Олег <le...@live.ru>]

Hi

Thanks so much for your answ

On the other hand, I wonder if that would cause undesired interference with the SQL Boolean
rules: maybe there is some place where the ability to treat a Bit as both an INT and a BOOLEAN
causes ambiguities in the planner or in run-time type resolution code.

On the one hand, Apache Drill lacks the function of converting BIT to number. But on the other hand, TypeCastRules explicitly declares the possibility of converting BIT to a number (e.g. line 162). So I'm trying to understand - is this an error in TypeCastRules, or an error in the absence of conversion functions? Or am I missing something else?
Regarding the problem I am solving, I need these transformations, but I can’t understand which way to go:
- make jar with udfs with necessary functions
- try to do PR in Apache Drill with the addition of these functions (if TypeCastRules contains the correct information)

P.S. I had to create a letter manually, I hope it will fall into the right thread