SELECT *
FROM
(
    SELECT number
    FROM numbers(10)
) AS a
,
(
    SELECT number
    FROM numbers(10)
) AS b
WHERE a.number = (b.number - 1)

Query id: 6ca49d4e-e6a1-4dc0-9552-280d481be176


0 rows in set. Elapsed: 0.003 sec.

Received exception from server (version 21.4.1):
Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch of columns to JOIN by: number: UInt64 at left, minus(b.number, 1): Int64 at right. Can't get supertype: There is no supertype for types UInt64, Int64 because some of them are signed integers and some are unsigned integers, but there is no signed integer type, that can exactly represent all required unsigned integer values.


set cross_to_inner_join_rewrite=0;

SELECT *
FROM
(
    SELECT number
    FROM numbers(10)
) AS a
,
(
    SELECT number
    FROM numbers(10)
) AS b
WHERE a.number = (b.number - 1)

Query id: ac47d703-f528-43ea-a0e7-a030a869b568

┌─number─┬─b.number─┐
│      0 │        1 │
│      1 │        2 │
│      2 │        3 │
│      3 │        4 │
│      4 │        5 │
│      5 │        6 │
│      6 │        7 │
│      7 │        8 │
│      8 │        9 │
└────────┴──────────┘