1--
2-- Tests for the planner's "equivalence class" mechanism
3--
4-- One thing that's not tested well during normal querying is the logic
5-- for handling "broken" ECs.  This is because an EC can only become broken
6-- if its underlying btree operator family doesn't include a complete set
7-- of cross-type equality operators.  There are not (and should not be)
8-- any such families built into Postgres; so we have to hack things up
9-- to create one.  We do this by making two alias types that are really
10-- int8 (so we need no new C code) and adding only some operators for them
11-- into the standard integer_ops opfamily.
12create type int8alias1;
13create function int8alias1in(cstring) returns int8alias1
14  strict immutable language internal as 'int8in';
15NOTICE:  return type int8alias1 is only a shell
16create function int8alias1out(int8alias1) returns cstring
17  strict immutable language internal as 'int8out';
18NOTICE:  argument type int8alias1 is only a shell
19create type int8alias1 (
20    input = int8alias1in,
21    output = int8alias1out,
22    like = int8
23);
24create type int8alias2;
25create function int8alias2in(cstring) returns int8alias2
26  strict immutable language internal as 'int8in';
27NOTICE:  return type int8alias2 is only a shell
28create function int8alias2out(int8alias2) returns cstring
29  strict immutable language internal as 'int8out';
30NOTICE:  argument type int8alias2 is only a shell
31create type int8alias2 (
32    input = int8alias2in,
33    output = int8alias2out,
34    like = int8
35);
36create cast (int8 as int8alias1) without function;
37create cast (int8 as int8alias2) without function;
38create cast (int8alias1 as int8) without function;
39create cast (int8alias2 as int8) without function;
40create function int8alias1eq(int8alias1, int8alias1) returns bool
41  strict immutable language internal as 'int8eq';
42create operator = (
43    procedure = int8alias1eq,
44    leftarg = int8alias1, rightarg = int8alias1,
45    commutator = =,
46    restrict = eqsel, join = eqjoinsel,
47    merges
48);
49alter operator family integer_ops using btree add
50  operator 3 = (int8alias1, int8alias1);
51create function int8alias2eq(int8alias2, int8alias2) returns bool
52  strict immutable language internal as 'int8eq';
53create operator = (
54    procedure = int8alias2eq,
55    leftarg = int8alias2, rightarg = int8alias2,
56    commutator = =,
57    restrict = eqsel, join = eqjoinsel,
58    merges
59);
60alter operator family integer_ops using btree add
61  operator 3 = (int8alias2, int8alias2);
62create function int8alias1eq(int8, int8alias1) returns bool
63  strict immutable language internal as 'int8eq';
64create operator = (
65    procedure = int8alias1eq,
66    leftarg = int8, rightarg = int8alias1,
67    restrict = eqsel, join = eqjoinsel,
68    merges
69);
70alter operator family integer_ops using btree add
71  operator 3 = (int8, int8alias1);
72create function int8alias1eq(int8alias1, int8alias2) returns bool
73  strict immutable language internal as 'int8eq';
74create operator = (
75    procedure = int8alias1eq,
76    leftarg = int8alias1, rightarg = int8alias2,
77    restrict = eqsel, join = eqjoinsel,
78    merges
79);
80alter operator family integer_ops using btree add
81  operator 3 = (int8alias1, int8alias2);
82create function int8alias1lt(int8alias1, int8alias1) returns bool
83  strict immutable language internal as 'int8lt';
84create operator < (
85    procedure = int8alias1lt,
86    leftarg = int8alias1, rightarg = int8alias1
87);
88alter operator family integer_ops using btree add
89  operator 1 < (int8alias1, int8alias1);
90create function int8alias1cmp(int8, int8alias1) returns int
91  strict immutable language internal as 'btint8cmp';
92alter operator family integer_ops using btree add
93  function 1 int8alias1cmp (int8, int8alias1);
94create table ec0 (ff int8 primary key, f1 int8, f2 int8);
95create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
96create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
97-- for the moment we only want to look at nestloop plans
98set enable_hashjoin = off;
99set enable_mergejoin = off;
100--
101-- Note that for cases where there's a missing operator, we don't care so
102-- much whether the plan is ideal as that we don't fail or generate an
103-- outright incorrect plan.
104--
105explain (costs off)
106  select * from ec0 where ff = f1 and f1 = '42'::int8;
107            QUERY PLAN
108-----------------------------------
109 Index Scan using ec0_pkey on ec0
110   Index Cond: (ff = '42'::bigint)
111   Filter: (f1 = '42'::bigint)
112(3 rows)
113
114explain (costs off)
115  select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
116              QUERY PLAN
117---------------------------------------
118 Index Scan using ec0_pkey on ec0
119   Index Cond: (ff = '42'::int8alias1)
120   Filter: (f1 = '42'::int8alias1)
121(3 rows)
122
123explain (costs off)
124  select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
125              QUERY PLAN
126---------------------------------------
127 Index Scan using ec1_pkey on ec1
128   Index Cond: (ff = '42'::int8alias1)
129   Filter: (f1 = '42'::int8alias1)
130(3 rows)
131
132explain (costs off)
133  select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
134                    QUERY PLAN
135---------------------------------------------------
136 Seq Scan on ec1
137   Filter: ((ff = f1) AND (f1 = '42'::int8alias2))
138(2 rows)
139
140explain (costs off)
141  select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
142                            QUERY PLAN
143-------------------------------------------------------------------
144 Nested Loop
145   Join Filter: (ec1.ff = ec2.x1)
146   ->  Index Scan using ec1_pkey on ec1
147         Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
148   ->  Seq Scan on ec2
149(5 rows)
150
151explain (costs off)
152  select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
153                 QUERY PLAN
154---------------------------------------------
155 Nested Loop
156   ->  Index Scan using ec1_pkey on ec1
157         Index Cond: (ff = '42'::int8alias1)
158   ->  Seq Scan on ec2
159         Filter: (x1 = '42'::int8alias1)
160(5 rows)
161
162explain (costs off)
163  select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
164               QUERY PLAN
165-----------------------------------------
166 Nested Loop
167   Join Filter: (ec1.ff = ec2.x1)
168   ->  Index Scan using ec1_pkey on ec1
169         Index Cond: (ff = '42'::bigint)
170   ->  Seq Scan on ec2
171         Filter: ('42'::bigint = x1)
172(6 rows)
173
174explain (costs off)
175  select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
176                 QUERY PLAN
177---------------------------------------------
178 Nested Loop
179   ->  Index Scan using ec1_pkey on ec1
180         Index Cond: (ff = '42'::int8alias1)
181   ->  Seq Scan on ec2
182         Filter: (x1 = '42'::int8alias1)
183(5 rows)
184
185explain (costs off)
186  select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
187               QUERY PLAN
188-----------------------------------------
189 Nested Loop
190   ->  Seq Scan on ec2
191         Filter: (x1 = '42'::int8alias2)
192   ->  Index Scan using ec1_pkey on ec1
193         Index Cond: (ff = ec2.x1)
194(5 rows)
195
196create unique index ec1_expr1 on ec1((ff + 1));
197create unique index ec1_expr2 on ec1((ff + 2 + 1));
198create unique index ec1_expr3 on ec1((ff + 3 + 1));
199create unique index ec1_expr4 on ec1((ff + 4));
200explain (costs off)
201  select * from ec1,
202    (select ff + 1 as x from
203       (select ff + 2 as ff from ec1
204        union all
205        select ff + 3 as ff from ec1) ss0
206     union all
207     select ff + 4 as x from ec1) as ss1
208  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
209                     QUERY PLAN
210-----------------------------------------------------
211 Nested Loop
212   ->  Index Scan using ec1_pkey on ec1
213         Index Cond: (ff = '42'::bigint)
214   ->  Append
215         ->  Index Scan using ec1_expr2 on ec1 ec1_1
216               Index Cond: (((ff + 2) + 1) = ec1.f1)
217         ->  Index Scan using ec1_expr3 on ec1 ec1_2
218               Index Cond: (((ff + 3) + 1) = ec1.f1)
219         ->  Index Scan using ec1_expr4 on ec1 ec1_3
220               Index Cond: ((ff + 4) = ec1.f1)
221(10 rows)
222
223explain (costs off)
224  select * from ec1,
225    (select ff + 1 as x from
226       (select ff + 2 as ff from ec1
227        union all
228        select ff + 3 as ff from ec1) ss0
229     union all
230     select ff + 4 as x from ec1) as ss1
231  where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
232                            QUERY PLAN
233-------------------------------------------------------------------
234 Nested Loop
235   Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
236   ->  Index Scan using ec1_pkey on ec1
237         Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
238         Filter: (ff = f1)
239   ->  Append
240         ->  Index Scan using ec1_expr2 on ec1 ec1_1
241               Index Cond: (((ff + 2) + 1) = '42'::bigint)
242         ->  Index Scan using ec1_expr3 on ec1 ec1_2
243               Index Cond: (((ff + 3) + 1) = '42'::bigint)
244         ->  Index Scan using ec1_expr4 on ec1 ec1_3
245               Index Cond: ((ff + 4) = '42'::bigint)
246(12 rows)
247
248explain (costs off)
249  select * from ec1,
250    (select ff + 1 as x from
251       (select ff + 2 as ff from ec1
252        union all
253        select ff + 3 as ff from ec1) ss0
254     union all
255     select ff + 4 as x from ec1) as ss1,
256    (select ff + 1 as x from
257       (select ff + 2 as ff from ec1
258        union all
259        select ff + 3 as ff from ec1) ss0
260     union all
261     select ff + 4 as x from ec1) as ss2
262  where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
263                             QUERY PLAN
264---------------------------------------------------------------------
265 Nested Loop
266   ->  Nested Loop
267         ->  Index Scan using ec1_pkey on ec1
268               Index Cond: (ff = '42'::bigint)
269         ->  Append
270               ->  Index Scan using ec1_expr2 on ec1 ec1_1
271                     Index Cond: (((ff + 2) + 1) = ec1.f1)
272               ->  Index Scan using ec1_expr3 on ec1 ec1_2
273                     Index Cond: (((ff + 3) + 1) = ec1.f1)
274               ->  Index Scan using ec1_expr4 on ec1 ec1_3
275                     Index Cond: ((ff + 4) = ec1.f1)
276   ->  Append
277         ->  Index Scan using ec1_expr2 on ec1 ec1_4
278               Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1)))
279         ->  Index Scan using ec1_expr3 on ec1 ec1_5
280               Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1)))
281         ->  Index Scan using ec1_expr4 on ec1 ec1_6
282               Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1)))
283(18 rows)
284
285-- let's try that as a mergejoin
286set enable_mergejoin = on;
287set enable_nestloop = off;
288explain (costs off)
289  select * from ec1,
290    (select ff + 1 as x from
291       (select ff + 2 as ff from ec1
292        union all
293        select ff + 3 as ff from ec1) ss0
294     union all
295     select ff + 4 as x from ec1) as ss1,
296    (select ff + 1 as x from
297       (select ff + 2 as ff from ec1
298        union all
299        select ff + 3 as ff from ec1) ss0
300     union all
301     select ff + 4 as x from ec1) as ss2
302  where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
303                           QUERY PLAN
304-----------------------------------------------------------------
305 Merge Join
306   Merge Cond: ((((ec1_4.ff + 2) + 1)) = (((ec1_1.ff + 2) + 1)))
307   ->  Merge Append
308         Sort Key: (((ec1_4.ff + 2) + 1))
309         ->  Index Scan using ec1_expr2 on ec1 ec1_4
310         ->  Index Scan using ec1_expr3 on ec1 ec1_5
311         ->  Index Scan using ec1_expr4 on ec1 ec1_6
312   ->  Materialize
313         ->  Merge Join
314               Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
315               ->  Merge Append
316                     Sort Key: (((ec1_1.ff + 2) + 1))
317                     ->  Index Scan using ec1_expr2 on ec1 ec1_1
318                     ->  Index Scan using ec1_expr3 on ec1 ec1_2
319                     ->  Index Scan using ec1_expr4 on ec1 ec1_3
320               ->  Sort
321                     Sort Key: ec1.f1 USING <
322                     ->  Index Scan using ec1_pkey on ec1
323                           Index Cond: (ff = '42'::bigint)
324(19 rows)
325
326-- check partially indexed scan
327set enable_nestloop = on;
328set enable_mergejoin = off;
329drop index ec1_expr3;
330explain (costs off)
331  select * from ec1,
332    (select ff + 1 as x from
333       (select ff + 2 as ff from ec1
334        union all
335        select ff + 3 as ff from ec1) ss0
336     union all
337     select ff + 4 as x from ec1) as ss1
338  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
339                     QUERY PLAN
340-----------------------------------------------------
341 Nested Loop
342   ->  Index Scan using ec1_pkey on ec1
343         Index Cond: (ff = '42'::bigint)
344   ->  Append
345         ->  Index Scan using ec1_expr2 on ec1 ec1_1
346               Index Cond: (((ff + 2) + 1) = ec1.f1)
347         ->  Seq Scan on ec1 ec1_2
348               Filter: (((ff + 3) + 1) = ec1.f1)
349         ->  Index Scan using ec1_expr4 on ec1 ec1_3
350               Index Cond: ((ff + 4) = ec1.f1)
351(10 rows)
352
353-- let's try that as a mergejoin
354set enable_mergejoin = on;
355set enable_nestloop = off;
356explain (costs off)
357  select * from ec1,
358    (select ff + 1 as x from
359       (select ff + 2 as ff from ec1
360        union all
361        select ff + 3 as ff from ec1) ss0
362     union all
363     select ff + 4 as x from ec1) as ss1
364  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
365                     QUERY PLAN
366-----------------------------------------------------
367 Merge Join
368   Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
369   ->  Merge Append
370         Sort Key: (((ec1_1.ff + 2) + 1))
371         ->  Index Scan using ec1_expr2 on ec1 ec1_1
372         ->  Sort
373               Sort Key: (((ec1_2.ff + 3) + 1))
374               ->  Seq Scan on ec1 ec1_2
375         ->  Index Scan using ec1_expr4 on ec1 ec1_3
376   ->  Sort
377         Sort Key: ec1.f1 USING <
378         ->  Index Scan using ec1_pkey on ec1
379               Index Cond: (ff = '42'::bigint)
380(13 rows)
381
382-- check effects of row-level security
383set enable_nestloop = on;
384set enable_mergejoin = off;
385alter table ec1 enable row level security;
386create policy p1 on ec1 using (f1 < '5'::int8alias1);
387create user regress_user_ectest;
388grant select on ec0 to regress_user_ectest;
389grant select on ec1 to regress_user_ectest;
390-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass
391explain (costs off)
392  select * from ec0 a, ec1 b
393  where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
394                 QUERY PLAN
395---------------------------------------------
396 Nested Loop
397   ->  Index Scan using ec0_pkey on ec0 a
398         Index Cond: (ff = '43'::int8alias1)
399   ->  Index Scan using ec1_pkey on ec1 b
400         Index Cond: (ff = '43'::int8alias1)
401(5 rows)
402
403set session authorization regress_user_ectest;
404-- with RLS active, the non-leakproof a.ff = 43 clause is not treated
405-- as a suitable source for an EquivalenceClass; currently, this is true
406-- even though the RLS clause has nothing to do directly with the EC
407explain (costs off)
408  select * from ec0 a, ec1 b
409  where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
410                 QUERY PLAN
411---------------------------------------------
412 Nested Loop
413   ->  Index Scan using ec0_pkey on ec0 a
414         Index Cond: (ff = '43'::int8alias1)
415   ->  Index Scan using ec1_pkey on ec1 b
416         Index Cond: (ff = a.ff)
417         Filter: (f1 < '5'::int8alias1)
418(6 rows)
419
420reset session authorization;
421revoke select on ec0 from regress_user_ectest;
422revoke select on ec1 from regress_user_ectest;
423drop user regress_user_ectest;
424