1-- test geqo
2CREATE SCHEMA geqo_schema;
3SET search_path TO geqo_schema;
4CREATE TABLE dist (a int, b int);
5SELECT create_distributed_table('dist', 'a');
6 create_distributed_table
7---------------------------------------------------------------------
8
9(1 row)
10
11INSERT INTO dist VALUES (1, 1), (2, 2), (3, 3);
12CREATE TABLE dist2 (a int, b int);
13SELECT create_distributed_table('dist2', 'a');
14 create_distributed_table
15---------------------------------------------------------------------
16
17(1 row)
18
19INSERT INTO dist2 VALUES (1, 1), (2, 2), (3, 3);
20SET geqo_threshold TO 2;
21SET geqo_pool_size TO 1000;
22SET geqo_generations TO 1000;
23SET citus.enable_repartition_joins to ON;
24SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a;
25 count
26---------------------------------------------------------------------
27     3
28(1 row)
29
30-- JOINs  with CTEs:
31WITH cte_1 AS (SELECT * FROM dist OFFSET 0)
32	SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a LEFT JOIN cte_1 ON true;
33 count
34---------------------------------------------------------------------
35     9
36(1 row)
37
38WITH cte_1 AS (SELECT * FROM dist OFFSET 0),
39	 cte_2 AS (SELECT * FROM dist2 OFFSET 0),
40	 cte_3 AS (SELECT * FROM dist OFFSET 0)
41	SELECT count(*) FROM cte_1 d1 LEFT JOIN cte_2 d2 ON d1.a = d2.a LEFT JOIN cte_3 d3 ON d3.a = d2.a LEFT JOIN cte_1 ON true;
42 count
43---------------------------------------------------------------------
44     9
45(1 row)
46
47-- Inner JOIN:
48SELECT count(*) FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a;
49 count
50---------------------------------------------------------------------
51     3
52(1 row)
53
54-- subquery join
55SELECT count(*) FROM (SELECT *, random() FROM dist) as d1 JOIN (SELECT *, random() FROM  dist2) d2 ON d1.a = d2.a JOIN (SELECT *, random() FROM dist) as d3 ON d3.a = d2.a;
56 count
57---------------------------------------------------------------------
58     3
59(1 row)
60
61SELECT count(*) FROM (SELECT *, random() FROM dist) as d1 LEFT JOIN (SELECT *, random() FROM  dist2) d2 ON d1.a = d2.a LEFT JOIN (SELECT *, random() FROM dist) as d3 ON d3.a = d2.a;
62 count
63---------------------------------------------------------------------
64     3
65(1 row)
66
67-- router query
68SELECT count(*) FROM dist d1 LEFT JOIN dist2 d2 ON d1.a = d2.a LEFT JOIN dist d3 ON d3.a = d2.a WHERE d1.a = 1 AND d2.a = 1 AND d3.a = 1;
69 count
70---------------------------------------------------------------------
71     1
72(1 row)
73
74-- fast path router query
75SELECT count(*) FROM dist WHERE a = 1;
76 count
77---------------------------------------------------------------------
78     1
79(1 row)
80
81-- simple INSERT
82INSERT INTO dist (a) VALUES (1);
83-- repartition join, probably not relevant, but still be defensive
84SELECT count(*) FROM dist d1 JOIN dist2 d2 ON d1.b = d2.b JOIN dist d3 ON d3.b = d2.b;
85 count
86---------------------------------------------------------------------
87     3
88(1 row)
89
90-- update query with join
91UPDATE dist SET b = foo.a FROM (SELECT d1.a FROM dist d1 JOIN dist2 d2 USING(a)) foo WHERE  foo.a = dist.a RETURNING *;
92 a | b | a
93---------------------------------------------------------------------
94 1 | 1 | 1
95 1 | 1 | 1
96 2 | 2 | 2
97 3 | 3 | 3
98(4 rows)
99
100-- insert select via repartitioning
101INSERT INTO dist (a) SELECT max(d1.b) FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a GROUP BY d1.a;
102SELECT count(*) FROM dist;
103 count
104---------------------------------------------------------------------
105     7
106(1 row)
107
108-- insert select pushdown
109INSERT INTO dist SELECT d1.* FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a WHERE d1.b < 2 AND d2.b < 2;
110SELECT count(*) FROM dist;
111 count
112---------------------------------------------------------------------
113    13
114(1 row)
115
116-- insert select via coordinator
117INSERT INTO dist SELECT d1.* FROM dist d1 JOIN dist2 d2 ON d1.a = d2.a JOIN dist d3 ON d3.a = d2.a WHERE d1.b < 2 AND d2.b < 2 OFFSET 0;
118SELECT count(*) FROM dist;
119 count
120---------------------------------------------------------------------
121    85
122(1 row)
123
124DROP SCHEMA geqo_schema CASCADE;
125NOTICE:  drop cascades to 2 other objects
126DETAIL:  drop cascades to table dist
127drop cascades to table dist2
128