Re:Re: Flink sql case when problem

Posted by 纳兰清风 on
URL: http://deprecated-apache-flink-user-mailing-list-archive.369.s1.nabble.com/Flink-sql-case-when-problem-tp44515p44517.html

Hi Leonard Xu，

    

    The version is 1.13. Is it a bug? I noticed that the type of column `b` is integer, but I use it as varchar.

    What the expected action should it be ?

At 2021-06-17 20:11:24, "Leonard Xu" <[hidden email]> wrote:

Hi, houying

It looks like a bug when code generate the operator code, which Flink version are you using? 

Could you help create an JIRA ticket?

Best,

Leonard

在 2021年6月17日，19:48，纳兰清风 <[hidden email]> 写道：

Hello,

    When I am using case when statement in flink sql, I got an error as follow:

org.apache.flink.table.planner.codegen.CodeGenException: Unable to find common type of GeneratedExpression(field$3,isNull$3,,INT,None) and ArrayBuffer(GeneratedExpression(((org.apache.flink.table.data.binary.BinaryStringData) str$4),false,,CHAR(0) NOT NULL,Some()), GeneratedExpression(((org.apache.flink.table.data.binary.BinaryStringData) str$5),false,,CHAR(1) NOT NULL,Some(0))).

at org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.$anonfun$generateIn$2(ScalarOperatorGens.scala:354)

at scala.Option.orElse(Option.scala:289)

at org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.generateIn(ScalarOperatorGens.scala:354)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.generateCallExpression(ExprCodeGenerator.scala:724)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:507)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:56)

at org.apache.calcite.rex.RexCall.accept(RexCall.java:174)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.$anonfun$visitCall$2(ExprCodeGenerator.scala:526)

at scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:233)

at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:58)

at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:51)

at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47)

at scala.collection.TraversableLike.map(TraversableLike.scala:233)

at scala.collection.TraversableLike.map$(TraversableLike.scala:226)

at scala.collection.AbstractTraversable.map(Traversable.scala:104)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:517)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:56)

at org.apache.calcite.rex.RexCall.accept(RexCall.java:174)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.$anonfun$visitCall$2(ExprCodeGenerator.scala:526)

at scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:233)

at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:58)

at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:51)

at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47)

at scala.collection.TraversableLike.map(TraversableLike.scala:233)

at scala.collection.TraversableLike.map$(TraversableLike.scala:226)

at scala.collection.AbstractTraversable.map(Traversable.scala:104)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:517)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:56)

at org.apache.calcite.rex.RexCall.accept(RexCall.java:174)

at org.apache.flink.table.planner.codegen.ExprCodeGenerator.generateExpression(ExprCodeGenerator.scala:155)

at org.apache.flink.table.planner.codegen.CalcCodeGenerator$.$anonfun$generateProcessCode$4(CalcCodeGenerator.scala:141)

at scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:233)

at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:58)

at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:51)

at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47)

at scala.collection.TraversableLike.map(TraversableLike.scala:233)

at scala.collection.TraversableLike.map$(TraversableLike.scala:226)

at scala.collection.AbstractTraversable.map(Traversable.scala:104)

......

My SQL is 

create table source_tb (
    a varchar,
    b INTEGER
) with (
    'connector' = 'kafka',
    ...
);

create table sink_tb (
    a varchar,
    c varchar
) with (
    'connector' = 'console',
    'format' = 'rich-json'
);

insert into sink_tb(a, c)
select a,
    case 
        when b is null or b = '' or b = '0' then '1000-01-01 00:00:00'
        else from_unixtime(cast(b as bigint))
    end as c
from source_tb;

But it works well when I change the when statement to  b is null or b in ('', '0')

Does anyone have idea about this ?