clickhouse right join 问题

clickhouse支持联表查询的。对常用sql的支持比较好。但是也有出人意料的问题。例如：rigth join

1，写sql时发现a left join b 根 a rigth join b 在clickhouse中不一样。mysql肯定是一样的，为了确认一下，又回到msyql测试了一下，是一样的。

2，准备测试数据

CREATE TABLE tanktest.tank1 ON CLUSTER clickhouse_test_netjoy
(
    `id` UInt8 DEFAULT 0 COMMENT 'id',
    `name` Nullable(String) COMMENT '名字',
    `create_time` DateTime DEFAULT toDateTime(now()) COMMENT '创建时间'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/tanktest/tank1', '{replica}')
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY id
ORDER BY (id, create_time)

CREATE TABLE tanktest.tank1_all ON CLUSTER clickhouse_test_netjoy AS tanktest.tank1
ENGINE = Distributed(clickhouse_test_netjoy, tanktest, tank1, rand());

testjian :] select * from tanktest.tank_all;

SELECT *
FROM tanktest.tank_all

┌─id─┬─name──┬─────────create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  1 │ tank1 │ 2021-08-21 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┘

4 rows in set. Elapsed: 0.003 sec. 

testjian :] select * from tanktest.tank1_all;

SELECT *
FROM tanktest.tank1_all

┌─id─┬─name──┬─────────create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  2 │ tank1 │ 2021-08-21 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┐
│  5 │ tank5 │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┘

5 rows in set. Elapsed: 0.005 sec.

创建的是Distributed+ReplicatedMergeTree分布表，插入了一些数据。

3，测试一

testjian :] select * from `tanktest`.`tank_all` as `a` left join `tanktest`.`tank1_all` as `b` on `a`.`id` = `b`.`id`;

SELECT *
FROM tanktest.tank_all AS a
LEFT JOIN tanktest.tank1_all AS b ON a.id = b.id

┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  1 │ tank1 │ 2021-08-21 14:21:30 │    0 │ ᴺᵁᴸᴸ   │ 1970-01-01 08:00:00 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank1  │ 2021-08-21 14:21:30 │
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘

5 rows in set. Elapsed: 0.006 sec. 

testjian :] select * from  `tanktest`.`tank1_all` as `b` right join `tanktest`.`tank_all` as `a` on `b`.`id` = `a`.`id`;

SELECT *
FROM tanktest.tank1_all AS b
RIGHT JOIN tanktest.tank_all AS a ON b.id = a.id

┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  2 │ tank1 │ 2021-08-21 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    1 │ tank1  │ 2021-08-21 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    4 │ tank4  │ 2021-08-24 14:21:30 │
└────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    1 │ tank1  │ 2021-08-21 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank2  │ 2021-08-22 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    3 │ tank3  │ 2021-08-23 14:21:30 │
└────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘

9 rows in set. Elapsed: 0.011 sec.

left join根mysql不一样，少了去重功能。

mysql> select * from `tanktest`.`tank` as `a` left join `tanktest`.`tank1` as `b` on `a`.`id` = `b`.`id`;
+----+-------+---------------------+------+-------+---------------------+
| id | name  | create_time         | id   | name  | create_time         |
+----+-------+---------------------+------+-------+---------------------+
|  2 | tank2 | 2021-08-22 14:21:30 |    2 | tank1 | 2021-08-21 14:21:30 |
|  3 | tank3 | 2021-08-23 14:21:30 |    3 | tank3 | 2021-08-23 14:21:30 |
|  4 | tank4 | 2021-08-24 14:21:30 |    4 | tank4 | 2021-08-24 14:21:30 |
|  1 | tank1 | 2021-08-21 14:21:30 | NULL | NULL  | NULL                |
+----+-------+---------------------+------+-------+---------------------+
4 rows in set (0.00 sec)

4，测试二

testjian :] select * from `tanktest`.`tank1_all` as `a` left join `tanktest`.`tank_all` as `b` on `a`.`id` = `b`.`id`;

SELECT *
FROM tanktest.tank1_all AS a
LEFT JOIN tanktest.tank_all AS b ON a.id = b.id

┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  2 │ tank1 │ 2021-08-21 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐
│  5 │ tank5 │ 2021-08-24 14:21:30 │    0 │ ᴺᵁᴸᴸ   │ 1970-01-01 08:00:00 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘

5 rows in set. Elapsed: 0.005 sec. 

testjian :] select * from  `tanktest`.`tank_all` as `b` right join `tanktest`.`tank1_all` as `a` on `b`.`id` = `a`.`id`;

SELECT *
FROM tanktest.tank_all AS b
RIGHT JOIN tanktest.tank1_all AS a ON b.id = a.id

┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank2  │ 2021-08-22 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank1  │ 2021-08-21 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    4 │ tank4  │ 2021-08-24 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    5 │ tank5  │ 2021-08-24 14:21:30 │
└────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │
│  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank1  │ 2021-08-21 14:21:30 │
└────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘
┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    3 │ tank3  │ 2021-08-23 14:21:30 │
│  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    5 │ tank5  │ 2021-08-24 14:21:30 │
└────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘

10 rows in set. Elapsed: 0.006 sec.

从测试来看，最好不要用right join，因为返回的结果根预期差距有点大，感觉像交集后的再right join。