## 錯誤提示如下：
```SQL
ERROR:  direct joins between distributed and local tables are not supported
HINT:  Use CTE's or subqueries to select from local tables and use them in joins
```
建立測試表
```SQL
drop table if exists products;
drop table if exists customers;
drop table if exists orders;

create table customers (customers_id bigint,product_name varchar(100));
alter table customers add primary key (customers_id);

create table orders (order_id bigint,customers_id bigint,order_detail varchar(100));
alter table orders add primary key(order_id,customers_id);

create table products (product_id bigint,customers_id bigint,product_name varchar(100));
alter table products add primary key (product_id);

select create_distributed_table('customers','customers_id');
select create_distributed_table('orders','customers_id',colocate_with=>'customers');

```
如下是不會報錯
```SQL
lightdb@test=# select p.*,o.* from products p ,orders o where o.customers_id = p.customers_id;
 product_id | customers_id | product_name | order_id | customers_id | order_detail 
------------+--------------+--------------+----------+--------------+--------------
(0 rows)

```
但是執行下面SQL便報如下錯誤
```SQL
lightdb@test=# select c.* , o.* from customers c , orders o where o.customers_id = c.customers_id 
lightdb@test-# and exists (select 1 from products p where p.customers_id = c.customers_id);
ERROR:  direct joins between distributed and local tables are not supported
HINT:  Use CTE's or subqueries to select from local tables and use them in joins
```
## 關於這個錯誤的解釋：
這個錯誤提示是因為 Canopy不支援在分散式表和本地表之間進行直接連線操作。
在 Canopy中，分散式表是由多個節點上的資料子集組成的，而本地表只存在於單個節點上。由於分散式表和本地表之間的資料分佈不同，Canopy不能有效地執行這種連線操作。
相反，您可以使用公共表表示式 (CTE) 或子查詢來從本地表中選擇資料，然後將其與分散式表連線。例如：
```SQL
WITH local_data AS (
  SELECT *
  FROM my_local_table
)
SELECT *
FROM my_distributed_table
```
JOIN local_data ON my_distributed_table.key = local_data.key
上面的示例中，我們首先使用 CTE 選擇本地表中的資料，然後將其與分散式表進行連線。這種方法可以避免直接連線分散式表和本地表，從而避免了錯誤提示。
總之，在 Canopy中連線分散式表和本地表時，您應該使用 CTE 或子查詢來選擇本地表資料，並將其與分散式表進行連線，而不是直接連線它們。這可以確保 Canopy在分散式叢集中正確執行查詢，並避免出現錯誤提示。
所以將原SQL改成
```SQL
lightdb@test=# with products_local as (select * from products)
lightdb@test-# select c.* , o.* 
lightdb@test-# from customers c , orders o 
lightdb@test-# where o.customers_id = c.customers_id 
lightdb@test-# and exists (select 1 from products_local p where p.customers_id = c.customers_id);
 customers_id | product_name | order_id | customers_id | order_detail 
--------------+--------------+----------+--------------+--------------
(0 rows)

```
## 或者考慮將products轉換成分散式參考表
```SQL
SELECT create_reference_table('products');
lightdb@test=# select c.* , o.* 
lightdb@test-# from customers c , orders o 
lightdb@test-# where o.customers_id = c.customers_id 
lightdb@test-# and exists (select 1 from products p where p.customers_id = c.customers_id);
 customers_id | product_name | order_id | customers_id | order_detail 
--------------+--------------+----------+--------------+--------------
(0 rows)
```
錯誤遍不會存在，正常情況下極少數會使用本地表

PostgreSQL/Citus 常見報錯問題分析（一）ERROR: direct joins between distributed

相關文章