遇见逻辑需要使用or实现,但是添加or总是报错误,同事说是clickhouse不支持or,但是我测试数据or是可以的?
LEFT join tmp_cash_ref_in cash
on (toString(m.ref_no) = cash.ref_no or m.ext_ref_no = cash.ref_no)
and m.io_type = cash.io_type
and m.account_no = cash.accountId
一个左连接的查询,上面的语句在执行就直接报错,如下:
SQL 错误 [403] [07000]: Code: 403. DB::Exception: Unsupported JOIN ON conditions. Unexpected '(toString(ref_no AS `--m.ref_no`) = (cash.ref_no AS `--cash.ref_no`)) OR (ext_ref_no = `--cash.ref_no`)': While processing (toString(ref_no AS `--m.ref_no`) = (cash.ref_no AS `--cash.ref_no`)) OR (ext_ref_no = `--cash.ref_no`). (INVALID_JOIN_ON_EXPRESSION) (version 22.3.3.44 (official build))
, server ClickHouseNode(addr=http:192.168.20.189:8123, db=default)@98496345
但是我在测试的时候,clickhouse是支持or的,结果如下:clickhouse是支持or的
mysql> select ref_no,ae_code from dwd_cash_movement where (ref_no = 10000002 or ref_no = 10000001);
+----------+---------+
| ref_no | ae_code |
+----------+---------+
| 10000001 | A012 |
| 10000002 | A012 |
| 10000001 | A012 |
| 10000002 | A012 |
+----------+---------+
4 rows in set (0.00 sec)
Read 16384 rows, 358.92 KiB in 0.004209074 sec., 3892542 rows/sec., 83.27 MiB/sec.