Move conditions from JOIN ON to WHERE

[alexey-milovidov]

Use case
Conditions that are not related to JOIN:
t1 JOIN t2 ON t1.k = t2.k AND t1.hello = 'world'
should be transformed to:
t1 JOIN t2 ON t1.k = t2.k WHERE t1.hello = 'world'
otherwise it does not work.

[hexiaoting]

assign to me

[alexey-milovidov]

@hexiaoting Ok. Thank you in advance!

[hexiaoting]

@alexey-milovidov I have two questions:
Q1:
t1 JOIN t2 ON t1.k = t2.k AND t1.size > 10 will this query throw exception or translated to: t1 JOIN t2 ON t1.k = t2.k where t1.size > 10

Q2: t1 JOIN t2 ON t1.size == 10 and t2.size == 8 what kind of new sql should this query translated to ?

[hexiaoting]

@alexey-milovidov I need some help, related to above questions. Thank you.

[vdimir]

@hexiaoting sorry for long delay.

t1 JOIN t2 ON t1.k = t2.k AND t1.size > 10

It's very close to query in first comment of this issue, so correct behavior is t1 JOIN t2 ON t1.k = t2.k where t1.size > 10

Q2: t1 JOIN t2 ON t1.size == 10 and t2.size == 8 what kind of new sql should this query translated to ?

It should not be allowed as it is now.

[hexiaoting]

@vdimir yeah, I have implemented this optimization and now the behavior is same with your opinion.

[vdimir]

Closed via #19685

[PHaroZ]

I think this could lead to error with ANY JOIN. A query like

SELECT *
FROM
	(SELECT 1 AS id) AS l
	INNER ANY JOIN (SELECT 1 AS id, number as v FROM system.numbers LIMIT 2) AS r
	          ON l.id = r.id AND r.v = 1

is not the same as

SELECT *
FROM
	(SELECT 1 AS id) AS l
	INNER ANY JOIN (SELECT 1 AS id, number as v FROM system.numbers LIMIT 2) AS r
	          ON l.id = r.id
WHERE
	r.v = 1

The first one should return a line while the second returns anything because it joins on the first machting line with v=0 and then apply v=1.

IMHO the where clause should be added to a subselect like

SELECT *
FROM
	(SELECT 1 AS id) AS l
	INNER ANY JOIN (SELECT * FROM (SELECT 1 AS id, number AS v FROM system.numbers LIMIT 2) WHERE v = 1) AS r
	          ON l.id = r.id;

[vdimir]

@PHaroZ makes sense, thank you for your notice. We are going to fix it.