[SAMZA-2554] SQL Join Filter Handling is Broken when Table is remote and the condition has more than one expression

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Assignee: Unassigned
• Reporter: Slim Bouguerra
• Created: 23/Jun/20 16:25
• Updated: 30/Jun/20 04:22

Description

The current implementation of remote table join can not handle more than one condition.

Currently this will return wrong results silently by ignoring the filter expression.

As a first step we need to fail such query to start with and then add the support of 2 or more filter expression as part of the join condition.

Here is the test case org.apache.samza.test.samzasql.TestSamzaSqlRemoteTable#testSourceEndToEndWithKeyWithNullForeignKeysRightOuterJoin

String sql =
        "Insert into testavro.enrichedPageViewTopic "
            + "select pv.pageKey as __key__, pv.pageKey as pageKey, coalesce(null, 'N/A') as companyName,"
            + "       p.name as profileName, p.address as profileAddress "
            + " from  "
            + " testavro.PAGEVIEW as pv left outer join testRemoteStore.Profile.`$table` as p"
            + " on p.__key__ = pv.profileId and p.__key__ = pv.profileId + 1 and  p.__key__ = pv.profileId - 1 ";

 String sql =
        "Insert into testavro.enrichedPageViewTopic "
            + "select pv.pageKey as __key__, pv.pageKey as pageKey, coalesce(null, 'N/A') as companyName,"
            + "       p.name as profileName, p.address as profileAddress "
            + " from  "
            + " testavro.PAGEVIEW as pv left outer join testRemoteStore.Profile.`$table` as p"
            + " on p.__key__ = pv.profileId  ";

Both queries will return the same result and that is wrong