Clickhouse: JOINs with FINAL keyword

[filimonov]

When joining with a table, the keyword final does not function as should.
Woraround: when you create a view with the FINAL and in the SQL you join to that view, the join works fine, however, this is not ideal since we are adding another step (i.e. View)

[4ertus2]

Could you attach a sample and expected result, please? It's not clear for which of joining tables FINAL should be applyed.

[alex-zaitsev]

Right side does not respect the FINAL, I guess.

[4ertus2]

I.e. we have multiple or comma JOIN with FINAL. Which table we should FINAL to apply? What if the query is big? It's very easy to make a mistake changing tables order.

I think the solution is to make special syntax: FINAL table1, table2 and forbid FINAL without table specification for JOINs. Even then we could have ambiguos table names in join.

[filimonov]

I think the most natural is to parse FINAL as a part of table name.

The query in original request was like:

select cols from from kafka_table inner join customer_data final using (customer_id, tenant_id) where tenant_id != 0;

So if we have comma join i would expect repeating final for every table which should be 'finalized'.

i.e.

 select * from tableX final; -- no joins
 select * from tableX final, tableY final; -- comma join, both 'final'
 select * from tableX, tableY final; -- comma join, right-hand with final
 select * from tableX left join tableY final; -- right-hand with final, left-hand w/o
 select * from tableX final left join tableY; -- left-hand with final, right-hand w/o

etc.

[alexey-milovidov]

That's reasonable, let's do that. @4ertus2 ?