ksqlDB stream join table with aggregation error

Question

When i execute the below sql with error, who knows how to resolve it? Thanks a lot.

error message: class io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression cannot be cast to class io.confluent.ksql.execution.expression.tree.ComparisonExpression (io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression and io.confluent.ksql.execution.expression.tree.ComparisonExpression are in unnamed module of loader 'app')

select 
a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10) as produce_date,
    substring(substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2) as produce_hour,
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end) cause_type,
    SUM((
    case 
    when ((a.result_code =1) and is_reset = false) then sn_count
    when ((a.result_code =3) and is_reset = false) then sn_count 
    when ((a.result_code =8) and is_reset = false) then sn_count 
    when ((a.result_code =1) and is_reset = true)  then -1*sn_count 
    when (a.result_code = 2) then sn_count else 0 
    end)) stats
from
    STREAM_ORI_SACMES_INSPECTION a
inner join KSQL_TABLE_GP_MO_ORDER b on
    (a.mo_order_id = b.id and (a.result_code =1 or a.result_code =3 or a.result_code =8))
group by 
    a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10),
    substring(substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2),
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end)
    emit changes;

Answer 1

Humm... looks like a bug!

The issue is that your join criteria is invalid: (a.mo_order_id = b.id and (a.result_code =1 or a.result_code =3 or a.result_code =8)).

Looks like your join criteria should be a.mo_order_id = b.id, and the other bits should be moved to a WHERE clause.

a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10) as produce_date,
    substring(substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2) as produce_hour,
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end) cause_type,
    SUM((
    case 
    when ((a.result_code =1) and is_reset = false) then sn_count
    when ((a.result_code =3) and is_reset = false) then sn_count 
    when ((a.result_code =8) and is_reset = false) then sn_count 
    when ((a.result_code =1) and is_reset = true)  then -1*sn_count 
    when (a.result_code = 2) then sn_count else 0 
    end)) stats
from
    STREAM_ORI_SACMES_INSPECTION a
inner join KSQL_TABLE_GP_MO_ORDER b on
    a.mo_order_id = b.id
where
    a.result_code =1 or a.result_code =3 or a.result_code =8
group by 
    a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10),
    substring(substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2),
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end)
    emit changes;
join ksqldb