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,准备测试数据

  1. CREATE TABLE tanktest.tank1 ON CLUSTER clickhouse_test_netjoy  
  2. (  
  3.     `id` UInt8 DEFAULT 0 COMMENT 'id',  
  4.     `name` Nullable(String) COMMENT '名字',  
  5.     `create_time` DateTime DEFAULT toDateTime(now()) COMMENT '创建时间'  
  6. )  
  7. ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/tanktest/tank1''{replica}')  
  8. PARTITION BY toYYYYMM(create_time)  
  9. PRIMARY KEY id  
  10. ORDER BY (id, create_time)  
  11.   
  12. CREATE TABLE tanktest.tank1_all ON CLUSTER clickhouse_test_netjoy AS tanktest.tank1  
  13. ENGINE = Distributed(clickhouse_test_netjoy, tanktest, tank1, rand());  
  14.   
  15. testjian :] select * from tanktest.tank_all;  
  16.   
  17. SELECT *  
  18. FROM tanktest.tank_all  
  19.   
  20. ┌─id─┬─name──┬─────────create_time─┐  
  21. │  3 │ tank3 │ 2021-08-23 14:21:30 │  
  22. └────┴───────┴─────────────────────┘  
  23. ┌─id─┬─name──┬─────────create_time─┐  
  24. │  4 │ tank4 │ 2021-08-24 14:21:30 │  
  25. └────┴───────┴─────────────────────┘  
  26. ┌─id─┬─name──┬─────────create_time─┐  
  27. │  1 │ tank1 │ 2021-08-21 14:21:30 │  
  28. └────┴───────┴─────────────────────┘  
  29. ┌─id─┬─name──┬─────────create_time─┐  
  30. │  2 │ tank2 │ 2021-08-22 14:21:30 │  
  31. └────┴───────┴─────────────────────┘  
  32.   
  33. 4 rows in set. Elapsed: 0.003 sec.   
  34.   
  35. testjian :] select * from tanktest.tank1_all;  
  36.   
  37. SELECT *  
  38. FROM tanktest.tank1_all  
  39.   
  40. ┌─id─┬─name──┬─────────create_time─┐  
  41. │  3 │ tank3 │ 2021-08-23 14:21:30 │  
  42. └────┴───────┴─────────────────────┘  
  43. ┌─id─┬─name──┬─────────create_time─┐  
  44. │  2 │ tank2 │ 2021-08-22 14:21:30 │  
  45. └────┴───────┴─────────────────────┘  
  46. ┌─id─┬─name──┬─────────create_time─┐  
  47. │  2 │ tank1 │ 2021-08-21 14:21:30 │  
  48. └────┴───────┴─────────────────────┘  
  49. ┌─id─┬─name──┬─────────create_time─┐  
  50. │  4 │ tank4 │ 2021-08-24 14:21:30 │  
  51. └────┴───────┴─────────────────────┘  
  52. ┌─id─┬─name──┬─────────create_time─┐  
  53. │  5 │ tank5 │ 2021-08-24 14:21:30 │  
  54. └────┴───────┴─────────────────────┘  
  55.   
  56. 5 rows in set. Elapsed: 0.005 sec.  

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

3,测试一

  1. testjian :] select * from `tanktest`.`tank_all` as `a` left join `tanktest`.`tank1_all` as `b` on `a`.`id` = `b`.`id`;  
  2.   
  3. SELECT *  
  4. FROM tanktest.tank_all AS a  
  5. LEFT JOIN tanktest.tank1_all AS b ON a.id = b.id  
  6.   
  7. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  8. │  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  9. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  10. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  11. │  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  12. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  13. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  14. │  1 │ tank1 │ 2021-08-21 14:21:30 │    0 │ ᴺᵁᴸᴸ   │ 1970-01-01 08:00:00 │  
  15. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  16. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  17. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank1  │ 2021-08-21 14:21:30 │  
  18. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  19. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  20.   
  21. 5 rows in set. Elapsed: 0.006 sec.   
  22.   
  23. testjian :] select * from  `tanktest`.`tank1_all` as `b` right join `tanktest`.`tank_all` as `a` on `b`.`id` = `a`.`id`;  
  24.   
  25. SELECT *  
  26. FROM tanktest.tank1_all AS b  
  27. RIGHT JOIN tanktest.tank_all AS a ON b.id = a.id  
  28.   
  29. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  30. │  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  31. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  32. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  33. │  2 │ tank1 │ 2021-08-21 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  34. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  35. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  36. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  37. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  38. ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  39. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    1 │ tank1  │ 2021-08-21 14:21:30 │  
  40. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  41. └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  42. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  43. │  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  44. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  45. ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  46. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    1 │ tank1  │ 2021-08-21 14:21:30 │  
  47. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  48. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  49. └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  50.   
  51. 9 rows in set. Elapsed: 0.011 sec.  

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

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

4,测试二

  1. testjian :] select * from `tanktest`.`tank1_all` as `a` left join `tanktest`.`tank_all` as `b` on `a`.`id` = `b`.`id`;  
  2.   
  3. SELECT *  
  4. FROM tanktest.tank1_all AS a  
  5. LEFT JOIN tanktest.tank_all AS b ON a.id = b.id  
  6.   
  7. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  8. │  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  9. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  10. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  11. │  2 │ tank1 │ 2021-08-21 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  12. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  13. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  14. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  15. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  16. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  17. │  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  18. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  19. ┌─id─┬─name──┬─────────create_time─┬─b.id─┬─b.name─┬───────b.create_time─┐  
  20. │  5 │ tank5 │ 2021-08-24 14:21:30 │    0 │ ᴺᵁᴸᴸ   │ 1970-01-01 08:00:00 │  
  21. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  22.   
  23. 5 rows in set. Elapsed: 0.005 sec.   
  24.   
  25. testjian :] select * from  `tanktest`.`tank_all` as `b` right join `tanktest`.`tank1_all` as `a` on `b`.`id` = `a`.`id`;  
  26.   
  27. SELECT *  
  28. FROM tanktest.tank_all AS b  
  29. RIGHT JOIN tanktest.tank1_all AS a ON b.id = a.id  
  30.   
  31. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  32. │  3 │ tank3 │ 2021-08-23 14:21:30 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  33. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  34. ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  35. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  36. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    2 │ tank1  │ 2021-08-21 14:21:30 │  
  37. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  38. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    5 │ tank5  │ 2021-08-24 14:21:30 │  
  39. └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  40. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  41. │  4 │ tank4 │ 2021-08-24 14:21:30 │    4 │ tank4  │ 2021-08-24 14:21:30 │  
  42. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  43. ┌─id─┬─name──┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  44. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank2  │ 2021-08-22 14:21:30 │  
  45. │  2 │ tank2 │ 2021-08-22 14:21:30 │    2 │ tank1  │ 2021-08-21 14:21:30 │  
  46. └────┴───────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  47. ┌─id─┬─name─┬─────────create_time─┬─a.id─┬─a.name─┬───────a.create_time─┐  
  48. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    3 │ tank3  │ 2021-08-23 14:21:30 │  
  49. │  0 │ ᴺᵁᴸᴸ │ 1970-01-01 08:00:00 │    5 │ tank5  │ 2021-08-24 14:21:30 │  
  50. └────┴──────┴─────────────────────┴──────┴────────┴─────────────────────┘  
  51.   
  52. 10 rows in set. Elapsed: 0.006 sec.  

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



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

相关文章

    • 暂无相关日志