Type conversions for JOIN keys. #18567
Description
Use case
SELECT *
FROM
(
SELECT 1 AS k
) AS t1
INNER JOIN
(
SELECT -1 AS k
) AS t2 USING (k)
Received exception from server (version 20.13.1):
Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch of columns to JOIN by: k UInt8 at left, t2.k Int8 at right.
SELECT *
FROM
(
SELECT 1 AS k
) AS t1
INNER JOIN
(
SELECT -1 AS k
) AS t2 ON t1.k = t2.k
Received exception from server (version 20.13.1):
Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch of columns to JOIN by: k UInt8 at left, t2.k Int8 at right.
Describe the solution you'd like
Calculate getLeastSupertype and convert columns to it before joining.
Details
It can be implemented in more sophisticated way, for example, if it is INNER or LEFT join, we can avoid type conversions for left table skip all records from right table that are not convertible. See the example in implementation of IN operator.
There can be also some difficulties due to the fact that the type of k in the result will be changed.