LEFT JOIN not working?

[rmoff]

Table:

ksql> DESCRIBE EXTENDED CUSTOMERS;

Type                 : TABLE
Key field            : ID
Timestamp field      : Not set - using <ROWTIME>
Key format           : STRING
Value format         : AVRO
Kafka output topic   : fullfillment.demo.customers2-smt (partitions: 1, replication: 1)

 Field      | Type
----------------------------------------
 ROWTIME    | BIGINT           (system)
 ROWKEY     | VARCHAR(STRING)  (system)
 ID         | INTEGER
 FIRST_NAME | VARCHAR(STRING)
 LAST_NAME  | VARCHAR(STRING)
 EMAIL      | VARCHAR(STRING)
 GENDER     | VARCHAR(STRING)
 COMMENTS   | VARCHAR(STRING)
----------------------------------------

Local runtime statistics
------------------------


(Statistics of the local KSQL server interaction with the Kafka topic fullfillment.demo.customers2-smt)
ksql>

Stream:

ksql> DESCRIBE EXTENDED LOGONS;

Type                 : STREAM
Key field            :
Timestamp field      : Not set - using <ROWTIME>
Key format           : STRING
Value format         : AVRO
Kafka output topic   : logons6 (partitions: 1, replication: 1)

 Field       | Type
-----------------------------------------
 ROWTIME     | BIGINT           (system)
 ROWKEY      | VARCHAR(STRING)  (system)
 LOGIN_ID    | BIGINT
 CUSTOMER_ID | INTEGER
 LOGIN_TS    | VARCHAR(STRING)
 IP          | VARCHAR(STRING)
-----------------------------------------

Local runtime statistics
------------------------


(Statistics of the local KSQL server interaction with the Kafka topic logons6)
ksql>

Each have rows for ID 151:

ksql> select * from LOGONS WHERE CUSTOMER_ID=151;
1518219370523 | %
 | 5 | 151 | 2018-02-07T17:24:41Z | 166.178.235.8
1518219370524 | %� | 240 | 151 | 2018-02-07T14:16:43Z | 164.80.215.169
1518219370559 | %� | 1241 | 151 | 2018-02-08T04:02:37Z | 176.185.37.150
1518219370563 | %� | 1706 | 151 | 2018-02-07T14:08:16Z | 132.60.88.248
1518219370580 | %�7 | 3539 | 151 | 2018-02-07T09:35:23Z | 198.82.179.166

ksql> select * FROM CUSTOMERS WHERE ID=151;
1518219977497 | "� | 151 | Bob3 | Botler | [email protected] | Female | User-friendly modular data-warehouse

The datatype matches (INTEGER), there are matching rows on each…but no values from the table are returned when attempting a LEFT JOIN:

ksql> SELECT L.LOGIN_ID,L.CUSTOMER_ID,C.FIRST_NAME FROM LOGONS L LEFT JOIN CUSTOMERS C ON L.CUSTOMER_ID=C.ID WHERE L.CUSTOMER_ID=151;
5 | 151 | null
240 | 151 | null
1241 | 151 | null
1706 | 151 | null

[rmoff]

This is probably #749 - will validate and confirm here.

[rmoff]

Confirmed, this is a dup of #749

CREATE STREAM CUSTOMERS_SOURCE WITH (KAFKA_TOPIC='fullfillment.demo.customers2-smt', VALUE_FORMAT='AVRO');
CREATE STREAM CUSTOMERS_REKEYED AS SELECT * FROM CUSTOMERS_SOURCE PARTITION BY ID;
CREATE TABLE CUSTOMERS WITH (KAFKA_TOPIC='CUSTOMERS_REKEYED', VALUE_FORMAT='AVRO', KEY='ID');
SELECT ROWKEY,ID FROM CUSTOMERS LIMIT 5;

ksql> SELECT LOGIN_TS,CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL FROM LOGONS L LEFT OUTER JOIN CUSTOMERS C ON L.CUSTOMER_ID = C.ID WHERE CUSTOMER_ID=151;
2018-02-07T17:24:41Z | 151 | Bob3 | Botler | [email protected]
2018-02-07T14:16:43Z | 151 | Bob3 | Botler | [email protected]
2018-02-08T04:02:37Z | 151 | Bob3 | Botler | [email protected]
2018-02-07T14:08:16Z | 151 | Bob3 | Botler | [email protected]
2018-02-07T09:35:23Z | 151 | Bob3 | Botler | [email protected]