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 │
└──────┴───────┴─────┘