Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 453 insertions, 0 deletions

diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
new file mode 100644
index 0000000..126f704
--- /dev/null
+++ b/src/test/regress/expected/equivclass.out
@@ -0,0 +1,453 @@
+--
+-- Tests for the planner's "equivalence class" mechanism
+--
+-- One thing that's not tested well during normal querying is the logic
+-- for handling "broken" ECs.  This is because an EC can only become broken
+-- if its underlying btree operator family doesn't include a complete set
+-- of cross-type equality operators.  There are not (and should not be)
+-- any such families built into Postgres; so we have to hack things up
+-- to create one.  We do this by making two alias types that are really
+-- int8 (so we need no new C code) and adding only some operators for them
+-- into the standard integer_ops opfamily.
+create type int8alias1;
+create function int8alias1in(cstring) returns int8alias1
+  strict immutable language internal as 'int8in';
+NOTICE:  return type int8alias1 is only a shell
+create function int8alias1out(int8alias1) returns cstring
+  strict immutable language internal as 'int8out';
+NOTICE:  argument type int8alias1 is only a shell
+create type int8alias1 (
+    input = int8alias1in,
+    output = int8alias1out,
+    like = int8
+);
+create type int8alias2;
+create function int8alias2in(cstring) returns int8alias2
+  strict immutable language internal as 'int8in';
+NOTICE:  return type int8alias2 is only a shell
+create function int8alias2out(int8alias2) returns cstring
+  strict immutable language internal as 'int8out';
+NOTICE:  argument type int8alias2 is only a shell
+create type int8alias2 (
+    input = int8alias2in,
+    output = int8alias2out,
+    like = int8
+);
+create cast (int8 as int8alias1) without function;
+create cast (int8 as int8alias2) without function;
+create cast (int8alias1 as int8) without function;
+create cast (int8alias2 as int8) without function;
+create function int8alias1eq(int8alias1, int8alias1) returns bool
+  strict immutable language internal as 'int8eq';
+create operator = (
+    procedure = int8alias1eq,
+    leftarg = int8alias1, rightarg = int8alias1,
+    commutator = =,
+    restrict = eqsel, join = eqjoinsel,
+    merges
+);
+alter operator family integer_ops using btree add
+  operator 3 = (int8alias1, int8alias1);
+create function int8alias2eq(int8alias2, int8alias2) returns bool
+  strict immutable language internal as 'int8eq';
+create operator = (
+    procedure = int8alias2eq,
+    leftarg = int8alias2, rightarg = int8alias2,
+    commutator = =,
+    restrict = eqsel, join = eqjoinsel,
+    merges
+);
+alter operator family integer_ops using btree add
+  operator 3 = (int8alias2, int8alias2);
+create function int8alias1eq(int8, int8alias1) returns bool
+  strict immutable language internal as 'int8eq';
+create operator = (
+    procedure = int8alias1eq,
+    leftarg = int8, rightarg = int8alias1,
+    restrict = eqsel, join = eqjoinsel,
+    merges
+);
+alter operator family integer_ops using btree add
+  operator 3 = (int8, int8alias1);
+create function int8alias1eq(int8alias1, int8alias2) returns bool
+  strict immutable language internal as 'int8eq';
+create operator = (
+    procedure = int8alias1eq,
+    leftarg = int8alias1, rightarg = int8alias2,
+    restrict = eqsel, join = eqjoinsel,
+    merges
+);
+alter operator family integer_ops using btree add
+  operator 3 = (int8alias1, int8alias2);
+create function int8alias1lt(int8alias1, int8alias1) returns bool
+  strict immutable language internal as 'int8lt';
+create operator < (
+    procedure = int8alias1lt,
+    leftarg = int8alias1, rightarg = int8alias1
+);
+alter operator family integer_ops using btree add
+  operator 1 < (int8alias1, int8alias1);
+create function int8alias1cmp(int8, int8alias1) returns int
+  strict immutable language internal as 'btint8cmp';
+alter operator family integer_ops using btree add
+  function 1 int8alias1cmp (int8, int8alias1);
+create table ec0 (ff int8 primary key, f1 int8, f2 int8);
+create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
+create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
+-- for the moment we only want to look at nestloop plans
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+--
+-- Note that for cases where there's a missing operator, we don't care so
+-- much whether the plan is ideal as that we don't fail or generate an
+-- outright incorrect plan.
+--
+explain (costs off)
+  select * from ec0 where ff = f1 and f1 = '42'::int8;
+            QUERY PLAN             
+-----------------------------------
+ Index Scan using ec0_pkey on ec0
+   Index Cond: (ff = '42'::bigint)
+   Filter: (f1 = '42'::bigint)
+(3 rows)
+
+explain (costs off)
+  select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
+              QUERY PLAN               
+---------------------------------------
+ Index Scan using ec0_pkey on ec0
+   Index Cond: (ff = '42'::int8alias1)
+   Filter: (f1 = '42'::int8alias1)
+(3 rows)
+
+explain (costs off)
+  select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
+              QUERY PLAN               
+---------------------------------------
+ Index Scan using ec1_pkey on ec1
+   Index Cond: (ff = '42'::int8alias1)
+   Filter: (f1 = '42'::int8alias1)
+(3 rows)
+
+explain (costs off)
+  select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on ec1
+   Filter: ((ff = f1) AND (f1 = '42'::int8alias2))
+(2 rows)
+
+explain (costs off)
+  select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (ec1.ff = ec2.x1)
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
+   ->  Seq Scan on ec2
+(5 rows)
+
+explain (costs off)
+  select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
+                 QUERY PLAN                  
+---------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = '42'::int8alias1)
+   ->  Seq Scan on ec2
+         Filter: (x1 = '42'::int8alias1)
+(5 rows)
+
+explain (costs off)
+  select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
+               QUERY PLAN                
+-----------------------------------------
+ Nested Loop
+   Join Filter: (ec1.ff = ec2.x1)
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = '42'::bigint)
+   ->  Seq Scan on ec2
+         Filter: ('42'::bigint = x1)
+(6 rows)
+
+explain (costs off)
+  select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
+                 QUERY PLAN                  
+---------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = '42'::int8alias1)
+   ->  Seq Scan on ec2
+         Filter: (x1 = '42'::int8alias1)
+(5 rows)
+
+explain (costs off)
+  select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
+               QUERY PLAN                
+-----------------------------------------
+ Nested Loop
+   ->  Seq Scan on ec2
+         Filter: (x1 = '42'::int8alias2)
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = ec2.x1)
+(5 rows)
+
+create unique index ec1_expr1 on ec1((ff + 1));
+create unique index ec1_expr2 on ec1((ff + 2 + 1));
+create unique index ec1_expr3 on ec1((ff + 3 + 1));
+create unique index ec1_expr4 on ec1((ff + 4));
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1
+  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = '42'::bigint)
+   ->  Append
+         ->  Index Scan using ec1_expr2 on ec1 ec1_1
+               Index Cond: (((ff + 2) + 1) = ec1.f1)
+         ->  Index Scan using ec1_expr3 on ec1 ec1_2
+               Index Cond: (((ff + 3) + 1) = ec1.f1)
+         ->  Index Scan using ec1_expr4 on ec1 ec1_3
+               Index Cond: ((ff + 4) = ec1.f1)
+(10 rows)
+
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1
+  where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
+         Filter: (ff = f1)
+   ->  Append
+         ->  Index Scan using ec1_expr2 on ec1 ec1_1
+               Index Cond: (((ff + 2) + 1) = '42'::bigint)
+         ->  Index Scan using ec1_expr3 on ec1 ec1_2
+               Index Cond: (((ff + 3) + 1) = '42'::bigint)
+         ->  Index Scan using ec1_expr4 on ec1 ec1_3
+               Index Cond: ((ff + 4) = '42'::bigint)
+(12 rows)
+
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss2
+  where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Nested Loop
+   ->  Nested Loop
+         ->  Index Scan using ec1_pkey on ec1
+               Index Cond: (ff = '42'::bigint)
+         ->  Append
+               ->  Index Scan using ec1_expr2 on ec1 ec1_1
+                     Index Cond: (((ff + 2) + 1) = ec1.f1)
+               ->  Index Scan using ec1_expr3 on ec1 ec1_2
+                     Index Cond: (((ff + 3) + 1) = ec1.f1)
+               ->  Index Scan using ec1_expr4 on ec1 ec1_3
+                     Index Cond: ((ff + 4) = ec1.f1)
+   ->  Append
+         ->  Index Scan using ec1_expr2 on ec1 ec1_4
+               Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1)))
+         ->  Index Scan using ec1_expr3 on ec1 ec1_5
+               Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1)))
+         ->  Index Scan using ec1_expr4 on ec1 ec1_6
+               Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1)))
+(18 rows)
+
+-- let's try that as a mergejoin
+set enable_mergejoin = on;
+set enable_nestloop = off;
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss2
+  where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Merge Join
+   Merge Cond: ((((ec1_4.ff + 2) + 1)) = (((ec1_1.ff + 2) + 1)))
+   ->  Merge Append
+         Sort Key: (((ec1_4.ff + 2) + 1))
+         ->  Index Scan using ec1_expr2 on ec1 ec1_4
+         ->  Index Scan using ec1_expr3 on ec1 ec1_5
+         ->  Index Scan using ec1_expr4 on ec1 ec1_6
+   ->  Materialize
+         ->  Merge Join
+               Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
+               ->  Merge Append
+                     Sort Key: (((ec1_1.ff + 2) + 1))
+                     ->  Index Scan using ec1_expr2 on ec1 ec1_1
+                     ->  Index Scan using ec1_expr3 on ec1 ec1_2
+                     ->  Index Scan using ec1_expr4 on ec1 ec1_3
+               ->  Sort
+                     Sort Key: ec1.f1 USING <
+                     ->  Index Scan using ec1_pkey on ec1
+                           Index Cond: (ff = '42'::bigint)
+(19 rows)
+
+-- check partially indexed scan
+set enable_nestloop = on;
+set enable_mergejoin = off;
+drop index ec1_expr3;
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1
+  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec1_pkey on ec1
+         Index Cond: (ff = '42'::bigint)
+   ->  Append
+         ->  Index Scan using ec1_expr2 on ec1 ec1_1
+               Index Cond: (((ff + 2) + 1) = ec1.f1)
+         ->  Seq Scan on ec1 ec1_2
+               Filter: (((ff + 3) + 1) = ec1.f1)
+         ->  Index Scan using ec1_expr4 on ec1 ec1_3
+               Index Cond: ((ff + 4) = ec1.f1)
+(10 rows)
+
+-- let's try that as a mergejoin
+set enable_mergejoin = on;
+set enable_nestloop = off;
+explain (costs off)
+  select * from ec1,
+    (select ff + 1 as x from
+       (select ff + 2 as ff from ec1
+        union all
+        select ff + 3 as ff from ec1) ss0
+     union all
+     select ff + 4 as x from ec1) as ss1
+  where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Merge Join
+   Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
+   ->  Merge Append
+         Sort Key: (((ec1_1.ff + 2) + 1))
+         ->  Index Scan using ec1_expr2 on ec1 ec1_1
+         ->  Sort
+               Sort Key: (((ec1_2.ff + 3) + 1))
+               ->  Seq Scan on ec1 ec1_2
+         ->  Index Scan using ec1_expr4 on ec1 ec1_3
+   ->  Sort
+         Sort Key: ec1.f1 USING <
+         ->  Index Scan using ec1_pkey on ec1
+               Index Cond: (ff = '42'::bigint)
+(13 rows)
+
+-- check effects of row-level security
+set enable_nestloop = on;
+set enable_mergejoin = off;
+alter table ec1 enable row level security;
+create policy p1 on ec1 using (f1 < '5'::int8alias1);
+create user regress_user_ectest;
+grant select on ec0 to regress_user_ectest;
+grant select on ec1 to regress_user_ectest;
+-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass
+explain (costs off)
+  select * from ec0 a, ec1 b
+  where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
+                 QUERY PLAN                  
+---------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec0_pkey on ec0 a
+         Index Cond: (ff = '43'::int8alias1)
+   ->  Index Scan using ec1_pkey on ec1 b
+         Index Cond: (ff = '43'::int8alias1)
+(5 rows)
+
+set session authorization regress_user_ectest;
+-- with RLS active, the non-leakproof a.ff = 43 clause is not treated
+-- as a suitable source for an EquivalenceClass; currently, this is true
+-- even though the RLS clause has nothing to do directly with the EC
+explain (costs off)
+  select * from ec0 a, ec1 b
+  where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
+                 QUERY PLAN                  
+---------------------------------------------
+ Nested Loop
+   ->  Index Scan using ec0_pkey on ec0 a
+         Index Cond: (ff = '43'::int8alias1)
+   ->  Index Scan using ec1_pkey on ec1 b
+         Index Cond: (ff = a.ff)
+         Filter: (f1 < '5'::int8alias1)
+(6 rows)
+
+reset session authorization;
+revoke select on ec0 from regress_user_ectest;
+revoke select on ec1 from regress_user_ectest;
+drop user regress_user_ectest;
+-- check that X=X is converted to X IS NOT NULL when appropriate
+explain (costs off)
+  select * from tenk1 where unique1 = unique1 and unique2 = unique2;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Seq Scan on tenk1
+   Filter: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL))
+(2 rows)
+
+-- this could be converted, but isn't at present
+explain (costs off)
+  select * from tenk1 where unique1 = unique1 or unique2 = unique2;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Seq Scan on tenk1
+   Filter: ((unique1 = unique1) OR (unique2 = unique2))
+(2 rows)
+
+-- check that we recognize equivalence with dummy domains in the way
+create temp table undername (f1 name, f2 int);
+create temp view overview as
+  select f1::information_schema.sql_identifier as sqli, f2 from undername;
+explain (costs off)  -- this should not require a sort
+  select * from overview where sqli = 'foo' order by sqli;
+          QUERY PLAN          
+------------------------------
+ Seq Scan on undername
+   Filter: (f1 = 'foo'::name)
+(2 rows)
+