2430002 - Left Join between two windows - SAP ESP

• The ESP project is using a LEFT JOIN on two windows.
• The project is using a window as the target of the LEFT JOIN.
• The expectation is that all the records from the left side (outer side) of the join will be produced in the target window. However, not all records are produced in the target window.
• How to understand this behavior?
   
• Configuration Details:
• The project is using a Database Input Adapter to obtain data from a HANA table with a polling period of 15 seconds.
• The data from the adapter is received by an Input Stream (INPUT_STREAM1).
• The Input Stream INPUT_STREAM1 is connected to a Derived Window (WINDOW1).
• WINDOW1 has a retention policy of KEEP EVERY 15 seconds.
• Using a LEFT JOIN, where the left side of the join is a Master Data set (MASTER_WIN), and the right side of the join is WINDOW1:
• Fetch the Master Data from HANA using a Database Input Adapter to fetch the data once (by not specifying a poll period).
• The data from the adapter is received by an Input Stream (INPUT_MASTERDATA_STREAM).
• The Input Stream INPUT_MASTERDATA_STREAM is connected to a Derived Window (MASTER_WIN) to keep forever (by not specifying a retention policy).
• The LEFT JOIN (WINDOW3) has a retention policy of KEEP EVERY 15 seconds.
• When the master data window MASTER_WIN consists of 1072 rows, the expectation is that the join output will also have 1072 rows every time.
  
  
• Observed Behavior:
• On the first polling for WINDOW1, there were 900 records.
• The master window (MASTER_WIN) consisted of 1072 rows.
• Then the output window (WINDOW3) had 1072 rows, with NULL values for 172 (1072-900) rows, as expected.
• On the second polling for WINDOW1, there were only 500 records.
• The expectation was for the output window (WINDOW3) to still see 1072 rows, with NULL values for 572 (1072-500) rows.
• Instead, there were only 900 records in WINDOW3.
   
• CCL Code:

Create Schema S1_SCHEMA (GUID STRING, CODE STRING, RH_DATE SECONDDATE);
Create Input Stream S1 Schema S1_SCHEMA ;

Attach Input Adapter Generic_DB_Input1 type db_in to S1
Properties
  service = 'hana_svc',
  query =  'select *  from \"HS\".\"TABLE1\" where RH_DATE > ?ESP_TIMESTAMP_VALUE',
  pollperiod = 15,
  msdateSequenceColumn = 'RH_DATE' ,
  msdateSequenceInitValue = '2017-02-15 21:00:20.000',
  maxReconnectAttempts = -1,
  reconnectAttemptDelayMSec = 5000 ;
 
CREATE OUTPUT WINDOW WIN1 PRIMARY KEY DEDUCED
KEEP EVERY 15 SECONDS
AS SELECT S1.CODE as CODE, (S1.RH_DATE + (- 5 HOURS 30 MIn)) as RH_DATE
FROM S1
GROUP BY S1.CODE ;

CREATE SCHEMA MASTER_SCHEMA (MSTR_CODE STRING, MSTR_S1 STRING, MSTR_S2 STRING);
CREATE INPUT STREAM MASTER_IN SCHEMA MASTER_SCHEMA;

ATTACH INPUT ADAPTER Generic_DB_Input2 type db_in TO MASTER_IN
PROPERTIES
  service = 'hana_svc2' ,
  table = '\"HS\".\"TABLE2\"';
  
CREATE OUTPUT  WINDOW MASTER_WIN PRIMARY KEY DEDUCED
KEEP ALL
AS SELECT
  MASTER_IN.MSTR_CODE as MSTR_CODE,
  MASTER_IN.MSTR_S1 as MSTR_S1,
  MASTER_IN.MSTR_S2  as MSTR_S2
from MASTER_IN
GROUP BY MASTER_IN.MSTR_CODE ;

//LEFT JOIN:
CREATE OUTPUT WINDOW WIN3 PRIMARY KEY DEDUCED
KEEP EVERY 15 SECONDS
AS SELECT MASTER_WIN.* , WIN1.*
FROM MASTER_WIN  left join WIN1
on MASTER_WIN.MSTR_CODE = WIN1.CODE ;

SAP Event Stream Processor (ESP) 5.1

streaming, processings, 2 windows , KBA , BC-SYB-ESP , Sybase ESP (Event Stream Processor) , How To