clickhouse right join 问题

张映 发表于 2021-09-09

分类目录: clickhouse

标签:,

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。



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/clickhouse/2532.html

相关文章

    • 暂无相关日志