1

I am trying to join a table and a stream and create another table as shown below:

CREATE TABLE table_fx_latest AS
   SELECT t1.currencyid,
          t1.maxtimestamp,
          t2.midprice 
  FROM stream_fx2 t2 LEFT JOIN table_fx_latest3 t1 
  ON t1.currencyid = t2.currencyid AND 
     t1.timestamp = t2.maxtimestamp 
  GROUP BY t1.currencyid, 
           t1.maxtimestamp, 
           t2.midprice;

but the following error is reported: 

Cannot RUN execution plan for this statement, CreateTableAsSelect{name=TABLE_FX_LATEST_PRICE6, query=Query{queryBody=QuerySpecification{select=Select{distinct=false, selectItems=[T1.CURRENCYID T1_CURRENCYID, T1.MAXTIMESTAMP MAXTIMESTAMP, T2.MIDPRICE MIDPRICE]}, from=Join{type=LEFT, left=AliasedRelation{relation=STREAM_FX2, alias=T2}, right=AliasedRelation{relation=TABLE_FX_LATEST3, alias=T1}, criteria=Optional[JoinOn{((T1.CURRENCYID = T2.CURRENCYID) AND (T2.TIMESTAMP = T1.MAXTIMESTAMP))}]}, =null, where=null, groupBy=Optional[GroupBy{isDistinct=false, groupingElements=[SimpleGroupBy{columns=[T1.CURRENCYID]}, SimpleGroupBy{columns=[T1.MAXTIMESTAMP]}, SimpleGroupBy{columns=[T2.MIDPRICE]}]}], having=null, orderBy=[], limit=null}, orderBy=[]}, notExists=false, properties={}}
Caused by: io.confluent.ksql.parser.tree.LogicalBinaryExpression cannot be cast to io.confluent.ksql.parser.tree.ComparisonExpression

And here's the description for both stream_fx2 stream and table_fx_latest3 table: 

ksql> describe stream_fx2;

Field      | Type
----------------------------------------
ROWTIME    | BIGINT           (system)
ROWKEY     | VARCHAR(STRING)  (system)
ID         | INTEGER
CURRENCY   | VARCHAR(STRING)
CURRENCYID | INTEGER
TIMESTAMP  | BIGINT
BIDPRICE   | DOUBLE
MIDPRICE   | DOUBLE
OFFERPRICE | DOUBLE

ksql> describe table_fx_latest3;

Field        | Type
------------------------------------------
ROWTIME      | BIGINT           (system)
ROWKEY       | VARCHAR(STRING)  (system)
CURRENCYID   | INTEGER          (key)
MAXTIMESTAMP | BIGINT
------------------------------------------

I guess that this might be a bug of KSQL (still in developer preview) but I wanted to make sure that I am not missing anything. Any help would be much appreciated.

Matthias J. Sax
  • 59,682
  • 7
  • 117
  • 137
Giorgos Myrianthous
  • 36,235
  • 20
  • 134
  • 156

1 Answers1

5

The JOIN criteria should only be key equality. If you have any other criteria you need to put them in the WHERE clause. Try the following:

CREATE TABLE table_fx_latest AS
  SELECT t1.currencyid,
         t1.maxtimestamp,
         t2.midprice 
  FROM stream_fx2 t2 LEFT JOIN table_fx_latest3 t1 
    ON t1.currencyid = t2.currencyid
  WHERE 
    t1.timestamp = t2.maxtimestamp 
  GROUP BY t1.currencyid, 
           t1.maxtimestamp, 
           t2.midprice;

Let me know if this works for you.

Hojjat
  • 684
  • 4
  • 4
  • The table is created successfully but I get an Exception: `Error Serializing Avro message` which is `Caused by: java.lang.ClassCastException`. – Giorgos Myrianthous Feb 13 '18 at 12:42
  • Can you check to make sure you define the schema for the source stream and table correctly. Seems that one of the fields is not defined with the right type. – Hojjat Feb 13 '18 at 21:58
  • I have been using a field which was `float` so I've created some new tables and streams with `double` instead. But some new issues appear. Could you please visit https://stackoverflow.com/questions/48785805/null-rowkey-in-a-stream and shed some light? – Giorgos Myrianthous Feb 14 '18 at 11:14