src/test/regress/sql/equivclass.sql

   1 --
   2 -- Tests for the planner's "equivalence class" mechanism
   3 --
   4
   5 -- One thing that's not tested well during normal querying is the logic
   6 -- for handling "broken" ECs.  This is because an EC can only become broken
   7 -- if its underlying btree operator family doesn't include a complete set
   8 -- of cross-type equality operators.  There are not (and should not be)
   9 -- any such families built into Postgres; so we have to hack things up
  10 -- to create one.  We do this by making two alias types that are really
  11 -- int8 (so we need no new C code) and adding only some operators for them
  12 -- into the standard integer_ops opfamily.
  13
  14 create type int8alias1;
  15 create function int8alias1in(cstring) returns int8alias1
  16   strict immutable language internal as 'int8in';
  17 create function int8alias1out(int8alias1) returns cstring
  18   strict immutable language internal as 'int8out';
  19 create type int8alias1 (
  20     input = int8alias1in,
  21     output = int8alias1out,
  22     like = int8
  23 );
  24
  25 create type int8alias2;
  26 create function int8alias2in(cstring) returns int8alias2
  27   strict immutable language internal as 'int8in';
  28 create function int8alias2out(int8alias2) returns cstring
  29   strict immutable language internal as 'int8out';
  30 create type int8alias2 (
  31     input = int8alias2in,
  32     output = int8alias2out,
  33     like = int8
  34 );
  35
  36 create cast (int8 as int8alias1) without function;
  37 create cast (int8 as int8alias2) without function;
  38 create cast (int8alias1 as int8) without function;
  39 create cast (int8alias2 as int8) without function;
  40
  41 create function int8alias1eq(int8alias1, int8alias1) returns bool
  42   strict immutable language internal as 'int8eq';
  43 create operator = (
  44     procedure = int8alias1eq,
  45     leftarg = int8alias1, rightarg = int8alias1,
  46     commutator = =,
  47     restrict = eqsel, join = eqjoinsel,
  48     merges
  49 );
  50 alter operator family integer_ops using btree add
  51   operator 3 = (int8alias1, int8alias1);
  52
  53 create function int8alias2eq(int8alias2, int8alias2) returns bool
  54   strict immutable language internal as 'int8eq';
  55 create operator = (
  56     procedure = int8alias2eq,
  57     leftarg = int8alias2, rightarg = int8alias2,
  58     commutator = =,
  59     restrict = eqsel, join = eqjoinsel,
  60     merges
  61 );
  62 alter operator family integer_ops using btree add
  63   operator 3 = (int8alias2, int8alias2);
  64
  65 create function int8alias1eq(int8, int8alias1) returns bool
  66   strict immutable language internal as 'int8eq';
  67 create operator = (
  68     procedure = int8alias1eq,
  69     leftarg = int8, rightarg = int8alias1,
  70     restrict = eqsel, join = eqjoinsel,
  71     merges
  72 );
  73 alter operator family integer_ops using btree add
  74   operator 3 = (int8, int8alias1);
  75
  76 create function int8alias1eq(int8alias1, int8alias2) returns bool
  77   strict immutable language internal as 'int8eq';
  78 create operator = (
  79     procedure = int8alias1eq,
  80     leftarg = int8alias1, rightarg = int8alias2,
  81     restrict = eqsel, join = eqjoinsel,
  82     merges
  83 );
  84 alter operator family integer_ops using btree add
  85   operator 3 = (int8alias1, int8alias2);
  86
  87 create function int8alias1lt(int8alias1, int8alias1) returns bool
  88   strict immutable language internal as 'int8lt';
  89 create operator < (
  90     procedure = int8alias1lt,
  91     leftarg = int8alias1, rightarg = int8alias1
  92 );
  93 alter operator family integer_ops using btree add
  94   operator 1 < (int8alias1, int8alias1);
  95
  96 create function int8alias1cmp(int8, int8alias1) returns int
  97   strict immutable language internal as 'btint8cmp';
  98 alter operator family integer_ops using btree add
  99   function 1 int8alias1cmp (int8, int8alias1);
 100
 101 create table ec0 (ff int8 primary key, f1 int8, f2 int8);
 102 create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);
 103 create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);
 104
 105 -- for the moment we only want to look at nestloop plans
 106 set enable_hashjoin = off;
 107 set enable_mergejoin = off;
 108
 109 --
 110 -- Note that for cases where there's a missing operator, we don't care so
 111 -- much whether the plan is ideal as that we don't fail or generate an
 112 -- outright incorrect plan.
 113 --
 114
 115 explain (costs off)
 116   select * from ec0 where ff = f1 and f1 = '42'::int8;
 117 explain (costs off)
 118   select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
 119 explain (costs off)
 120   select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
 121 explain (costs off)
 122   select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
 123
 124 explain (costs off)
 125   select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
 126 explain (costs off)
 127   select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
 128 explain (costs off)
 129   select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
 130 explain (costs off)
 131   select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
 132 explain (costs off)
 133   select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
 134
 135 create unique index ec1_expr1 on ec1((ff + 1));
 136 create unique index ec1_expr2 on ec1((ff + 2 + 1));
 137 create unique index ec1_expr3 on ec1((ff + 3 + 1));
 138 create unique index ec1_expr4 on ec1((ff + 4));
 139
 140 explain (costs off)
 141   select * from ec1,
 142     (select ff + 1 as x from
 143        (select ff + 2 as ff from ec1
 144         union all
 145         select ff + 3 as ff from ec1) ss0
 146      union all
 147      select ff + 4 as x from ec1) as ss1
 148   where ss1.x = ec1.f1 and ec1.ff = 42::int8;
 149
 150 explain (costs off)
 151   select * from ec1,
 152     (select ff + 1 as x from
 153        (select ff + 2 as ff from ec1
 154         union all
 155         select ff + 3 as ff from ec1) ss0
 156      union all
 157      select ff + 4 as x from ec1) as ss1
 158   where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
 159
 160 explain (costs off)
 161   select * from ec1,
 162     (select ff + 1 as x from
 163        (select ff + 2 as ff from ec1
 164         union all
 165         select ff + 3 as ff from ec1) ss0
 166      union all
 167      select ff + 4 as x from ec1) as ss1,
 168     (select ff + 1 as x from
 169        (select ff + 2 as ff from ec1
 170         union all
 171         select ff + 3 as ff from ec1) ss0
 172      union all
 173      select ff + 4 as x from ec1) as ss2
 174   where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
 175
 176 -- let's try that as a mergejoin
 177 set enable_mergejoin = on;
 178 set enable_nestloop = off;
 179
 180 explain (costs off)
 181   select * from ec1,
 182     (select ff + 1 as x from
 183        (select ff + 2 as ff from ec1
 184         union all
 185         select ff + 3 as ff from ec1) ss0
 186      union all
 187      select ff + 4 as x from ec1) as ss1,
 188     (select ff + 1 as x from
 189        (select ff + 2 as ff from ec1
 190         union all
 191         select ff + 3 as ff from ec1) ss0
 192      union all
 193      select ff + 4 as x from ec1) as ss2
 194   where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
 195
 196 -- check partially indexed scan
 197 set enable_nestloop = on;
 198 set enable_mergejoin = off;
 199
 200 drop index ec1_expr3;
 201
 202 explain (costs off)
 203   select * from ec1,
 204     (select ff + 1 as x from
 205        (select ff + 2 as ff from ec1
 206         union all
 207         select ff + 3 as ff from ec1) ss0
 208      union all
 209      select ff + 4 as x from ec1) as ss1
 210   where ss1.x = ec1.f1 and ec1.ff = 42::int8;
 211
 212 -- let's try that as a mergejoin
 213 set enable_mergejoin = on;
 214 set enable_nestloop = off;
 215
 216 explain (costs off)
 217   select * from ec1,
 218     (select ff + 1 as x from
 219        (select ff + 2 as ff from ec1
 220         union all
 221         select ff + 3 as ff from ec1) ss0
 222      union all
 223      select ff + 4 as x from ec1) as ss1
 224   where ss1.x = ec1.f1 and ec1.ff = 42::int8;
 225
 226 -- check effects of row-level security
 227 set enable_nestloop = on;
 228 set enable_mergejoin = off;
 229
 230 alter table ec1 enable row level security;
 231 create policy p1 on ec1 using (f1 < '5'::int8alias1);
 232
 233 create user regress_user_ectest;
 234 grant select on ec0 to regress_user_ectest;
 235 grant select on ec1 to regress_user_ectest;
 236
 237 -- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass
 238 explain (costs off)
 239   select * from ec0 a, ec1 b
 240   where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
 241
 242 set session authorization regress_user_ectest;
 243
 244 -- with RLS active, the non-leakproof a.ff = 43 clause is not treated
 245 -- as a suitable source for an EquivalenceClass; currently, this is true
 246 -- even though the RLS clause has nothing to do directly with the EC
 247 explain (costs off)
 248   select * from ec0 a, ec1 b
 249   where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
 250
 251 reset session authorization;
 252
 253 revoke select on ec0 from regress_user_ectest;
 254 revoke select on ec1 from regress_user_ectest;
 255
 256 drop user regress_user_ectest;
 257
 258 -- check that X=X is converted to X IS NOT NULL when appropriate
 259 explain (costs off)
 260   select * from tenk1 where unique1 = unique1 and unique2 = unique2;
 261
 262 -- this could be converted, but isn't at present
 263 explain (costs off)
 264   select * from tenk1 where unique1 = unique1 or unique2 = unique2;
 265
 266 -- check that we recognize equivalence with dummy domains in the way
 267 create temp table undername (f1 name, f2 int);
 268 create temp view overview as
 269   select f1::information_schema.sql_identifier as sqli, f2 from undername;
 270 explain (costs off)  -- this should not require a sort
 271   select * from overview where sqli = 'foo' order by sqli;
 272
 273 --
 274 -- test handling of merge/hash clauses that do not have valid commutators
 275 --
 276
 277 -- There are not (and should not be) any such operators built into Postgres
 278 -- that are mergejoinable or hashable but have no commutators; so we leverage
 279 -- the alias type 'int8alias1' created in this file to conduct the tests.
 280 -- That's why this test is included here rather than in join.sql.
 281
 282 begin;
 283
 284 create table tbl_nocom(a int8, b int8alias1);
 285
 286 -- check that non-commutable merge clauses do not lead to error
 287 set enable_hashjoin to off;
 288 set enable_mergejoin to on;
 289 explain (costs off)
 290 select * from tbl_nocom t1 full join tbl_nocom t2 on t2.a = t1.b;
 291
 292 -- check that non-commutable hash clauses do not lead to error
 293 alter operator = (int8, int8alias1) set (hashes);
 294 alter operator family integer_ops using hash add
 295   operator 1 = (int8, int8alias1);
 296 create function hashint8alias1(int8alias1) returns int
 297   strict immutable language internal as 'hashint8';
 298 alter operator family integer_ops using hash add
 299   function 1 hashint8alias1(int8alias1);
 300 set enable_hashjoin to on;
 301 set enable_mergejoin to off;
 302 explain (costs off)
 303 select * from tbl_nocom t1 full join tbl_nocom t2 on t2.a = t1.b;
 304
 305 abort;