N-way joins don't work when the join condition is an expression

[vpapavas]

Describe the bug N-way joins don't work when the join condition is an expression rather than a ColumnReference.

Consider this example:

select * from tab1 inner join tab2 on tab1.id+1 = tab2.id inner join tab3 on tab1.id*2 = tab3.id emit changes;
Invalid Left Join Expression: Cannot find the select field in the available fields. field: `ID`, schema: [`TAB1_SUM` INTEGER, `TAB1_ROWTIME` BIGINT, `TAB1_ROWPARTITION` INTEGER, `TAB1_ROWOFFSET` BIGINT, `TAB1_ID` INTEGER, `TAB2_COUNT` BIGINT, `TAB2_ROWTIME` BIGINT, `TAB2_ROWPARTITION` INTEGER, `TAB2_ROWOFFSET` BIGINT, `TAB2_ID` INTEGER]. expression: (ID * 2), schema:`TAB1_ID` INTEGER KEY, `TAB1_SUM` INTEGER, `TAB1_ROWTIME` BIGINT, `TAB1_ROWPARTITION` INTEGER, `TAB1_ROWOFFSET` BIGINT, `TAB1_ID` INTEGER, `TAB2_COUNT` BIGINT, `TAB2_ROWTIME` BIGINT, `TAB2_ROWPARTITION` INTEGER, `TAB2_ROWOFFSET` BIGINT, `TAB2_ID` INTEGER
Statement: select * from tab1 inner join tab2 on tab1.id+1 = tab2.id inner join tab3 on tab1.id*2 = tab3.id emit changes;
ks

Notice that although the join conditions are on the keys of the tables, they are expressions hence they get translated to foreign key joins.

The problem is that after each binary join, we change the schema to include non-qualified identifiers by replacing tab1.id with tab1_id. When translating the next join of the N-way join, we try to parse the join expression and lookup the join condition arguments (tab1.id) in the output schema of the previous join and cannot find it.

To Reproduce Steps to reproduce the behavior, include:

1. The version of KSQL. Master
2. Sample source data.

CREATE STREAM s1 (id int key, col1 int, col2 int) WITH (kafka_topic='s1', format='json', partitions=1);
CREATE STREAM s2 (id int key, col1 int, col2 int) WITH (kafka_topic='s2', format='json', partitions=1);
create table tab1 as select id, sum(col1) as sum from s1 group by id;
create table tab2 as select id, count(col1) as count from s2 group by id;

4. Any SQL statements you ran

select * from tab1 inner join tab2 on tab1.id+1 = tab2.id inner join tab3 on tab1.id*2 = tab3.id emit changes;

Expected behavior We should throw a more meaningful error message saying that complex expressions are not supported in N-way joins for now.

Actual behaviour A clear and concise description of what actually happens, including:

Invalid Left Join Expression: Cannot find the select field in the available fields

Additional context Add any other context about the problem here.

[suhas-satish]

@vpapavas , whats the scope of your proposed fix for this issue? Just improving the error msg or actually ensuring its handled as a primary key join and not throw the error?

[vpapavas]

Hey @suhas-satish! I am working on a PR that addresses part of the problem. N-way foreign key joins will work when the join expressions are simple columns. For the rest of the cases, it will fail but throw a more meaningful error message.

The fix for the general case is not trivial as we would need to change how joins behave in general (not change the output schema by prepending the alias) and since there is already work for changing the logical planner, I think it is best if this problem gets addressed then.