The problem with PR is distributed queries expect ON with _shard_num working

SELECT * FROM a JOIN b ON _shard_num = b.shard_num

Hi,

This PR breaks a lots of queries in my app : queries like

SELECT
	t.value,
	i18n.title
FROM
	aTable t
	LEFT ANY JOIN aTable_i18n i18n
	          ON i18n.ref_id = t.id AND i18n.lang = 'en'

Is it really what you wanted or it's an unexpected side-effect ?

yes, new version of clickhouse corrupts number of queries:

SELECT * FROM (SELECT NULL AS a, 1 AS b) AS foo RIGHT JOIN (SELECT 1024 AS b) AS bar ON 1 = foo.b;

throws error: Not equi-join ON expression: 1 = b. No columns in one of equality side.: While processing 1 = b

whats wrong in joining on value=column? works in any other database

@jobosk Seems that it's different case.

Now conditions that is not in the form t1.col1 = t2.col2 where t1 and t2 different tables is not supported, except case of INNER join (for such cases conditions moved to WHERE on query processing, see #18720). Conditions that depends on one table in ON and WHERE sections have slightly different semantic (see #19685 (comment)).

We considered to support such conditions in ON section, but now there's no ETA for this. I'll create an issue.

UPD: Comment by @jobosk to that I answered had been deleted from this thread

@jobosk Seems that it's different case.

Now conditions that is not in the form t1.col1 = t2.col2 where t1 and t2 different tables is not supported, except case of INNER join (for such cases conditions moved to WHERE on query processing, see #18720). Conditions that depends on one table in ON and WHERE sections have slightly different semantic (see #19685 (comment)).

We considered to support such conditions in ON section, but now there's no ETA for this. I'll create an issue.

UPD: Comment by @jobosk to that I answered had been deleted from this thread

Sorry! I deleted my comment after updating to 21.3.2.5, and not having an issue with that version I assumed it was fixed afterwards. Didn't see your reply, the page must've been cached.

For anyone who wonders what it was about: I had a problem after migrating to server v21.1.7.1 using conditions like r1.col1 = 'string' in the ON section of my JOIN.

Since I deleted my comment and don't have access to the code right now, I'm not sure wether it was an INNER JOIN and it was indeed a problem with that version later fixed, or if wasn't, in which case I'm not really sure why it does work with later versions. I'll let you know as soon as I have access to the code.