Clickhouse ASOF JOIN on just one column (Exception: Cannot get JOIN keys from JOIN ON section)

Viewed 1107

Clickhouse gives me an error when I try to ASOF JOIN on just one column, but not when I add an equality JOIN clause. Why?

Example tables:

orders:
┌─time─┬─price─┬─id─┐
│    0 │     4 │  3 │
│    1 │     4 │  3 │
│    2 │     4 │  3 │
│    3 │     3 │  3 │
│    4 │     2 │  3 │
│    5 │     1 │  3 │
│    6 │     1 │  3 │
trades:
┌─time─┬─qty─┬─id─┐
│    2 │ 100 │  3 │
│    4 │ 200 │  3 │
│    5 │ 205 │  3 │

Let's first try to ASOF JOIN on the time column alone.

SELECT
    time,
    price,
    qty
FROM orders
ASOF INNER JOIN trades ON trades.time >= orders.time
ORDER BY time ASC

Received exception from server (version 21.7.5):
Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Cannot get JOIN keys from JOIN ON section: trades.time >= time. 

Now let's do the same thing, except we'll also JOIN on the dummy column (id).

SELECT
    time,
    price,
    qty
FROM orders
ASOF INNER JOIN trades ON (trades.id = orders.id) AND (trades.time >= orders.time)
ORDER BY time ASC

┌─time─┬─price─┬─qty─┐
│    0 │     4 │ 100 │
│    1 │     4 │ 100 │
│    2 │     4 │ 100 │
│    3 │     3 │ 200 │
│    4 │     2 │ 200 │
│    5 │     1 │ 205 │
└──────┴───────┴─────┘
0 Answers
Related