How to cast int to boolean when doing SQL transform in Apache Beam

Question

I'm trying to do a SQL transform with Apache Beam using Calcite SQL syntax. I'm doing an int to boolean cast. My sql looks like this:

,CASE WHEN cast(IsService as BOOLEAN) THEN CASE WHEN IsEligible THEN 1 ELSE 0 END ELSE NULL END AS Reported

Where IsService is an indicator int and IsEligible is a boolean.

According to the documentation an explicit cast from int to boolean is fine. However, I get the below error when I run the pipeline:

Caused by: org.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type INTEGER to type BOOLEAN

Can anyone explain why I'm getting the error?

why don't just use `IsService = [int]` , is cast absolute needed here? — T. Peter, Jan 08 '21 at 02:54
@T.Peter you're right, it would work, but my sql is being auto generated somewhere else and changing that is a pain in the butt. Was just curious as to why i was getting the error — artofdoe, Jan 08 '21 at 03:03
Taking a look. Beam is on a pretty old version of Calcite, so that might be it. I'm going to add another answer to solve your problem, though. — Kenn Knowles, Jan 08 '21 at 18:48
@KennKnowles I'm using Beam version 1.24, but also tried with the latest 1.27 with the same result. However, it looks like it's using version 1.20 of Calcite, which is almost 2 years old.. — artofdoe, Jan 12 '21 at 18:56

score 1 · Answer 1 · answered Jan 08 '21 at 18:50

1

To solve your problem immediately, you could use IsService > 0 instead of CAST. In my opinion, it is even more clear what the conversion is. I am not a fan of relying on "falsey" values.

answered Jan 08 '21 at 18:50

Kenn Knowles

5,838
18
22

using `> 0` would solve the problem, but that requires changes in the sql which is auto generated and it's a big hassle – artofdoe Jan 12 '21 at 18:57

How to cast int to boolean when doing SQL transform in Apache Beam

1 Answers1