{"action":"created","issue":{"url":"https://api.github.com/repos/confluentinc/ksql/issues/1609","repository_url":"https://api.github.com/repos/confluentinc/ksql","labels_url":"https://api.github.com/repos/confluentinc/ksql/issues/1609/labels{/name}","comments_url":"https://api.github.com/repos/confluentinc/ksql/issues/1609/comments","events_url":"https://api.github.com/repos/confluentinc/ksql/issues/1609/events","html_url":"https://github.com/confluentinc/ksql/issues/1609","id":343126697,"node_id":"MDU6SXNzdWUzNDMxMjY2OTc=","number":1609,"title":"Steam-table Left join partially (and silently) fails to populate values","user":{"login":"OferLahav","id":6720729,"node_id":"MDQ6VXNlcjY3MjA3Mjk=","avatar_url":"https://avatars3.githubusercontent.com/u/6720729?v=4","gravatar_id":"","url":"https://api.github.com/users/OferLahav","html_url":"https://github.com/OferLahav","followers_url":"https://api.github.com/users/OferLahav/followers","following_url":"https://api.github.com/users/OferLahav/following{/other_user}","gists_url":"https://api.github.com/users/OferLahav/gists{/gist_id}","starred_url":"https://api.github.com/users/OferLahav/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/OferLahav/subscriptions","organizations_url":"https://api.github.com/users/OferLahav/orgs","repos_url":"https://api.github.com/users/OferLahav/repos","events_url":"https://api.github.com/users/OferLahav/events{/privacy}","received_events_url":"https://api.github.com/users/OferLahav/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-07-20T14:22:21Z","updated_at":"2018-07-25T14:06:28Z","closed_at":null,"author_association":"NONE","body":"I am joining a stream with a table. The result of the join is only partially successful. Some of the values are accurately populated while others are null. I checked to make sure that the values exist in both the table and stream, and that the column used to join on is the key for both.\r\n\r\nI'm using confluent to load the topics that are reading from tables in MSSQL using the jdbc.\r\n\r\nIm then creating the stream and table from the corresponding topics using KSQL, and the data is all in JSON format.\r\n\r\nThe time stamps on the missing data in the table is older than that of the stream.\r\n\r\nAny suggestions?\r\n\r\ncreate stream casecode_contract_stream as select ct.projectid, ct.casecode, cs.isTrue from contract_stream cs left join casecode_table ct on cs.projectid = ct.projectid;    \r\n\r\nselect * from casecode_contract_stream limit 1;\r\n1532034321292 | 706083 | null | null | true\r\n\r\nselect * from casecode_contract_stream where casecode is not null limit 1;\r\n1532034321292 | 705147 | 705147 | data1 | true\r\n\r\nselect * from casecode_table where projectid = 705147;\r\n1532033878462 | 705147 | 705147 | data1 \r\n\r\nselect * from casecode_table where projectid = 706083;\r\n1532033878463 | 706083 | 706083 | data2\r\n\r\nselect * from contract_stream where projectid = 705147;\r\n1532034321292 | 705147 | 705147 | true\r\n\r\nselect * from contract_stream where projectid = 706083;\r\n1532034321292 | 706083 | 706083 | true"},"comment":{"url":"https://api.github.com/repos/confluentinc/ksql/issues/comments/407766162","html_url":"https://github.com/confluentinc/ksql/issues/1609#issuecomment-407766162","issue_url":"https://api.github.com/repos/confluentinc/ksql/issues/1609","id":407766162,"node_id":"MDEyOklzc3VlQ29tbWVudDQwNzc2NjE2Mg==","user":{"login":"OferLahav","id":6720729,"node_id":"MDQ6VXNlcjY3MjA3Mjk=","avatar_url":"https://avatars3.githubusercontent.com/u/6720729?v=4","gravatar_id":"","url":"https://api.github.com/users/OferLahav","html_url":"https://github.com/OferLahav","followers_url":"https://api.github.com/users/OferLahav/followers","following_url":"https://api.github.com/users/OferLahav/following{/other_user}","gists_url":"https://api.github.com/users/OferLahav/gists{/gist_id}","starred_url":"https://api.github.com/users/OferLahav/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/OferLahav/subscriptions","organizations_url":"https://api.github.com/users/OferLahav/orgs","repos_url":"https://api.github.com/users/OferLahav/repos","events_url":"https://api.github.com/users/OferLahav/events{/privacy}","received_events_url":"https://api.github.com/users/OferLahav/received_events","type":"User","site_admin":false},"created_at":"2018-07-25T14:06:28Z","updated_at":"2018-07-25T14:06:28Z","author_association":"NONE","body":"We took a different stream for a different topic that contains the 'projectid' column and joined it on the same table as it mentioned above. The results of which includes the data that the join above was unable to join on. It appears as though this is an issue with the non determinism that you mention.\r\n\r\nAt this time, we've given up on using KSQL for these transformations.  We're moving forward by having the JDBC connector ingest from a view in SQL that implements the join."}}
{"actor":{"display_login":"OferLahav"}}