AntDB 数据库分布式功能-FQS增强12

左连接/全连接，on 的条件都包含分片键且没有排序

# 测试用表和数据请参加附录
# 左连接2表on的条件都包含分片键的且没有排序的FQS支持
antdb=# EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY LEFT OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID;
                                                                                                     QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_department.emp_id, fqs_company.name, fqs_department.dept
   Primary node/s: DN0
   Node/s: DN0, DN1, DN2
   Remote query: SELECT fqs_department.emp_id, fqs_company.name, fqs_department.dept FROM (public.fqs_company fqs_company LEFT JOIN public.fqs_department fqs_department ON ((fqs_company.id
 = fqs_department.id)))
(5 rows)


# 全连接2表on的条件都包含分片键的且没有排序的FQS支持
antdb=# EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY FULL OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID;
                                                                                                     QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_department.emp_id, fqs_company.name, fqs_department.dept
   Primary node/s: DN0
   Node/s: DN0, DN1, DN2
   Remote query: SELECT fqs_department.emp_id, fqs_company.name, fqs_department.dept FROM (public.fqs_company fqs_company FULL JOIN public.fqs_department fqs_department ON ((fqs_company.id
 = fqs_department.id)))
(5 rows)

对 Query 条件表达式中多个分片条件的筛选，包含分片键且是绑定变量的

# 测试用表和数据请参加附录
# 对Query条件表达式中多个分片条件的筛选，包含分片键且是绑定变量的
antdb=# prepare s2(int) as SELECT * FROM fqs_t1,fqs_t2 WHERE fqs_t1.c1=fqs_t2.c1 and fqs_t1.c1 = $1;
PREPARE
antdb=# EXPLAIN (verbose) execute s2(1);
                                                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id
   Node expr: $1
   Remote query: SELECT fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM public.fqs_t1 fqs_t1, public.fqs_t2 fqs_t2 WHERE ((fqs_t1.c1 = fqs_t2.c1) AND (fqs_t1.c1 = $1))
(4 rows)

# 对Query条件表达式中多个分片条件的筛选，包含分片键且静态常量的
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_t1,fqs_t2 WHERE fqs_t1.c1=fqs_t2.c1 and fqs_t1.c1 = 2;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id
   Node/s: DN1
   Remote query: SELECT fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM public.fqs_t1 fqs_t1, public.fqs_t2 fqs_t2 WHERE ((fqs_t1.c1 = fqs_t2.c1) AND (fqs_t1.c1 = 2))
(4 rows)