Using comparison operators in ON clause

Viewed 361

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.

1 Answers
Related