Correct move conditions from JOIN ON to WHERE for ANY JOIN` #21671
Description
I think this could lead to error with . 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 = 1is 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 = 1The first one should return a line while the second returns anything because it joins on the first machting line with and then apply .
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;Originally posted by @PHaroZ in #18720 (comment)