Cannot use comparison operators in ON clause?
[How to reproduce]
- ClickHouse server version : v19.11.0
- Example with two tables
table1
CREATE TABLE table1
(
str1 String,
num1 UInt64
) ENGINE=MergeTree()
ORDER BY (str1);
table2
CREATE TABLE table2
(
str2 String,
num2 UInt64
) ENGINE=MergeTree()
ORDER BY (str2);
query
SELECT
*
FROM
table1 AS t1
LEFT OUTER JOIN
table2 AS t2
ON
t1.str1 = t2.str2
AND greater(t1.num1, t2.num2) = 1;
error message
DB::Exception: Invalid columns in JOIN ON section. Columns num1 and num2 are from different tables..
Without AND greater(t1.num1, t2.num2) = 1, query is successful.